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.
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.