How to Create a Heat Map in Excel (4 Methods)

Last updated on June 11th, 2018

Heat maps are the visual representation which shows the comparison of a dataset based on some criteria in a table. In this article, we are going to see how to create a heat map in Excel. Here we will see different processes of creating heat map in Excel. We will also see the process of creating dynamic heat map in Excel.

You can create heat map manually in Excel by changing the color of each cell. The problem occurs when the values of each cells changes. Using the conditional formatting is the best way to create heat map in Excel. Here we will see the processes based on conditional formatting.

Creating Heat Map in Excel

1) Using Conditional Formatting (Fastest Way)

Let`s look into the below table where the information of a number of participants is given of a course. The course occurs in every year and in every month, we can see a different number of participants. The data table is shown below.

Now we are going to create a heat map for the table stated above. The process is given below.

  • First, select the data set of which you want to create the heat map. In this case, the dataset range is B2:F13

  • Now in the Home tab select the Conditional Formatting Under the Conditional Formatting option select the Color Scales option. Under the Color Scales option, you will see a different color option. Select whichever color option you want.

how to create a heat map in Excel

  • After selecting the color option your table will look like this.

how to create a heat map in Excel

Note: This is the fastest and easiest way to insert heat map in Excel. The problem with this heat graph is that there is too much color in this table. While sorting out data you want fewer colors to identify the desired data easily. So, this kind of heat map is not suitable where a large number of data exists.

2) Using Conditional Formatting with 3-Color Scale Rules

Here you are going to see another process of creating heat map in Excel. We are going to follow the same process but instead of choosing the color scale option we are going to improvise it.

  • Select the range as before which is B2:F13.
  • Now in the Home tab select the Conditional Formatting Under the Conditional Formatting option select the Color Scales option. In the drop-down option of Color Scales select More Rules.

  • In the New Formatting Rule Box that appears after selecting the More Rules, select the option Format all cells based on their values and select the 3-color scale style in the Format Style Our data ranges from 45-120. In the Minimum portion side, select the type as number and set the value 45 there. After doing this, press OK.

  • After doing these your data table will like the picture below.

  • Now if you want to hide the numbers of your data, select the cells where you placed the heat map and select the Format Cells option.

how to create a heat map in Excel

  • In the Format Cells option press on the Number tab and select Custom. In the type option type “;;;” and press OK.

  • You will get to see the below result.

3) Creating a Dynamic Heat Map using Scroll Bar

You can insert a scroll bar in your table and create a heat map with it to look it more dynamic. Here, in this example, we will insert a scroll bar in a new worksheet which will show data from another worksheet. Let`s say the worksheet named Table is our primary worksheet. We want the same data along with a scrollbar and heat map in another worksheet which is Sheet4. The process is given below.

  • Create a new worksheet and copy the months from another worksheet here.

  • In the Developer tab under the Control option select Insert and choose the ScrollBar.

  • In your worksheet select an area to insert the ScrollBar.
  • Now right click on the scroll bar and select the Format Control

  • In the Format Control dialog box, do the following and press OK.

how to create a dynamic heat map in Excel

In cell B1 of Sheet4 write down the formula, =INDEX(Table!$B$1:$F$13,ROW(),Table!$H$2+COLUMNS($B$1:B1)-1) and press enter. Now drag down this formulated cell in the rest of the cells of Sheet4. Let’s say we want to show only 3 years in the worksheet and by scrolling the scrollbar the rest of the years will come. So, drag the formulated cell B1 from B1 to D13.

  • Now in Sheet4 create the heat map. Now by scrolling the scrollbar, you will see a dynamic heat map in your worksheet.

how to create a heat map in Excel

4) Creating a Dynamic Heat Map using Option Buttons

You can create a dynamic heat map using the Radio Buttons option. To do this follow the process below.

  • In the Developer tab under the Control, option select Insert and choose the Option Button.

  • Repeat the process to get another option button. Now place these option buttons close to each other and edit their names as Larger values and Lower Values.

  • In the Larger Values button click on to the right button of your mouse and select Format Control Box. In the Format Control Box click on to the Control option. In the value tab, click on to Checked and in the Cell Link press $H$3.

  • Do the same thing with Lower Values
  • Now, we will apply conditional formatting in the range B2:F13. Select the range and in the Conditional Formatting option click on to New Rule.

how to create a heat map in Excel

  • In the New Formatting Rule dialogue box select Use a Formula to determine which cells to Format and write the formula, =IF($H$3=1,IF(B2>=LARGE($B$2:$D$13,10),TRUE,FALSE)) in Format values where this formula is true and then select a color.

  • You will get to see the below result after this.

  • Now for highlighting the lower values, do the same process with the code, =IF($H$3=2,IF(B2<=SMALL($B$2:$D$13,10),TRUE,FALSE)).

how to create a heat map in Excel

  • After this, you will get to see the below result.

Related Articles

How to Highlight Every Other Row in Excel

Download The Working File

Conclusion

With the help of Heatmap, you can easily distinguish a cell with its value from other cells. In this article, we showed you some different processes of creating a heat map. Hope this article will be useful for you. Stay tuned for more getting for useful articles like this one.


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply