How to Create a Risk Heat Map in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

A heat map is a visual representation of the comparison of a dataset based on color. If you are looking for some special tricks to create a risk heat map in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to create a risk heat map in Excel. In this article, we’ll discuss three methods to create a risk heat map in Excel. Let’s follow the complete guide to learn all of this.


Watch Video – Create a Risk Heat Map in Excel



How to Create a Risk Heat Map in Excel: 3 Easy Ways

We will use three effective and tricky methods to create a risk heat map in Excel in the following section. This section provides extensive details on three methods. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge.


1. Applying Conditional Formatting to Create a Risk Heat Map in Excel

Here, we will demonstrate how to create a risk heat map in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing students’ math, chemistry, and physics scores. Now, we are going to identify the risk level of every student’s score.

Applying Conditional Formatting to Create a Risk Heat Map in Excel

We consider a mark less than 40 as poor and denote it as a high-risk level. If the obtained mark falls between 40 and 60, we will consider it a medium-risk level, and if the mark falls between 80 and 100, we will consider it a low-risk level. Let’s walk through the steps to create a risk heat map in Excel.

Applying Conditional Formatting to Create a Risk Heat Map in Excel

📌 Steps:

  • Firstly, select the range of the cells C5:E13. Go to the Home tab, select the Conditional Formatting and select the New Rule option.

  • When the New Formatting Rule dialog box appears, select the Format all cells based on their values option. Then, select the 3-Color Scale in the Format Style box.
  • Next, enter the numbers 40,60, and 80 in the Value box and select your desired color from the Color option.
  • Then, press OK.

Applying Conditional Formatting to Create a Risk Heat Map in Excel

  • Finally, we will be able to create a risk heat map like the following.

Applying Conditional Formatting to Create a Risk Heat Map in Excel

Read More: How to Make a Heatmap in Excel


2. Using Conditional Formatting with 2-Color Scale Rule

Here we will use another method to create a risk heat map by using Conditional Formatting with a 2-Color Scale. We consider a mark less than 60  as poor and denote it as a high-risk level. As long as the mark falls between 80 and 100, we will consider it low-risk. Let’s walk through the steps to create a risk heat map in Excel.

📌 Steps:

  • Firstly, select the range of the cells C5:E13. Go to the Home tab, select the Conditional Formatting and select the New Rule option.

Using Conditional Formatting with 2-Color Scale Rules

  • When the New Formatting Rule dialog box appears, select the Format all cells based on their values option. Then, select the 2-Color Scale in the Format Style box.
  • Next, enter the numbers 60 and 80 in the Value box and select your desired color from the Color option.
  • Then, press OK.

  • Finally, you will be able to create a risk heat map like the following.

Using Conditional Formatting with 2-Color Scale Rules


3. Creating a Risk Heat Map Using Form Controls

Here, we will use another method to create a risk heat map by using the Option Button in Form Controls. In this method, we combine IF, SMALL, and LARGE functions. We consider a mark less than 60  as poor and denote it as a high-risk level. As long as the mark falls between 80 and 100, we will consider it low-risk. Let’s walk through the steps to create a risk heat map in Excel.

📌 Steps:

  • Firstly, go to the Developer tab, select Insert, and choose the Option Button.

Creating a Risk Heat Map Using Option Buttons

  • Continue the process to get another option button and change the names of the two options to Low and High.

  • Next, put the mouse cursor on the Low button, right-click and select the Format Control box.

Creating a Risk Heat Map Using Option Buttons

  • When the Format Object dialog box appears, click on Checked and press $H$3 in the Cell Link.
  • You have to do the same thing with the High button.

  • Now, select the range of the cells C5:E13. Go to the Home tab, select the Conditional Formatting and select the New Rule option.

Creating a Risk Heat Map Using Option Buttons

  • When the New Formatting Rule dialog box appears, select the Use a Formula to determine which cells to Format option. Then, write the following code in Format values where this formula is true box.

=IF($H$3=1,IF(C5>=LARGE($C$5:$E$13,10),TRUE,FALSE))

Here, $H$3=1 indicates the Low button. The LARGE function returns the n-the largest value from the dataset. Using the IF function, we will get only the value that is above 80.

  • Next, select your desired color from the Format option.
  • Then, press OK.

  • As a result, you will get the following output where the values above 80 are highlighted in green.

Creating a Risk Heat Map Using Option Buttons

  • When the New Formatting Rule dialog box appears, select the Use a Formula to determine which cells to Format option. Then, write the following code in Format values where this formula is true box.

=IF($H$3=2,IF(C5<=SMALL($C$5:$E$13,10),TRUE,FALSE))

Here, $H$3=2 indicates the Low button. The SMALL function returns the n-the smallest value from the dataset. Using the IF function we will get only the value that is less than 56.

  • Next, select your desired color from the Format option.
  • Then, click on OK.

  • As a result, you will get the following output where the values below 56 are highlighted in yellow.

Creating a Risk Heat Map Using Option Buttons


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may create a risk heat map in Excel. If you have any queries or recommendations, please share them in the comments section below.


Related Articles


<< Go Back to Heatmap in ExcelData Visualisation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo