fbpx

Free Excel Risk Map Template

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

Risk map template for Excel - demo

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

Example risk register - Excel Template

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.

blank risk map grid

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

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Weighted average formula in excel

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

3 Responses to “Free Excel Risk Map Template”

  1. Ron says:

    Why didn't you include the mitigation or risk IDs in the chart?

    • Chandoo says:

      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.

  2. shashi kumar says:

    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

Leave a Reply