Skip to main content
Risk Heat Map

How to Create a Risk Heatmap in Excel – Part 2

In the previous article (part 1), I’ve introduced the concept and possible applicability of a risk heat map, when capturing and managing operational risk. This article explains how to achieve the two heat maps described in part 1, including the data setup and necessary adjustments in Excel in order to plot all the risks (roughly 100) into an ineligible chart.

The idea is that you can reuse the example heat map table, populate it and score your relevant risks and be able to see the result in the heat map chart.

Step 1 – Risk Data Setup

The first step is to create a spreadsheet to record the relevant risks. The sheet I use has the following column headings:

  • Risk ID: unique for each risk
  • Dept Ref: short reference to distinguish each department
  • Risk Type: description of the risk type e.g. an applicable generic risk
  • Business Unit: this may or not be the department name (in this example it is assumed so)
  • Risk Description: self explanatory, this the goal is to record the risk description
  • Probability: ranges from 10 to 40. See the risk ratings table below
  • Impact: ranges from 10 to 40. See the risk ratings table below
  • Risk Score: corresponds to the product of probability rating scores and the impact rating scores
  • Concat: used for the charts, it’s simply a concatenation of the “Probability” with “Impact” columns
  • RiskID: used for the charts, same as “Risk ID” but without the leading “R” i.e. “R1” becomes “1”
  • Probability (%): used for the charts, macthes the value in column probability with a corresponding % which is in sheet “Risk Ratings”, using a vlookup function

Once you are done setting up the necessary columns, make sure you save the file as a macro – File > Save As > Save as Type “Excel Macro-Enabled Workbook (*.xlsm).

Step 2 – Understanding Sheet “Risk Ratings”

Sheet “Risk Ratings” contains the different scores, descriptions and criterias used for the “Probability” and “Impact” dimensions. Note that in this example sheet, I am using a 4 score rating system (10, 20, 30, 40), which correspond in the risk matrices to “Low, Medium, High, Very High”. Some firms use a 5 score rating system, for example Low, Medium, Medium-High, High, Very High.

Risk Management Guru - Risk Ratings

Step 3 – Fill in sheet “Risk Assessment Data”

The next step is to fill in your risk assessment data. The spreadsheet is pre-filled with dummy example data that you should replace with your own. My advice is that you replace (overwrite) the existing risk data instead of deleting all entries and creating new ones – this is the best option to make sure the heat map displays correctly. Also note that the dummy risk entries have different impact and probability scores. This will induce some level of risk dispersion in the risk heat map which is useful to understand the example.

Risks Assessment Data

 

Step 4 – Understanding sheet “Heatmap Table”

The heat map table below displays the same risk data only a more summarised way, yet also allowing a graphical representation of risks in a RAG scale. The heat map table was created following two distinct steps:

  1. Populate the table: using function countif(), the table is filled crossing all possible combinations of row versus column (e.g. 10×10) which origin in the “Risk Assessment Data” sheet
  2. Applying colour scales to the heat map: using Excel native function “Conditional Formatting > Color Scales“. The standard function will apply predetermined colours but you can adapt and use your custom colours

Excel Risk Heat Map Table

 

Step 5 – Update Chart Data and Labels

After filling in your risk assessment data as explained in step 3, go to sheet “Risk Factor Graph” and click on button “Update chart data and labels“. If everything is correctly input in sheet “Risk Assessment Data”, your heat map should plot correctly and display your risks in your Red, Amber and Green (RAG) chart.

Risk Heatmap Example

Conclusion

Even though Excel includes several pre-made charts, when you have a considerable amount of data (e.g. 100 risks) to plot in a chart, you might face difficulties and issues displaying them. Part 1 of this article and Part 2 in this article explain how to achieve a simple yet populated risk heat map using Excel.

Please comment below, we look forward to get your feedback on this solution and if you were able to apply it to your real life challenges.

 

If you liked this article, please donate below and contribute for this blog to continue alive. Thank you in advance!

 

 

Download this Example Risk Heat Map

Click here to download the Excel spreadsheet (zip format). Note: you must enable macros in Excel in order to run this file.

Antonio Caldas

Program/Project/HR and Risk manager with 15+ years mix-industry, with a particular emphasis in Banking & Financial Services. Active in risk management, market risk control, front office risk management, product control, change and transformation management, business analysis and business process improvement for global capital markets and investment banking, covering a multiple range of asset classes.

