Build a NIST 800-53 Risk Register

Build a cybersecurity risk register in Excel mapped to NIST 800-53 Rev 5.

Introduction

30 Second Summary

Every organization faces security threats, from unauthorized access to unpatched vulnerabilities. Tracking those threats in a structured way is what separates a secure operation from one waiting for an incident.

In this project, you will build a cybersecurity risk register in Excel aligned with NIST SP 800-53 Rev 5. You'll map real information security risks to specific control families, automate risk scoring with formulas, and visualize risk distribution with a color-coded heatmap.

What You'll Build

A cybersecurity risk register workbook in Excel with automated scoring, conditional formatting, dropdown validation, and a 5x5 risk matrix heatmap.

By the end of this project, you'll have:

A 12-column risk register with automated risk scoring, color-coded ratings, and dropdown menus mapped to NIST 800-53 Rev 5 control families.
8 documented cybersecurity risks each mapped to a specific control family and control identifier using Cause-Event-Impact format.
A 5x5 risk matrix heatmap that visually maps all identified risks by likelihood and impact.
Secret Mission: Add residual risk tracking with a POA&M column to align with RMF deliverables.

Are there any prerequisites?

This project assumes you are familiar with NIST 800-53 Rev 5 control families and core concepts. You should also have Microsoft Excel installed on your computer.

Before We Start

Before We Start...

Set Up the Workbook Structure

A risk register's column structure determines what information you can track and how you connect risks to specific security controls.

In this step, you'll create a new Excel workbook with 12 columns aligned to NIST 800-53 Rev 5 requirements. You'll also add a second sheet for your risk matrix heatmap.

In this step, get ready to:

Create and save the Excel workbook.
Set up 12 formatted column headers.
Add a Risk Matrix worksheet.

Create the workbook and save it

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Excel and press Enter to open it.
Click Blank Workbook to create a new file.
Save the workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).
Name the file NIST_800-53_Risk_Register.xlsx and save it to your Desktop.
Right-click the sheet tab at the bottom of the screen (labeled "Sheet1").
Select Rename and type Risk Register.
Press Enter to confirm the name.

You should see the tab at the bottom now reads Risk Register instead of "Sheet1".

Can't find the Rename option?

On some versions of Excel, you can double-click the sheet tab name directly to edit it. If right-clicking doesn't show a context menu, try double-clicking the tab text instead.

Help me rename my Excel sheet tab.

Set up the header row

Your risk register needs 12 columns that capture everything from risk identification through to response tracking. Each column maps to a specific part of the risk assessment process.

Click cell A1 to select it.
Type the following 12 headers in order from A1 to L1, pressing Tab after each one to advance to the next cell:

A1: Risk ID
B1: Risk Description
C1: Control Family
D1: Mapped Control
E1: Likelihood (1-5)
F1: Impact (1-5)
G1: Risk Score
H1: Rating
I1: Response Strategy
J1: Response Action
K1: Risk Owner
L1: Status

What do these columns track?

The first four columns (Risk ID through Mapped Control) identify the risk and link it to a specific NIST 800-53 Rev 5 control. Likelihood and Impact feed into automated scoring. The remaining columns track your response plan and ownership.

Now format the header row so it stands out visually.

Select the entire header row by clicking cell A1 and pressing Cmd+Shift+Right Arrow (macOS) or Ctrl+Shift+Right Arrow (Windows).
Bold the selection by pressing Cmd+B (macOS) or Ctrl+B (Windows).
In the Home tab, click the Fill Color dropdown (the paint bucket icon) and choose a dark blue.
Click the Font Color dropdown (the "A" with a colored bar) and select white.

Finally, freeze the top row so your headers stay visible as you scroll.

Go to View > Freeze Panes > Freeze Top Row.
Scroll down a few rows to confirm that Row 1 stays fixed at the top.

Your header row should now appear bold, with white text on a dark blue background. It remains visible no matter how far down you scroll.

Headers not staying visible when you scroll?

