The Diagram Gal

•

NIST Compliance Diagrams

•

Conmon Spreadsheets in Excel: examples & formulas

•

FedRamp | GovRamp | SOC 2 | PCI DSS 4.0.1

Welcome to Better Compliance Documentation

How to add “Add ‘em up” Formulas in GovRamp ConMon

Executive Summary & Open POA&M Sheets

Make your executive summary do the math…well mostly. There are some things Excel won’t do. Yah, I know Excel can do everything right? Well….almost.

GovRamp expects you to list things as originally reported in this section. The original severity ratings, the original due dates. Exceptions are made on the Stats Summary Sheet when considering if you are approaching or exceeding their tolerances for over-aged items. There it excludes items that have pending or approved deviation requests or outstanding vendor dependencies. However on the EXECUTIVE Summary all is included.

First let’s configure the conditional coloration in the age list

This is 100% optional and only there for you if you want it. It can highlight when you are approaching too many aged items. SEE how below.

  • Setting Conditional Formatting in particular cells.
    1. Select the cells you want to format. Such as cells that contain overaged counts.
    2. Go to Home, Conditional Formatting, and “create new rule.” ( This may look different depending on the version of Excel and your computer OS).
    3. Select “Classic Style”, “Format only cells that contain”, “Cell Value”, “greater than”, and “0”.
    4. The easiest way to apply to a lattice of cells (when you are not just high lighting a row or column) is to apply the rules to one section of cells then go to “manage rules” from under the Conditional Formatting button on the Home menu.
    5. Then you just have to duplicate the rule 6 times and correct the cells it is applied to.
      • Here is a short cut to copy and paste into your (you can also simply put all applicable sections in the “applied to” field separated by a comma.
        • High: $R$13:$U$13
        • High/Mod: $S$14:$U$14
        • *GovRamp doesn’t let you do High to Low anymore
        • Moderate: $S$15:$U$15
        • Moderate/Low: $T$16:$U$16
        • Low: $T$17:$U$17
        • Total: $U18
Next Let us prep your worksheet to calculate the Age of the Open POA&Ms -> Go to open POA & M sheet for this step.

Who wants to do all this in their head! Calculators have been available commercially since the 1970s and Excel since the 1980s. No reason to use people power on this task.

You cannot add both a table and use column filters at the same time. You have 2 options.

  • remove the filters in the open POA&M sheet and have a big table for all the columns with formulas
  • leave the filters in place and add a new table at the end of the open POAMs. Either will work but it get’s complicated with a filtered area next to a table. I don’t recommend.

Removing Filters

  • First select all columns that are being filtered by clicking and dragging across the top A to AD column names.
  • While the area is selected, go to the Home Menu then find the Filter/Sort funnel on the right.
    • Click on the dropdown
    • Uncheck Filter
    • Done!
Let’s Get Rid of too many formulas on the OPEN POA&M sheet. Although massively improved, we will add a table and it can be a wee bit better still.

GovRamp improved this already, there were millions, and now there are 19,996 in column L. We can still do a bit better. Let’s add a table and a spare formula for when we need it.

  • Delete some 19,000+ formulas
    • On your blank pristine new worksheet. 🙂 Go to Cell L31
    • Click the cell to highlight it. Then, on your keyboard, press shift, ctrl, and the down arrow. This will highlight all of the remaining formulas below Cell L30.
    • Be Brave. Right click, click Delete. If it asks, select “cells shift up.” 🙂
  • Create a table
    • We are going to add a table. It will do 2 things that the current set up won’t.
      • First, it lets you sort all the rows by one column. That is much better then individualized column sorts.
      • Second, and this is the big one. If you click and drag the table to make it bigger. It will copy the formulas in column L down to the last row. You can even just go to the Column A in the first row below the table and add data, and it should auto add that one row to your table and bring down the formula over in column L.
    • Select the table area. Go to Cell A3 where it says POAM ID. The select that cell and hold Shift or just click and drag your mouse to Cell AD30.
    • Go to the Menu Bar at the top and find the “Insert” Menu
      • Select “add table”
      • Check the box for “my table has headers”
      • And OK.
  • Create a Spare. Excel formulas get deleted sometimes and the spreadsheets get all weird at times when you delete rows. So let’s ensure you have a spare formula.
    • Go to Cell K2 and type: SPARE Formula–>
    • Then go to Cell L4, select the cell, right click
    • Select “copy” on the pop up menu
    • Then go to cell L2 and right click and select “paste special”
    • Then click “paste formula”
    • You can’t see it but if you click back into the cell and look at the formula bar, you will see your spare formula. I am formatting that cell so I know it is special. I added blue fill.

Next Let us calculate each POA&M’s age on the Open POA&M sheet. Add some Columns.

In this next step we will add some columns to the table you just created and then you will see the power of a table. It the built in functions in excel inside a table really make life easier. Let me show you.

  • Copy the block of text below
    • Aged to last submission date
      Age at submission 0 to 30
      Age at submission 31 to 90
      Age at submission 90 to180
      Age at submission >= 181
  • Select Cells AE3 to AI3 by clicking in Cell AE3 and dragging to AI3
  • Right click & paste
  • Your table has added 5 columns
  • You will need your table name to match mine for ease of copying formulas, so let’s name it–“Table2”.

We are getting there….keep going. Remember these hours will save you time all year…it will be worth the work!

  • Add a tiny reference table for the date. This will make your formulas in the next step work well.
    • Select cells AE2:AF3. Just 4 cells.
    • Go to “Insert”, Click “Table”
    • Check “My table has headers” and then “OK”
    • We didn’t add the table header names, but can do that now. (Just showing you a different way.)
    • Change “Column1” to “Last Submission”
    • Change “Column2” to “Today’s date”
    • Then go to the Table Menu at the top and change the name of this table to “Table3”.
  • Let’s add 2 formulas, but first we have to undo a merged cell.
  • Go to the Executive Summary tab, click on the merged date cell E8:X8
    • Go to the Home menu and click on “merge cells” to unmerge that box.
    • Let’s make it prettier.
    • Select cells E8 to X8 again
      • Format it by changing the fill from “no fill” to white and the text from that grey color to black.
      • Go ahead and enter today’s date in the cell E8.
  • Now add the formulas
    • In Cell AE3 on the “Open POA&M” tab, click into the cell and type =
    • Then at the bottom of the page you will see the separate page tabs, Click on “Executive Summary”
    • Select cell E8 and hit Enter.
    • This will link the cell AE2 to the date on the executive summary.
  • One last formula
    • On the Open POA&Ms sheet, go to Cell AF2 and enter =today()
    • This is optional but I find it useful so I can pay attention to the last time I updated the date on the executive summary sheet. I would list that day as the submission date for your monthly POA&M submission to the PMO. That way you know what will be out of date on the submission date.

Whew! Okay take a break…get a tasty beverage and/or a snack. Then come back. We are halfway there. 🙂

Now we are going to put in formulas. The days aged calculations are coming.

  • Go to Cell AE4 and paste: =Table3[Last submission date]-[@[Original Detection Date]]
  • Hit enter. It will auto populate the formula to the end of the table in that column. If you are having any issues you can always try typing “=” in the cell then selecting cell AE4 type “-” and select K4. Then hit “enter”. (I believe that GovRamp has a line return between Original and Detection Date that may interfere with the formula above.)
  • If you see my second image showing the same formula, the difference is clear, the formula in the second image is red verses black showing a valid cell reference and there is a line return between “Original” and “Detection.” (Press Alt & “enter” to get an in cell line return in excel.)
  • Let’s remove that pesky line return to make our next steps easier.
  • Click in Cell K4 and you will see the line return.
  • Put your cursor directly before “Detection”
  • Press “backspace” and then “space.” (This is completely optional)

Now Lets Move on. “NEXT!!”

  • Put your cursor in Cell AF4
  • Paste:
    • =IF(AND([@[aged to last submission date]]>0,[@[aged to last submission date]]<31),TRUE,FALSE)
    • hit “Tab”
  • Put your cursor in Cell AG4
  • Paste:
    • =IF(AND([@[aged to last submission date]]>30,[@[aged to last submission date]]<91),TRUE,FALSE)
    • hit “Tab”
  • Put your cursor in Cell AH4
  • Paste:
    • =IF(AND([@[aged to last submission date]]>90,[@[aged to last submission date]]<181),TRUE,FALSE)
    • hit “Tab”
  • And finally put your cursor in Cell AI4
  • Paste:
    • =IF([@[aged to last submission date]]>180,TRUE,FALSE)
    • hit “Enter”
  • You may notice the little yellow warning triangle and the green corner triangles. in all the formula columns. This is because, in my version of Excel (the latest one 😉 ) There is a new way to protect formulas. So if you highlight all of the cells with the green triangle.
  • Then click on the yellow triangle and select “Lock Cell.”

Now you just need some data. We can add test data after we finish.

Go take another break!!

ALMOST THERE @Dear Reader! Having the Executive Summary auto calculate your aged list.

Yes, all this work will soon be worth it. A day designing your new spread sheet and you will save hours for the next 12 months. Here we go. The Big Formulas.

Head back on over to the Executive Summary Sheet.

Now the sweet stuff. The short cut I am handing you here is the Formulas, but unlike in a table, these formulas will need to be added one at a time. See the empty white cells? There are 20 of them, you need 20 formulas. Here we go. Copy and paste one at a time.

  1. HIGH. Cell Q13, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!U:U,”no”,’Open POA&M’!AF:AF,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!U:U,”no”,’Open POA&M’!AF:AF,TRUE) + COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”high”,’Open POA&M’!AF:AF,TRUE)
  2. Cell R13, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!U:U,”no”,’Open POA&M’!AG:AG,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!U:U,”no”,’Open POA&M’!AG:AG,TRUE) + COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”high”,’Open POA&M’!AG:AG,TRUE)
  3. Cell S13, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!U:U,”no”,’Open POA&M’!AH:AH,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!U:U,”no”,’Open POA&M’!AH:AH,TRUE)+COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”high”,’Open POA&M’!AH:AH,TRUE)
  4. Cell T13, copy below, click into cell, paste, hit “enter.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!U:U,”no”,’Open POA&M’!AI:AI,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!U:U,”no”,’Open POA&M’!AI:AI,TRUE)+COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”high”,’Open POA&M’!AI:AI,TRUE)
  5. High to Moderate. Cell Q14, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!T:T,”Moderate”,’Open POA&M’!AF:AF,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AF:AF,TRUE)
  6. Cell R14, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!T:T,”Moderate”,’Open POA&M’!AG:AG,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AG:AG,TRUE)
  7. Cell S14, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AH:AH,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AH:AH,TRUE)
  8. Cell T14, copy below, click into cell, paste, hit “enter.”
    • =COUNTIFS(‘Open POA&M’!S:S,”High”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AI:AI,TRUE) +COUNTIFS(‘Open POA&M’!S:S,”critical”,’Open POA&M’!T:T,”moderate”,’Open POA&M’!AI:AI,TRUE)
  9. Moderate. Q15, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”Moderate”,’Open POA&M’!U:U,”no”,’Open POA&M’!AF:AF,TRUE)
  10. Cell R15, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”Moderate”,’Open POA&M’!U:U,”no”,’Open POA&M’!AG:AG,TRUE)
  11. Cell S15, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”Moderate”,’Open POA&M’!U:U,”no”,’Open POA&M’!AH:AH,TRUE)
  12. Cell T15, copy below, click into cell, paste, hit “enter.”
    • =COUNTIFS(‘Open POA&M’!S:S,”Moderate”,’Open POA&M’!U:U,”no”,’Open POA&M’!AI:AI,TRUE)
  13. Moderate to Low. Q16, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”moderate”,’Open POA&M’!T:T,”low”,’Open POA&M’!AF:AF,TRUE)
  14. Cell R16, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”moderate”,’Open POA&M’!T:T,”low”,’Open POA&M’!AG:AG,TRUE)
  15. Cell S16, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”moderate”,’Open POA&M’!T:T,”low”,’Open POA&M’!AH:AH,TRUE)
  16. Cell T16, copy below, click into cell, paste, hit “enter.”
    • =COUNTIFS(‘Open POA&M’!S:S,”moderate”,’Open POA&M’!T:T,”low”,’Open POA&M’!AI:AI,TRUE)
  17. Low. Cell Q17, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”low”,’Open POA&M’!U:U,”no”,’Open POA&M’!AF:AF,TRUE)
  18. Cell R17, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!S:S,”Low”,’Open POA&M’!U:U,”no”,’Open POA&M’!AG:AG,TRUE)
  19. Cell S17, copy below, click into cell, paste, hit “tab.”
    • =COUNTIFS(‘Open POA&M’!T:T,”Low”,’Open POA&M’!U:U,”no”,’Open POA&M’!AH:AH,TRUE)
  20. Cell T17, copy below, click into cell, paste, hit “enter.”
    • =COUNTIFS(‘Open POA&M’!U:U,”Low”,’Open POA&M’!U:U,”no”,’Open POA&M’!AI:AI,TRUE)

