Build a Risk Register in Excel With Risk Scores

A risk register in Excel is a simple table: one row per risk, with a likelihood, an impact, a score and an owner. Excel does the scoring for you, labels each risk High, Medium or Low, colors the dangerous ones and counts what's still open. This guide is for project coordinators, team leads and anyone asked to "keep a risk log", and it builds the whole register with three formulas.

What goes in a risk register?#

A risk register lists the things that could go wrong, how likely and how serious each one is, and who is dealing with it. Each risk is one row. These columns cover most needs:

Many teams also add a response column (what you'll do about it) and a review date. Keep the register as one block with no empty rows, so sorting, filtering and formulas work on all of it.

Here is the example register used in this guide, in A1:H8:

How to calculate a risk score#

The risk score is likelihood multiplied by impact. In E2, type:

=C2*D2

Press Enter, then fill it down to E8 (select E2:E8 and press Ctrl+D; on a Mac, ⌘D). R-01 scores 4 × 5 = 20. With both scales running from 1 to 5, every score lands between 1 and 25.

Multiplying gives a big score only when a risk is both likely and serious. A rare but severe risk, like R-04, scores just 5. That's the point: it pushes the risks that need attention now to the top.

Turn the score into a rating with IFS#

IFS checks conditions in order and returns the result for the first one that's true. In F2, type:

=IFS(E2>=15,"High",E2>=8,"Medium",TRUE,"Low")

Fill it down. Scores of 15 to 25 become High, 8 to 14 become Medium, and 1 to 7 become Low. The final TRUE,"Low" is a catch-all: TRUE is always true, so any score that failed the first two tests gets Low.

Order matters. Check the highest band first. If you started with E2>=8, a score of 20 would stop there and be called Medium. These cut-offs are an example; many organisations set their own, so use the bands your team or policy agrees on. The IFS function guide and the article on how IFS replaces nested IF explain the syntax in more detail.

IFS needs Excel 2019 or later, or Microsoft 365. In older Excel, use nested IF, which gives the same results:

=IF(E2>=15,"High",IF(E2>=8,"Medium","Low"))

Stop typing mistakes with drop-down lists#

A typo like "Opne" or a likelihood of 6 breaks your counts. Add drop-down lists so people can only pick valid values:

1. Select C2:D50 (likelihood and impact).
2. Go to Data > Data Validation.
3. Under Allow, choose Whole number, between 1 and 5.
4. For the Status column, select H2:H50, choose List, and type Open,Monitoring,Closed in Source.

Now Excel rejects anything outside the list. The rating column stays a formula, so nobody types a rating by hand.

Color high risks with conditional formatting#

Conditional formatting changes a cell's color when it meets a rule. To color the ratings:

1. Select F2:F8.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
3. Type High and choose Light Red Fill with Dark Red Text.
4. Repeat with Medium and a yellow fill.

To color the whole row of every High risk, select A2:H8, choose New Rule > Use a formula to determine which cells to format, and enter:

=$F2="High"

The $ locks column F, so every cell in the row checks that row's rating. The conditional formatting guide shows more rule types, such as color scales.

Count risks with COUNTIFS#

COUNTIFS counts the rows that meet every condition you give it. It turns the register into a summary for a status meeting:

R-07 is High but has the status Monitoring, which is why the second count is 2, not 3. The COUNTIFS function guide and the article on COUNTIFS and SUMIFS with several conditions cover criteria like ">=10".

Build a simple risk matrix#

A risk matrix (or heat map) shows how many risks sit at each likelihood and impact. Put likelihood 5 to 1 in J3:J7 and impact 1 to 5 in K2:O2. In K3, type:

=COUNTIFS($C$2:$C$8,$J3,$D$2:$D$8,K$2)

Fill it across to O3 and down to row 7. The mixed references matter: $J3 always reads the likelihood from column J, and K$2 always reads the impact from row 2. In this register, the cell for likelihood 4 and impact 5 shows 1 (R-01). Add a color scale from Home > Conditional Formatting > Color Scales so busy cells stand out.

A risk register sits well next to a project budget in Excel, and both are core Excel skills for project managers.

Practise it#

The rating formula is the heart of the register. Practise nested IF and IFS in a live spreadsheet: you type the formula and it checks it instantly. Then learn to count with several conditions in the COUNTIFS and SUMIFS lesson and color rows with a formula rule. Try the IFS exercise on this page first.