Risk comes from not knowing what you are doing.
Warren Buffet
If you ever ask a project manager what they are up to, they will tell you “I have no idea“. So risks are quite common in project management. That is why I made this awesome free Excel risk map template to keep track and visualize risks.
Download Risk Map Template
If you just want risk map template, click here to download it.
For more templates on Project Management, click here.
Create your own Risk Tracker & Risk Map in Excel – Tutorial
If you want to make something similar for your work situation, then follow this tutorial.
1. Set up your risk register. For this you could use Excel Tables. Just add necessary columns – Risk ID, description, impact, likelihood and any other columns you want. Here is a sample risk register. Imagine, this table is named risks
2. Make a 5×5 empty grid and color it. In a separate Excel tab, create 5×5 (or 4×4 etc.) grid and color it as per risk color coding you follow. Make sure you add the Impact & Likelihood scale. This is how it would look.
3. Write formulas to print matching risks. We can use TEXTJOIN() formula to get all the risks that have a given impact and likelihood value.
Note: TEXTJOIN() is available only in Excel 2019 & 365.
If you do not have TEXTJOIN(), please use the VBA Excel Risk Map Generator.
For example, the formula in D2 cell (Likelihood=5, Impact=1) would be,
="• " & TEXTJOIN(CHAR(10)&"• ",TRUE, IF(risks[Likelihood]=$C2,IF(risks[Impact]=D$7,risks[Title],""),""))
This is an array formula, so press CTRL+Shift+Enter to get the result.
How does this formula work?
- We use two nested IF conditions to check if risks[Likelihood] and risks[Impact] matches $C2 and D$7 respectively.
- If they both match, we get risks[Title], else blank space “”
- We then pass this resulting array to TEXTJOIN() which combines all the matching risks with the CHAR(10)bulletspace.
- We add an extra bulletspace at start for the first risk (as TEXTJOIN will only print bullet symbol between risks, but not at front)
- CHAR(10) is the newline symbol. So when you word wrap the cell, Excel prints each risk in a new line.
What about cells with no risks? Would they not print a bullet point?
You are right my dear. We can use conditional formatting to suppress such cells. We can set the cell format code ;;; to those cells.
Related: Make cell value disappear with custom format codes
How to make this template – Video
Please watch this video tutorial to learn how the template is constructed. You can use the ideas to make something similar for your work easily.
Download Risk Map Template
- FREE Risk Map Template – works in Excel 365, 2019 with TEXTJOIN
- Excel Risk Map Generator – uses VBA, works in most versions of Excel
- Project Management Templates – 24 templates to help you manage projects better.
3 Responses to “Free Excel Risk Map Template”
Why didn't you include the mitigation or risk IDs in the chart?
You can easily add such detail by modifying the TEXTJOIN function. Another way to use them is to add a slicer to highlight all risks that have a specific mitigation strategy or team member assigned to them. I left out those bits fto keep the article short.
Can you create a sheet in live stock market data price change with profit and loss graph with time. which could indicate live profit and loss in each time frame 5minute, 10 minute, 15 minute, 30minute, hourly with some modifications