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

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.

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.

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.

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

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

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.

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

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.

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

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

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

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.

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

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.

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

• Make Geographic Heat Map in Excel
• How to Create a Zip Code Heat Map in Excel

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