Make sure you selected Freeze Top Row specifically. If you selected Freeze Panes without specifying Top Row, it may freeze at the wrong position. Go to View > Freeze Panes > Unfreeze Panes first, then try again.

Help me freeze the top row in Excel.

Create the Risk Matrix sheet

Your workbook needs a second worksheet where you'll later build a 5x5 heatmap showing where each risk falls on the Likelihood vs. Impact grid.

Right-click the Risk Register tab at the bottom of the screen.
Select Insert > Worksheet.
Right-click the new sheet tab (likely named "Sheet2").
Select Rename and type Risk Matrix.
Press Enter to confirm.

You should now see two tabs at the bottom of your workbook: Risk Register and Risk Matrix.

Alternative way to add a sheet

You can also click the + button next to your existing sheet tabs to add a new worksheet.

Click the Risk Register tab to switch back to it.
Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Make sure your workbook is saved before moving on.

ⓧ I'd like to double check the full setup

Here's what your workbook should look like at this point:

Two sheet tabs at the bottom: Risk Register and Risk Matrix.
On the Risk Register sheet, Row 1 has 12 headers in cells A1 through L1: Risk ID, Risk Description, Control Family, Mapped Control, Likelihood (1-5), Impact (1-5), Risk Score, Rating, Response Strategy, Response Action, Risk Owner, Status.
The header row is bold with white text on a dark blue background.
The top row is frozen (stays visible when you scroll down).
The Risk Matrix sheet is blank.

Your workbook structure is ready. Next, you'll add dropdown menus to enforce consistent data entry across your risk register.

Add Data Validation Dropdowns

Your workbook structure is ready with all 12 columns labeled and formatted. The next challenge is keeping data entry consistent across those columns.

Data validation dropdowns restrict cells to predefined values. This prevents typos, enforces standardized terminology, and keeps your register auditable. In this step, you'll add dropdowns to five columns so every entry follows the same controlled vocabulary.

In this step, get ready to:

Add a Control Family dropdown with all 20 NIST 800-53 Rev 5 families.
Add Likelihood and Impact scoring dropdowns.
Add Response Strategy and Status dropdowns.

Add the Control Family dropdown

The Control Family column maps each risk to one of the 20 NIST SP 800-53 Rev 5 control families. A dropdown ensures every entry uses the official two-character family code.

Click on cell C2 in the Risk Register sheet.
Hold Shift and click cell C20 to select the range C2:C20.
Click the Data tab in the ribbon.
Click Data Validation.
Under Allow, select List.
In the Source field, type the following comma-separated list of all 20 control families:

AC,AT,AU,CA,CM,CP,IA,IR,MA,MP,PE,PL,PM,PS,PT,RA,SA,SC,SI,SR

Click OK.

Why include all 20 control families?

NIST SP 800-53 Rev 5 organizes its 1,196 controls into 20 families, each identified by a two-character code (AC for Access Control, IR for Incident Response, and so on). Including all 20 in the dropdown makes your register reusable for any future risk you identify.

In this project you'll map risks to 8 of these families. The full list is there so you can extend the register later without modifying the validation.

Click on any cell in the range C2:C20.
You should see a small dropdown arrow appear on the right side of the cell. Click it to confirm the list of control families appears.

Don't see a dropdown arrow?

Make sure you selected the range C2:C20 before opening Data Validation. If you only selected one cell, the dropdown only applies to that single cell.

Check that you chose List under Allow and that the Source field has no extra spaces before the first value.

Help me troubleshoot my Data Validation dropdown in Excel. I selected C2:C20, chose List, and typed the control family codes, but no dropdown arrow appears.

Add Likelihood and Impact dropdowns

The Likelihood and Impact columns use a 1-5 scale. Restricting these to integers prevents someone from entering a 6 or typing text, which would break your risk score formula later.

Select cells E2:E20 (the Likelihood column).
Click the Data tab, then click Data Validation.
Under Allow, select List.
In the Source field, type: 1,2,3,4,5.
Click OK.

Now repeat the same process for the Impact column.

