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

Excel School made me great at work.
5/5

– Brenda

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

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

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

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

9 box grid - talent map in Excel - template video

9 Box grid for talent mapping – HR for Excel – Template & Explanation

9 box grid is a popular method for talent mapping. Download this free excel template for tracking and visualizing your employee performance & potential data.

In this article, I explain about powerful excel features like FILTER(), SORT() and CONCATENATEX() to create a similar 9 box talent map grid for your data.

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

      • Rajesh says:

        I tried adding a slicer filter for the mitigation step but the TEXTJOIN is not affected by it. I added a helper column called "Visible" using the AGGREGATE function but I am unable to think of a method to pass that on to the map.
        Could you please help, Chandoo?

        Thanks

  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

  3. Henrik B. Wieder says:

    Hi

    I've tried to get your formula to work, but likelihood / impact 1/1 does not seem to work.

  4. Sally says:

    Hi Chandoo

    Awesome instructions! Thank you so much, this really helped me.

    I was wondering if it would be possible to list the Risk ID number along with the Risk Title with a dash in between, rather than a bullet point? I have had a try at this but I keep getting a #VALUE error. I can see it's wrong but can't figure out what it should be instead. If you have time do you mind letting me know what I'm doing wrong?

    {=" - " & TEXTJOIN(CHAR(10)&" - ",TRUE,
    IF(RiskRegister[Likelihood]=$A17,IF(RiskRegister[Consequence]=F$3,CONCAT(RiskRegister[ID],RiskRegister[Risk Title]),""),""))}

    Thank you!

    Sally

    • Chandoo says:

      Hey Sally, You are welcome.

      I think the CONCAT inside TEXTJOIN is the culprit. Try this and hopefully you should see the ID too.

      {=" - " & TEXTJOIN(CHAR(10)&" - ",TRUE,
      IF(RiskRegister[Likelihood]=$A17,IF(RiskRegister[Consequence]=F$3,RiskRegister[ID]&RiskRegister[Risk Title],""),""))}

      • Sally says:

        Hi Chandoo
        You're a legend! Thank you so much! I had to make a minor tweak but otherwise it worked perfectly. Here is the tweaked version in case it helps anyone else:

        =TEXTJOIN(CHAR(10),TRUE,
        IF(RiskRegister[Likelihood]=$A8,IF(RiskRegister[Consequence]=C$3,RiskRegister[ID]&" - "&RiskRegister[Risk Title],""),""))

        Thank you again!

Leave a Reply