DONE!

Add some data to check.

Now you may not need fake data. If you are building out this worksheet, you probably have your own. I’m going to give you some fake data here though to test out your spreadsheet calculations.

We are going back to the Open POA&M sheet and I’m going to talk you through this. We are going to paste some data in sections to make sure we don’t override those formulas. So We will paste up until column L and then after Column L. That is the column with the calculation for the Scheduled due date.

What I want you to do is copy the data below. Select cells cells A3 to K17. Paste.

Test-00001CA-7poor math skilz 1difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template10/31/25
Test-00002CA-7poor math skilz 2difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template11/30/25
Test-00003CA-7poor math skilz 3difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template11/30/25
Test-00004CA-7poor math skilz  4difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template11/30/25
Test-00005CA-7poor math skilz 5difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-00006CA-7poor math skilz 6difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-00007CA-7poor math skilz  7difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-00008CA-7poor math skilz 8difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-00009CA-7poor math skilz 9difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-000010CA-7poor math skilz 10difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-000011CA-7poor math skilz  11difficultiy adding summary statsCritiqueHumanxlml con mon matrixsee emailnoneadd to template12/31/25
Test-000012CM-6Cat I Disa StigsThe system has failed to implement one or more Critical or High impact recommended configurationsCritiqueHumanxlml con mon matrixsee emailnoneadd to template02/01/26
Test-000013CM-6Cat II Disa StigsThe system has failed to implement one or more Moderate impact recommended configurationsCritiqueHumanxlml con mon matrixsee emailnoneadd to template02/01/26
Test-000014CM-6Cat III Disa StigsThe system has failed to implement one or more Low impact recommended configurationsCritiqueHumanxlml con mon matrixsee emailnoneadd to template02/01/26
  • Next copy the data below here, select cells M3:Y17, paste!