Select cells F2:F20 (the Impact column).
Click Data > Data Validation.
Set Allow to List and type 1,2,3,4,5 in the Source field.
Click OK.

What do the 1-5 values represent?

The 5-point scale follows ISO 31000-aligned risk assessment methodology. For Likelihood: 1 = Rare, 2 = Unlikely, 3 = Possible, 4 = Likely, 5 = Almost Certain. For Impact: 1 = Negligible, 2 = Minor, 3 = Moderate, 4 = Major, 5 = Catastrophic.

Multiplying these together produces risk scores from 1 to 25. You'll build that formula in the next step.

Click on cell E2 and confirm the dropdown shows values 1 through 5.
Click on cell F2 and confirm the same dropdown appears.

Dropdown shows text instead of numbers?

Make sure you typed the values without spaces between the commas. The source should be exactly 1,2,3,4,5 with no spaces. Extra spaces create entries like " 2" instead of "2", which can cause formula errors later.

Help me fix my Data Validation list in Excel. My dropdown values have extra spaces or aren't displaying as expected.

Add Response Strategy and Status dropdowns

The final two dropdowns standardize how you document your risk response decisions and track their progress.

Select cells I2:I20 (the Response Strategy column).
Click Data > Data Validation.
Set Allow to List and type in the Source field:

Avoid,Mitigate,Transfer,Accept

Click OK.

Now set up the Status dropdown.

Select cells L2:L20 (the Status column).
Click Data > Data Validation.
Set Allow to List and type in the Source field:

Open,In Progress,Closed

Click OK.

What are the four response strategies?

These are the standard risk response strategies in the NIST Risk Management Framework context. Avoid eliminates the activity causing the risk. Mitigate reduces likelihood or impact through controls.

Transfer shifts the risk to a third party (like cyber insurance). Accept acknowledges the risk when the cost of mitigation exceeds the potential loss.

Click on cell I2 and confirm the dropdown shows Avoid, Mitigate, Transfer, and Accept.
Click on cell L2 and confirm the dropdown shows Open, In Progress, and Closed.
Save your workbook with Cmd+S.

Data Validation option is greyed out?

This can happen if your worksheet is protected. Go to Review > Unprotect Sheet and try again. Also confirm you're clicking inside the spreadsheet cells (not the header row) before opening Data Validation.

Help me figure out why Data Validation is greyed out in my Excel workbook.

✔️ Awesome, I've got everything!

Great. Double check you've saved your workbook before moving on.

ⓧ I'd like to double check the full setup

Here's what your Risk Register sheet should have at this point:

Headers in A1:L1 with bold text, dark blue background, white text, and frozen top row.
C2:C20: Control Family dropdown with values AC,AT,AU,CA,CM,CP,IA,IR,MA,MP,PE,PL,PM,PS,PT,RA,SA,SC,SI,SR.
E2:E20: Likelihood dropdown with values 1,2,3,4,5.
F2:F20: Impact dropdown with values 1,2,3,4,5.
I2:I20: Response Strategy dropdown with values Avoid,Mitigate,Transfer,Accept.
L2:L20: Status dropdown with values Open,In Progress,Closed.
The Risk Matrix sheet exists but remains blank.

Your register now enforces consistent data entry across five key columns. Next up, you'll add formulas that automatically calculate risk scores and assign rating bands based on the Likelihood and Impact values you just constrained.

Add Formulas for Automated Risk Scoring

Your risk register now has structured dropdowns for consistent data entry. The next piece is automated scoring.

A risk score quantifies each risk by multiplying its Likelihood by its Impact. Instead of calculating this by hand for every row, you will add formulas that auto-calculate the score and assign a rating band (Low, Medium, High, or Critical) the moment you enter values.

In this step, get ready to:

Enter the Risk Score formula that multiplies Likelihood by Impact.
Enter the Rating formula that assigns a band based on the score.
Format the score and rating columns for readability.

Create the Risk Score formula