29 thoughts on “How to Create a Risk Heatmap in Excel – Part 2

  1. in the heat map of step 5, you should interchange the wording of the high-left (high potential, not likely), with the low-right (likely, low potential).

  2. Antonio – Excellent information and process, thanks for sharing.
    I am currently working through the ERM process and found this to be extremely helpful, but did have a question or two – would it be possible for you to reach out to me via email to discuss?
    Thanks again!

  3. Thanks for the very useful article templates. Your instructions say to score 10,20,30,40 and your templates work off these values, but your map has a scale up to 50. So it makes the high risks look less risky – because there’s nothing out out in the 50 zone. Have I done something wrong here? Please explain.

    1. Dear Virgina, thank you for your feedback and for flagging indeed an important point. The reason why I intentionally left the chart with a 5 score grade in both probability (or likelihood) and impact is that, fundamentally, some firms will follow a 5 rating approach, while others will use 4 score system. I understand your point of making the high risks looking less risky (even though plotted towards the correspondent “MITIGATE” quadrant). However, this template was built with the goal of being a basis for each person to adapt for their specific needs. When building it, I found that it would be more cumbersome to include a chart with 4 scale rating approach, and then someone wanting to adapt it to 5 rating system, than the other way around.
      Thank you again for your feedback and good luck with your Risk Management work.
      Best regards,
      Antonio Caldas

  4. Thanks Antonio, it is very useful. I have noticed that the chart has impact as X-axis and likelihood as Y-axis. The convention, at least in the study I am doing, is that impact is as Y-axis. Anyway, great tool. Thanks!

  5. Hi Antonio,

    Great chart and risk tool and thanks for taking the time to create this. I note from other responses that this is a 5 score grade chart but If I wanted this to be a 4 score chart, how can I do this? I have tried to modify it but I get a debug error (my Excel skills aren’t that great!) Appreciate your help and insight. Thank you!

  6. Hey,

    Thanks for the template, but I am actually unable to update the risk factor graph on my Mac, it says “method or data member not found”, I copied the probabilities and impacts into the sheet but didn’t change anything else, and took out the last few rows that I didn’t need. Urgent help to update that would be appreciated.

    Thanks

    1. Hi Parms, I’m really sorry to hear that. I haven’t tested this heatmap template in Mac systems but will try to see what I can do.
      Thank you for visiting Risk Management Guru and for your feedback.
      Best regards,
      Antonio

  7. I have found the template very helpful and the instructions informative. Is it possible to have text labels on the heatmap instead of R1, R2 etc ? If it is possible how can this be achieved ? Thanks Rebecca

    1. Hi Rebecca, thanks for your feedback and sorry for the late reply. Yes, you can change these labels by doing the following:

      1 – open the excel template provided in the article
      2 – in sheet “Risk Factor Graph”, locate cell AT3 to MG3. If you right-click on this range and select option “Format Cells > Custom” you will notice that these cells are defined to appear as “R” followed by a numeric. You can change this accordingly as you require
      3 – also, the numbers which are concatenated to the letter “R” are set from range AT50 to QD50

      You will be able to change R1, R2 etc by changing cells (including format) in range AT3 to MG3. However, please note that this chart has been optimised to display small text labels such as R1, R2, etc. If you decide to include larger labels, it might turn your chart unreadable.
      Best,
      Antonio

  8. It’s a рity you ⅾon’t have a donate button! I’d withⲟut a doubt donate to
    this outstanding blog! I suppose for now і’ll settle for bookmɑrking and adding your RSS feed to my Google account.
    I look forward to new updates and will share
    this site with my Faϲebook group. Talk soon!

  9. Hello, I wonder if you have done a risk assessment graph showing the inherent, controls and residual risks.

    Thanks,
    Jay

  10. Thanks Antonio, i have a question regarding Label position overrides: how can you determine override, in relation with position, thank you.

    1. Hi Tinh.

      Thanks for your comment. Please check the VBA in the spreadsheet. I built the chart plot by setting the incremental space between labels, to override the issue of having to many risks accumulating in the same space.

      Thanks for visiting Risk Management Guru!
      Regards.
      Antonio

  11. great tool.. the organisation already uses scales 1,2,3,4,5 for both Probability & Impact, was wanting to use your heat map.. what do I need to do to permit 1-5 scale being used ?

  12. I am trying to adjust the heat map to be on a 5 grade scale but intervals of 1 as opposed to 10, but I am having difficulty. Is there any easy way to do this? I adjusted the axis ranges but it always seems to reset when I run the macro. Thanks for any feedback.

  13. Hi Antonio, I want to introduce management and control scoring as well along with Impact and likelihood. Will you be able to help with this?

  14. Dear Antonio,

    This is great information; however, I am having trouble accessing the template, please would you be able to help?

  15. Hello Antonio,

    Just like Brian, I am trying to adjust the heat map to be on a 5 grade scale but intervals of 1 as opposed to 10, but I am having difficulty. Is there any easy way to do this? I adjusted the axis ranges but it always seems to reset when I run the macro. Thanks for any feedback.

  16. Hello Antonio,
    Thank you for sharing this blog. Its very useful.

    My organisation has multiple offices across the globe and i would like to create a heat map that would give a risk rating for each office based on the crisis incidents that occured in the last 5 years. The approach for this model would most certainly apply however do you have any thoughts on how do i go about creating the heat map for the “Business Units” in this model rather than the risks themselves.

    Crisis incidents could be any disruption to service due to Natural calamities, threat to physical security, Infrastructre failure impacting operations, etc.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox GDPR is required

*

I agree