Risk Register Template in Excel: Scoring, 5×5 Heat Map, Owners, Responses and Residual Risk
Build a risk register template in Excel: how to word risks, likelihood and impact scales, score and rating formulas, a 5×5 heat map with COUNTIFS, response strategies, residual risk, contingency using expected value, and a worked project example.
Quick answer: a risk register template in Excel is a table with one row per risk: ID, description (cause, event, effect), category, owner, likelihood and impact on a 1–5 scale, a score (likelihood × impact), a rating (low, medium, high, critical), a response (avoid, reduce, transfer, accept) with actions and due dates, status, and the residual score after the actions. A 5×5 heat map built with COUNTIFS shows where the risks sit, and the top-scored risks are reviewed at every project meeting.
The template works for projects, product launches, events and day-to-day operations. The scales and thresholds below are common choices, not a standard you must follow; agree on your own at the start. The example is fictional.
Step 1: Write risks so they can be managed
A useful risk description has three parts: because of a cause, an event may happen, which would have an effect. Compare:
- Weak: “Payments.”
- Useful: “Because the payment provider needs business verification, approval may be delayed, which would push the store launch date.”
The second version tells you who to call, what to do now and how to know if it is happening. Keep issues (things already happening) in a separate list; a risk register is about what might happen.
Step 2: Agree on scales
| Score | Likelihood | Impact on schedule | Impact on cost |
|---|---|---|---|
| 1 | Rare (under 10%) | Less than 1 day | Under $500 |
| 2 | Unlikely (10–30%) | 1–5 days | $500–$2,000 |
| 3 | Possible (30–50%) | 1–2 weeks | $2,000–$5,000 |
| 4 | Likely (50–80%) | 2–4 weeks | $5,000–$15,000 |
| 5 | Almost certain (over 80%) | More than a month | Over $15,000 |
Adjust the money and time bands to the size of your project; what matters is that everyone scores against the same definitions. Use the highest applicable impact (schedule, cost, quality, safety, reputation).
Step 3: The register table
Create a table (Ctrl+T) named Risks:
| Column | Field | Input or formula |
|---|---|---|
| A | ID | R01, R02… |
| B | Description | Cause – event – effect |
| C | Category | Technical, supplier, people, financial, legal, external |
| D | Owner | One person |
| E | Likelihood (1–5) | Dropdown |
| F | Impact (1–5) | Dropdown |
| G | Score | =[@[Likelihood (1–5)]]*[@[Impact (1–5)]] |
| H | Rating | =IFS([@Score]>=20,"Critical",[@Score]>=10,"High",[@Score]>=5,"Medium",TRUE,"Low") |
| I | Response | Avoid / Reduce / Transfer / Accept |
| J | Actions | What will be done, by whom |
| K | Action due | Date |
| L | Trigger / early warning | Sign that the risk is materializing |
| M | Status | Open / Monitoring / Closed / Occurred |
| N–O | Residual likelihood and impact | Expected values after the actions |
| P | Residual score | =N5*O5 |
| Q | Last reviewed | Date |
Color the Rating column with conditional formatting (green, yellow, orange, red), and flag stale entries with =IF(TODAY()-[@[Last reviewed]]>30,"Review","").
Step 4: Worked example — online store launch
| ID | Risk | L | I | Score | Rating | Response |
|---|---|---|---|---|---|---|
| R01 | Key developer leaves before launch, delaying the build | 2 | 5 | 10 | High | Reduce: document code weekly, line up a backup contractor |
| R02 | Payment provider approval delayed, pushing the launch | 3 | 4 | 12 | High | Reduce: submit documents now, set up a second provider |
| R03 | Product content not ready, launching with thin pages | 4 | 3 | 12 | High | Reduce: weekly content quota, launch with top 50 products first |
| R04 | Design costs exceed budget | 3 | 2 | 6 | Medium | Reduce: fixed-price quote, limit revision rounds |
| R05 | Hosting outage on launch day | 1 | 5 | 5 | Medium | Transfer/Reduce: managed hosting with SLA, status monitoring |
| R06 | Launch traffic lower than planned | 3 | 3 | 9 | Medium | Reduce: email list warm-up, launch offer |
Three high risks (R01–R03) get attention first. After the actions, the team expects R02 to drop to likelihood 2 (residual score 2 × 4 = 8, medium) and R03 to likelihood 2 (2 × 3 = 6, medium). Recording both the current and residual score shows whether the planned actions are worth their cost.
Step 5: The 5×5 heat map
On a separate sheet, put impact 1–5 across row 3 (B3:F3) and likelihood 5 down to 1 in column A (A4:A8). In each cell:
=COUNTIFS(Risks[Likelihood (1–5)],$A4,Risks[Impact (1–5)],B$3,Risks[Status],"<>Closed")
Apply conditional formatting based on the product of the row and column headers (=$A4*B$3>=10 for orange, >=20 for red), so the grid is colored even where the count is zero. In the example, the cells (likelihood 2, impact 5), (3, 4) and (4, 3) each hold one high risk. To list the IDs inside a cell instead of a count, Excel 365 users can use =TEXTJOIN(", ",TRUE,FILTER(Risks[ID],(Risks[Likelihood (1–5)]=$A4)*(Risks[Impact (1–5)]=B$3),"")).
Step 6: Contingency with expected value
For risks with a clear cost impact, estimate a probability and a cost, and multiply:
- Expected value:
=Probability*Cost_impact - Contingency reserve: the sum of expected values of the open risks you decide to accept or only partly reduce.
For example, if R02 has a 30% chance of causing $8,000 of extra cost (lost sales and rework), its expected value is $2,400; R04 at 40% × $3,000 adds $1,200. A contingency of about $3,600 for these two is a defensible starting point. Add it to the budget tab of your project management template.
Step 7: Make the review a routine
- At every status meeting, review risks rated High or Critical and any whose trigger has appeared.
- Update scores and statuses; add new risks raised during the meeting.
- Close risks that can no longer happen (for example, after the milestone has passed).
- If a risk occurs, move it to the issues log with an owner and action, and note what was learned.
Record decisions and new actions in your meeting minutes, and check whether schedule risks change dates on the Gantt chart.
Common mistakes
- Vague risk names (“Budget”) that cannot be acted on.
- No owner or a team as owner.
- Scoring once at kickoff and never again.
- Only listing risks without actions, triggers or due dates.
- Mixing issues and risks in one list, which hides what is actually happening.
FAQ
Should I use a 3×3 or 5×5 matrix?
A 3×3 matrix is quicker for small projects; a 5×5 matrix separates risks better when you have many of them. Pick one and stick with it so scores are comparable over time.
What is the difference between a risk register and a RAID log?
A RAID log tracks Risks, Assumptions, Issues and Dependencies together. The risk register is the “R” part, usually with more detail on scoring and responses.
Does this work in Google Sheets?
Yes. COUNTIFS, IFS, TEXTJOIN, FILTER and conditional formatting are available, and the register can be shared with the whole team.
More guides: Project Management.