The Risk Score column (G) needs to multiply each row's Likelihood by its Impact. The formula should also handle empty rows gracefully by returning a blank instead of zero.

Click cell G2 in the Risk Register sheet.
Type the following formula and press Enter:

=IF(AND(E2<>"",F2<>""),E2*F2,"")

What does this formula do?

AND(E2<>"",F2<>"") checks that both the Likelihood cell (E2) and the Impact cell (F2) have values. This prevents the formula from returning 0 when a row is still empty.
If both cells have values, E2*F2 multiplies Likelihood by Impact to produce a score from 1 to 25.
If either cell is blank, the formula returns an empty string so the row stays clean.

Now copy the formula down to row 20. Click cell G2 to select it.
Hover your cursor over the lower-right corner of the cell until it turns into a plus sign (+). This is the fill handle.
Drag the fill handle down to cell G20 and release.

All cells in G2:G20 should appear blank. This is correct because no Likelihood or Impact values have been entered yet.

Seeing a 0 or an error instead of a blank cell?

If you see 0 in cells, the formula may be missing the IF/AND wrapper. Double-click G2 and confirm the formula matches exactly, including the double-quote pairs.
If you see a #VALUE! error, check for mismatched parentheses. The formula has three opening parentheses and three closing ones.
If the fill handle is not visible, go to Excel > Preferences > Edit and check Allow fill handle and cell drag-and-drop.

Help me troubleshoot my Risk Score formula in Excel.

Create the Rating formula

The Rating column (H) translates the numeric score into a human-readable band. Scores of 15 or above are Critical, 8-14 are High, 4-7 are Medium, and 1-3 are Low.

Click cell H2.
Type the following formula and press Enter:

=IF(G2="","",IF(G2>=15,"Critical",IF(G2>=8,"High",IF(G2>=4,"Medium","Low"))))

What does this formula do?

This is a nested IF formula that checks the Risk Score in G2 and assigns a rating band.

The outer IF(G2="","",...) returns blank when there is no score, keeping empty rows clean.
IF(G2>=15,"Critical",...) catches the highest-severity risks first (scores 15-25).
The remaining thresholds cascade downward: 8+ is High, 4+ is Medium, and everything else is Low.

Click cell H2 to select it.
Drag the fill handle from the lower-right corner of H2 down to H20.

Cells H2:H20 should appear blank. The Rating formula depends on the Risk Score column, which is also blank until you enter data in a later step.

Seeing #VALUE! or mismatched text?

Count your parentheses. This formula has five opening parentheses and five closing ones. A mismatch causes a syntax error.
Confirm the comparison operators are correct: >=15, >=8, >=4. Using just > instead of >= shifts the bands by one.
Make sure you typed straight double quotes, not curly (smart) quotes. Excel does not recognize curly quotes in formulas.

Help me fix my nested IF Rating formula.

Format the score and rating columns

Formatting ensures the Risk Score displays as a whole number and the Rating text is centered for easy scanning.

Select the range G2:G20.
Right-click the selection and choose Format Cells.
In the Number tab, select Number from the category list.
Set Decimal places to 0 and click OK.
Now select the range H2:H20.
In the Home tab, click the Center alignment button to center-align the text.

You will not see a visible change yet because both columns are still blank. The formatting takes effect once you enter risk data in a later step.

Why format now instead of later?

Applying number formatting and alignment before data entry means every new row inherits the correct display settings automatically. You avoid having to come back and reformat after the register is populated.

✔️ Awesome, I've got everything!

Great. Both formulas are in place and formatted. Save your workbook before moving on.

ⓧ I'd like to double check my formulas

Here is a summary of what your Risk Register sheet should have at this point:

Cells G2:G20 contain the formula: =IF(AND(E2<>"",F2<>""),E2*F2,"") (with row references adjusting per row). Formatted as Number with 0 decimal places.
Cells H2:H20 contain the formula: =IF(G2="","",IF(G2>=15,"Critical",IF(G2>=8,"High",IF(G2>=4,"Medium","Low")))) (with row references adjusting per row). Center-aligned.
All cells in both columns display as blank because no Likelihood or Impact values are entered yet.

