How to Create a Risk Register in Excel (Step by Step)
What is a risk register?
A risk register is the central record of everything that could affect a project's cost, time, quality or safety. It shows who owns each risk, how serious it is, and what is being done about it. Clients, auditors and funders often ask to see it.
Which columns should you include?
| Column | Purpose |
|---|---|
| Risk ID | Unique reference, e.g. R-001 |
| Description | Cause → event → effect, in one sentence |
| Category | Technical, commercial, safety, environmental, etc. |
| Owner | The person responsible for managing it |
| Likelihood (1–5) | How probable the risk is |
| Impact (1–5) | How severe the consequence would be |
| Score | Likelihood × Impact (1–25) |
| Response | Avoid, reduce, transfer or accept — and the action |
| Status / review date | Open or closed, and when it is next reviewed |
How do you calculate the risk score?
Multiply the two ratings. In Excel, if likelihood is in column E and impact in column F, enter =E2*F2 in the score column. A common banding is: 1–4 low, 5–9 medium, 10–16 high, 20–25 critical.
Worked example
| Risk | Likelihood | Impact | Score | Rating |
|---|---|---|---|---|
| Late utility relocation delays road works | 4 | 4 | 16 | High |
| Steel price increase | 3 | 3 | 9 | Medium |
| Minor design query from client | 2 | 1 | 2 | Low |
How do you build a heat map in Excel?
Create a 5×5 grid with likelihood on one axis and impact on the other. Use COUNTIFS to count how many open risks fall in each cell, then apply conditional formatting colours from green (low) to red (critical). The heat map updates every time a score changes.
Common mistakes
- Writing vague risks such as "delays" instead of a clear cause and effect.
- No named owner, so nobody acts.
- Never re-scoring risks after mitigation.
- Letting the register go stale between meetings.
Save time: our Risk Register Template Excel with Heat Map has all of this built in — scoring, banding, an automatic 5×5 heat map and a summary dashboard.
Frequently asked questions
What columns should a risk register have?
At minimum: risk ID, description, category, owner, likelihood, impact, risk score, response/mitigation, status and review date.
How is a risk score calculated?
Most registers multiply likelihood by impact, each scored 1–5, giving a score from 1 to 25 that is grouped into low, medium, high and critical bands.
How often should a risk register be updated?
Review it at every project progress meeting, typically weekly or fortnightly, and whenever a major change occurs.
Further reading: Project Management Institute · ISO 31000 Risk Management