How to Create a Heat Map in Excel

a simple heat map in excel

a simple heat map in Excel

And, when it comes to Excel, we have a different way to create a heat map or a template.

So today in this post, I’d like to share with you 3 different ways which you can use to create a heat map in Excel.

1. Steps to Create a Heat Map in Excel using Conditional Formatting

Data Table To Create A Excel Heat Map Template Using Conditional Formatting Color Scales

Here's how to do it:

  • Select the data on which you want to apply heat map.
  • Go to Home Tab ➜ Styles ➜ Conditional Formatting.
  • In conditional formatting options, selects color scales. You have six different types of color scales to create a heap map.
Six Conditional Formatting Color Scales To Create A Excel Heat Map Template

It applies a simple heat map just like below.

a simple heat map in excel

And if you want to hide number the follow use below steps. 

  • Now, the next step is to hide numbers from the table.
  • Select heat map data and open formatting options (Ctrl + 1).
  • In the number tab, go to custom and enter ;;; in type.
Custom Formatting To Create A Excel Heat Map Template Using Color Scales
  • Click OK.
A Simple Excel Heat Map Template Using Color Scales

By using above steps you can easily convert your data table into a heat map.

2. Steps to add a Heat Map in a Pivot Table

  • Select any of the cells in the pivot table.
  • Go to Home Tab ➜ Styles ➜ Conditional Formatting.
  • In conditional formatting options, selects color scales.

Further, if you want to hide numbers (which I don't recommend) please follow these simple steps.

  • Select any of the cells in the pivot table.
  • Go To Analyze Tab ➜ Active Field ➜ Field Settings.
Change Pivot Table Number Format To Create A Excel Heat Map Template
  • Click on number format.
  • In number tab, go to custom and enter ;;; in type.
  • Click OK.

3. Steps to Create a Dynamic Heat Map in Excel

You can create a dynamic heat map if you want to hide/show it according to your need.

a dynamic heat map template in excel

Below is the table we have use to create a dynamic heat map.

Table To Create A Excel Heat Map Template

And following things we need to incorporate.

  1. Option to switch between heat-map and numbers.
  2. Auto update table with heat map when add new data.

Here's how to do this:

Insert A Check Box To Create A Dynamic Excel Heat Map Template
  • Now, select the entire data range in the table.
  • Go to Home Tab ➜ Styles ➜ Conditional Formatting.
  • In conditional formatting, go to Color Scales -> Select more rules.
Select More Rules To Create Excel Heat Map Template
  • Here you have a new formatting rule pop-up.
  • From the Format Style drop down, change format style to 3-Color Scale.
  • Now change the type to “Formula” for the minimum, midpoint and maximum values and insert following formulas.

Minimum:

=IF($K$1=TRUE,MIN($B$2:$H$25),FALSE)

Midpoint:

=IF($K$1=TRUE,AVERAGE($B$2:$H$25),FALSE)

Maximum:

=IF($K$1=TRUE,MAX($B$2:$H$25),FALSE)

Use Conditional Formatting Pop Up Box To Create Excel Heat Map Template
  • In the end, click OK.

Now you have a dynamic heat map which you can control with a check box.

final dynamic heat map in excel using a check box

And if you want to hide numbers when you tick mark checkbox you need to create a separate conditional formatting rule.

Here's how to do this: 

  • Open conditional formatting new rule dialog box.
  • Select “Use formula to select which cell to format” and enter the below formula.

=IF($K$1=TRUE,TRUE,FALSE)

Use Conditional Formatting Pop Up Box To Create Excel Heat Map Template
  • Now, specify custom number formatting.
Custom Formatting To Create A Excel Heat Map Template Using Color Scales

After applying all the above customization you will get a dynamic heat map.

More Information

These ate some links to help you out while choosing your favorite color scheme.

Expert Tip: When you take a printout of a heat map on a paper it looks really nasty especially when you are using a black and white printer.

Sample File

Conclusion

Imagine if you are looking at a large data-set, it’s really hard to identify the lower values or higher values, but if you have a heat map then it’s easy to identify them.

You can use different color schemes to illustrate a heat map. And, if you can put some extra efforts then a dynamic heat map is the best.

I hope this tips will help you to get better at Excel and now tell one thing.

Do you think a heat map can be helpful for you in your work?

Share your views with me in the comment section, I'd love to hear from you. And, please don't forget to share this tip with your friends.

  • Thank you PG, for sharing these tips on creating an awesome Excel Heat Map Template. It was very interesting, insightful, and something I’ll be using in future.

    Sincerely,
    Leonardo “List Legend” LaVito

    • Puneet Gogia

      Thanks Leo LaVito for your words.

  • Dilshad Ahmad

    Nice

    • Puneet Gogia

      Thanks for your words. 🙂

  • Lester Daniels

    I really like your web site’s content but these pop-up messages are so incredibly annoying that I will not be back. I think they’re called “exit intent” popups. They are horrible. By the time I scrolled down 1/2 I was bombarded with no less than 3 pop-ups: on on the bottom-right, one even larger one on the bottom-left covering a lot of content, and then the entire screen became a pop-up. If these pop-up things arent smart enough to know that I have already signed up for a newsletter then what good are they? Isnt that what we’re wanting to learn – how to be smarter with technology? I hope you’ll consider removing it but I understand that you shouldnt just for me.

    • Puneet Gogia

      Lesson learned

  • E

    Thank you so much for sharing! I have been following you for awhile and your blogs has helped my work greatly! Thank you!!!