To verify a formula in any cell, double-click the cell. The formula bar at the top shows the full formula text.

Your formulas are locked in. Next up, you will apply conditional formatting to color-code these scores and ratings so high-risk items jump off the page at a glance.

Apply Conditional Formatting

Your formulas now calculate risk scores and assign rating labels automatically. But a wall of text labels is hard to scan quickly during a risk review meeting.

In this step, you will add conditional formatting so that risk ratings instantly stand out by color. A red cell screams "Critical" before anyone reads the text.

In this step, get ready to:

Color-code the Rating column with green, yellow, orange, and red fills.
Add a color scale gradient to the Risk Score column.
Verify the formatting triggers correctly with test data.

Color the Rating column

Conditional formatting rules evaluate each cell's value and apply a fill color when a condition is met. You will create four separate rules, one for each rating band.

Select cells H2:H20 (the Rating column).
Go to Home > Conditional Formatting > New Rule.
Select Format only cells that contain.
Set the condition to Cell Value > equal to > type Low.
Click Format > select the Fill tab > choose green.
Click OK to close the Format dialog, then OK again to save the rule.

That is your first rule complete. Now repeat the process three more times for the remaining ratings.

With H2:H20 still selected, go to Home > Conditional Formatting > New Rule.
Create a rule for Cell Value equal to Medium with a yellow fill.
Create another rule for Cell Value equal to High with an orange fill.
Create a final rule for Cell Value equal to Critical with a red fill.

Why four separate rules?

Each rule targets one exact text value. When the Rating formula outputs "Critical", only the red rule fires. This gives you precise control over each color without relying on a single gradient.

In risk review meetings, stakeholders can instantly spot which risks need immediate attention just by scanning for red and orange cells.

Rules not showing in the menu?

Make sure you have H2:H20 selected before creating each rule. If you accidentally clicked elsewhere, reselect the range and try again.

If you do not see the Fill tab in the Format dialog, make sure you clicked the Format button (not the OK button) in the New Rule dialog first.

Help me troubleshoot my conditional formatting rules in Excel.

Add a color scale to Risk Score

Color scales apply a gradient across an entire range of numeric values. Instead of discrete colors per label, the Risk Score column will shade from green (low scores) through yellow to red (high scores).

Select cells G2:G20 (the Risk Score column).
Go to Home > Conditional Formatting > Color Scales.
Select the Green-Yellow-Red color scale (green for low values, red for high).

What does the color scale do?

Unlike the Rating column's discrete rules, the color scale creates a smooth gradient. A score of 6 appears as a light yellow-green, while a score of 20 appears deep red. This gives you an at-a-glance sense of severity across the entire register without reading individual numbers.

Verify the formatting with test data

Time to confirm everything works together. You will enter test values that trigger the highest score and check that both the formula and the formatting respond.

Click cell E2 and enter 5 (Likelihood).
Click cell F2 and enter 5 (Impact).

Cell G2 should display 25 with a deep red color scale fill. Cell H2 should display Critical with a red fill.

Delete the test values in E2 and F2 to clear the row for real data.

G2 shows 25 but no color?

Go to Home > Conditional Formatting > Manage Rules to check that your color scale rule applies to G2:G20. If the range is wrong, select the rule and edit it.

If H2 shows "Critical" but has no red fill, check that the rule condition says equal to Critical (with a capital C). The comparison is case-sensitive to the formula output.

Help me fix my conditional formatting so it applies colors correctly.

Your register now gives instant visual feedback. Next, you will populate it with 8 real cybersecurity risks mapped to specific NIST 800-53 Rev 5 control families.

Document 8 Cybersecurity Risks Mapped to NIST 800-53

Your register now has automated scoring and visual indicators ready to go. The formulas and conditional formatting you built will spring to life the moment you start entering risk data.

In this step, you will populate the register with 8 information security risks, each tied to a specific NIST 800-53 Rev 5 control family and control identifier. This is the core analytical work of the register. It connects identified risks to the controls that address them.