Yes02/08/25GovRampHighN/ANoNoNon/a
Non/an/aCriticalN/ANoNoNon/a
Non/an/aModerateN/ANoNoNon/a
Non/an/aModerateLowPending ApprovalNoNotesting RA based on CVSS including data impact level
Non/an/aLowN/ANoNoNon/a
Non/an/aHighModeratePending ApprovalNoNotesting RA based on CVSS including data impact level
Non/an/aCriticalHighPending ApprovalNoNotesting RA based on CVSS including data impact level
Non/an/aLowN/ANoPending ApprovalNoworksheet functions as expected
Non/an/aLowN/ANoNoPending Approvalcan’t update at this time because time is money friends
Non/an/aModerateN/ANoNoNon/a
Non/an/aModerateN/ANoNoNon/a
Non/an/aHighN/ANoNoNon/a
Non/an/aModerateN/ANoNoNon/a
Non/an/aLowN/ANoNoNon/a
  • Open POA&M with fake data
  • Open POA&M showing ages of fake list–whoops. See column AE. That is a date format, but it would work better for us to see it as a number of days “aged” format. Don’t worry we can fix.
    • Select the table column Cell AE4. Then press “Shift” and page down to get to the end of the table in that column.
  • In the home menu, find the data type in the center. Change it from date to number.
  • Now you may want to get rid of the default 2 decimal places. You can click the “Decrease decimal” icon 2 X.

  • DID IT WORK??!!!
    • We added 17 vulnerabilities….
    • …it adds up! Yay!.
  • If you want, change the date in Cell E8 of the executive summary and see what happens.
  • Success! The thing works.

See the PDF below for a full explanation of the changes listed above. This is a copy of the document sent to GovRAMP in which I made some suggestions between the last version and the current version. Some of the ideas have been adopted and some have not yet. However, for your convenience you can download a copy of my current working improved ConMon matrix with the sample data included. Please remember to delete the sample data and my explanation coversheet before submission to GovRAMP if you decide to adopt my version. Also, I have zipped it for convince and security. I have set security headers on this site to prevent unauthorized external content. Please contact me if you run into any issues on LinkedIn. https://www.linkedin.com/in/jennifer-ponder-kilgore-88b74730/

TheDiagramGalsCalclationsGovRAMP-Continuous Monitoring-Matrix_Rev5_V1.3 2.xlsxDownload
ConMon Matrix Improvement ChecklistDownload