In this step, get ready to:

Enter 8 risk IDs mapped to distinct NIST 800-53 Rev 5 control families.
Write risk descriptions using Cause-Event-Impact format and score each risk.
Sort the register by Risk Score to surface the highest-priority risks.

Populate risks with control mappings

Each risk in your register maps to one of the 20 NIST 800-53 Rev 5 control families. You will use 8 distinct families to demonstrate breadth across the framework.

Click cell A2 and type R-001.
In cell C2, click the dropdown arrow and select AC.
In cell D2 (Mapped Control column), type AC-2.

Continue entering the remaining 7 risks in rows 3 through 9. Use the following mappings:

R-002: Control Family AU, Mapped Control AU-2 (Audit and Accountability).
R-003: Control Family CM, Mapped Control CM-6 (Configuration Management).
R-004: Control Family IA, Mapped Control IA-5 (Identification and Authentication).
R-005: Control Family IR, Mapped Control IR-4 (Incident Response).
R-006: Control Family RA, Mapped Control RA-3 (Risk Assessment).
R-007: Control Family SC, Mapped Control SC-8 (System and Communications Protection).
R-008: Control Family SI, Mapped Control SI-3 (System and Information Integrity).

Why map risks to specific control IDs?

The NIST 800-53 control identifier format uses a two-character family ID followed by a number (e.g., AC-2, RA-3). Mapping to this level of specificity ties each risk directly to the control that addresses it.

This is what auditors look for during authorization decisions. A register that only says "Access Control" is too vague. Saying AC-2 (Account Management) tells exactly which control mitigates the risk.

Write risk descriptions and score each risk

Each risk description follows a Cause-Event-Impact structure. This format clearly links the control gap (cause) to what could happen (event) and the business consequence (impact).

Click cell B2 (Risk Description for R-001).
Type a description using Cause-Event-Impact format. For example: Because of inadequate account management procedures, unauthorized users may retain access after role changes, resulting in data exposure.
In cell E2 (Likelihood), click the dropdown and select a value from 1 to 5.
In cell F2 (Impact), click the dropdown and select a value from 1 to 5.

Notice that columns G (Risk Score) and H (Rating) auto-populate as soon as you enter both Likelihood and Impact. The Rating cell fills with color based on the score.

In cell I2 (Response Strategy), click the dropdown and select one of: Avoid, Mitigate, Transfer, or Accept.
In cell J2 (Response Action), type a brief description of how you will execute the strategy. For example: Implement automated account provisioning and quarterly access reviews.
In cell K2 (Risk Owner), type a role title responsible for the risk. For example: IAM Team Lead.
In cell L2 (Status), click the dropdown and select Open, In Progress, or Closed.

Repeat this process for rows 3 through 9 (R-002 through R-008). Each risk should have a unique description tied to its mapped control's purpose.

What is Cause-Event-Impact format?

This structure comes from ISO 31000 risk identification methodology. It forces you to articulate three things: the root cause (a control gap or weakness), the threat event (what an attacker or failure mode could do), and the resulting business impact.

A well-written risk statement makes the connection between missing controls and business consequences obvious. This is what makes your register actionable rather than just a list of worries.

Use varied scores across your 8 risks

Give your risks a spread of Likelihood and Impact values so the register demonstrates all four rating bands (Low, Medium, High, Critical). A mix of scores also makes the Risk Matrix heatmap more informative in the next step.

Sort by Risk Score and verify completeness

Click any cell in column G (Risk Score).
Go to Data in the menu bar.
Click Sort.
Set the Sort By field to Risk Score (Column G).
Set the Order to Largest to Smallest (descending).
Click OK.

Your highest-scored risks now appear at the top. The Critical (red) and High (orange) risks should be in the first few rows.

Scan all 8 rows to verify that every column (A through L) has data. No cell should be empty.
Confirm each row maps to a different control family. You should see 8 distinct two-character codes in the Control Family column (AC, AU, CM, IA, IR, RA, SC, SI).
Save your workbook.

Why sort by Risk Score?

Sorting by descending score surfaces the risks that need attention first. In practice, this prioritized view drives resource allocation decisions and determines which risks require immediate mitigation versus those that can be monitored.

Risk Score or Rating column still blank?

If column G or H shows no value for a row, check that both Likelihood (column E) and Impact (column F) have values selected from the dropdown. The formula only calculates when both inputs are present.

If you see a formula error, click the cell and check the formula bar. Make sure the formula in G2 is =IF(AND(E2<>"",F2<>""),E2*F2,"") and that it was dragged down correctly to row 9.

help me troubleshoot why my Risk Score formula isn't calculating

Your register now holds 8 documented risks with automated scoring and clear control mappings. Next up, you will build a 5x5 risk matrix heatmap that plots these risks visually by Likelihood and Impact.

Build the 5x5 Risk Matrix Heatmap

Your Risk Register sheet now holds 8 scored and color-coded risks. Numbers tell the full story, but a visual heatmap makes risk concentration instantly obvious to stakeholders.

A 5x5 risk matrix plots every risk by its Likelihood and Impact on a single grid. This is the standard visualization used alongside NIST 800-53 risk assessments to communicate which risks demand immediate attention.

In this step, get ready to:

Build a 5x5 grid with Likelihood and Impact labels.
Place your 8 risks in the correct grid cells by their scores.
Apply color-coded conditional formatting to create the heatmap.

Set up the matrix grid

The matrix needs two axes: Impact across the top (columns) and Likelihood down the side (rows). You will label these so the grid reads correctly at a glance.

Click the Risk Matrix sheet tab at the bottom of your workbook to switch to it.
Click cell B2 and type IMPACT →.
In cells B3 through F3, type the five Impact column headers:

B3: 1-Negligible
C3: 2-Minor
D3: 3-Moderate
E3: 4-Major
F3: 5-Catastrophic

Click cell A4 and type LIKELIHOOD ↓.
In cells A5 through A9, type the five Likelihood row labels (highest at the top):

A5: 5-Almost Certain
A6: 4-Likely
A7: 3-Possible
A8: 2-Unlikely
A9: 1-Rare

Why is 5 at the top?

Placing the highest Likelihood at the top means the most dangerous cell (Likelihood 5, Impact 5) sits in the top-right corner. This is the standard layout for probability-impact matrices, matching how risk professionals expect to read them.

Save the workbook.
Confirm your grid shows IMPACT → above five column headers and LIKELIHOOD ↓ beside five row labels.

Labels look misaligned?

Make sure IMPACT → is in B2 (not A2). The column A is reserved for Likelihood labels starting in row 4. If your headers shifted, clear the row and re-enter from B3.

Help me fix my risk matrix grid layout.

Fill in risk scores and place risks

Each cell in the 5x5 grid represents a unique Likelihood-Impact combination. You will fill in the score product and then tag each cell with the Risk IDs that land there.

For each cell in the range B5:F9, enter the product of its row's Likelihood number and its column's Impact number.
For example, cell B5 is Likelihood 5 x Impact 1 = 5. Cell F5 is Likelihood 5 x Impact 5 = 25.
Fill all 25 cells with their score products.

What do the score products represent?

Each number is the Risk Score a risk would receive if it had that exact Likelihood and Impact combination. This lets you visually locate any risk on the grid by matching its Likelihood row and Impact column.

Switch back to the Risk Register sheet and note each risk's Likelihood and Impact values.
Return to the Risk Matrix sheet. For each risk, find the cell that matches its Likelihood (row) and Impact (column).
Append the Risk ID to that cell's score. For example, if R-001 has Likelihood 4 and Impact 5, update cell F6 (row for Likelihood 4, column for Impact 5) to read 20 (R-001).
If two risks share the same Likelihood and Impact, place both IDs in that cell separated by a comma (e.g. 12 (R-003, R-005)).

Save the workbook.
Verify that all 8 Risk IDs (R-001 through R-008) appear somewhere in the B5:F9 grid.

Missing a Risk ID in the grid?

Double-check the Likelihood and Impact values on your Risk Register sheet. The row for Likelihood 4 is row 6 (not row 5). Remember the grid starts with Likelihood 5 at the top (row 5) and counts down to 1 at the bottom (row 9).

Help me map my risks to the correct matrix cells.

Color-code the matrix

The heatmap needs four color bands that match the rating thresholds from your Risk Register formulas. This makes the matrix instantly scannable.

Select cells B5:F9 (the entire 5x5 grid).
Go to Home > Conditional Formatting > New Rule.
Set the Style to Classic.
Select Format only cells that contain.
Set the condition to Cell Value less than or equal to 3.
Click Format, go to the Fill tab, select green, and click OK.
Click OK to save the rule.

Now add three more rules using the same steps. Keep B5:F9 selected and create a new rule for each:

Cell Value between 4 and 7 = yellow fill.
Cell Value between 8 and 14 = orange fill.
Cell Value greater than or equal to 15 = red fill.

Why these color thresholds?

The bands match your Rating formula from the Risk Register: Low (1-3) is green, Medium (4-7) is yellow, High (8-14) is orange, and Critical (15-25) is red. Using the same thresholds across both sheets keeps your risk communication consistent.

Save the workbook.
Your grid should now display a gradient from green in the bottom-left corner (low risk) to red in the top-right corner (critical risk). Cells with Risk IDs in the red zone represent your highest-priority risks.

Colors not showing on cells with Risk IDs?

Conditional formatting evaluates the cell's value. If a cell contains text like 20 (R-001), Excel may not recognize it as a number. Make sure the score product appears first in the cell. If colors still don't apply, try entering only the number (e.g. 20) and adding the Risk ID in a comment or note instead.

Help me fix my conditional formatting on the risk matrix.

Your risk register workbook is complete. You now have both a detailed tabular view and a visual heatmap that communicates risk posture at a glance. Ready to take it further? The Secret Mission adds residual risk tracking to align with full RMF deliverables.

Secret mission

Add Residual Risk Tracking with POA&M

Your risk register scores inherent risk, but what happens after controls are implemented? Extend your register with residual risk columns and a POA&M reference to align with the three core RMF deliverables: the SSP, SAR, and POA&M.

Clean Up Your Resources

This project runs entirely locally in Excel, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your workbook for future use or delete it entirely.

Resources you used:

NIST_800-53_Risk_Register.xlsx workbook file (saved locally).

Keep everything running

No action needed. Choose this if you plan to continue building on your risk register, adding more risks, or referencing it for future projects.

Your workbook stays where you saved it. Open it any time to update risks, add new control mappings, or refine your scoring.

Pause - I'll come back to this later

Since this is a local file with no running processes, there is nothing to pause. Your workbook is already at rest on your machine.

Close the workbook in Excel if it is still open.
The file remains saved on your computer and ready to reopen whenever you return.

Delete - I don't want to use this again

Remove the workbook file from your computer to start fresh.

Close the workbook in Excel if it is still open.
Delete the file by running this command in Terminal:

rm ~/Desktop/NIST_800-53_Risk_Register.xlsx

If you saved the file somewhere other than your Desktop, navigate to that location in Finder and move the file to Trash instead.

Nice Work!

You just built a cybersecurity risk register aligned with NIST SP 800-53 Rev 5. This is a core artifact in the Risk Management Framework that connects identified risks to specific security controls and justifies control selection during audits and authorization decisions.

You learned how to:

Built a 12-column risk register with automated scoring formulas, dropdown validation, and conditional formatting that color-codes risks from green to red.
Mapped 8 information security risks to specific NIST 800-53 Rev 5 control families and control IDs using Cause-Event-Impact risk statements.
Created a 5x5 risk matrix heatmap that visually maps all identified risks by likelihood and impact.
Added residual risk tracking with POA&M references aligned to RMF deliverables.

Ready to quiz yourself?