Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Excel Heat Map Template

Visuals are always easily understandable comparing to values. Even in Excel, when you present your data in a graphical mode it’s easy for the user to understand it.

But, not every time we can go this way. Most of the time we need to present our data in tables or in other report formats.

To deal with this kind of situation I have found that a heat map is the best solution. It helps us to present data with visual effect and without changing the actual data structure.

Basically, a heat map is a presentation of data with colors according to the value. For example, for bottom values color should be red and for higher it should be green or vice-versa.

A Simple Excel Heat Map Template Using Color Scales

And, when it comes to Excel, we have a different way to create a heat map or a heat map 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.

Heat Map With Conditional Formatting

If you don’t want to put extra efforts and save your time then you can create a simple heat map by using conditional formatting.

Here we are using below data table.

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

And, here are the steps:

  • 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
  • Now, the next step is to hide numbers from the table.
  • Select heat map data and open formatting options (use ctrl + 1).
  • In the number tab, go to custom & 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. Or, if you don’t want to hide numbers then you can skip custom formatting.

Heat Map in a Pivot Tables

I always prefer to use a heat map in a pivot table. Please follow these simple steps.

  • Select any of the cells in a 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 & enter ;;; in type.
  • Click OK.

Bonus Tip ==> Use Conditional Formatting in a Pivot Table

Dynamic Heat Map

You can create a dynamic heat map if you want to hide/show it according to your need. It's little bit tricky but worth to learn and looks really awesome.

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 into this dynamic our heat map template.

  • Option to switch between heat map and numbers.
  • Auto update data table on adding new data row.

Please follow these simple steps.

  • First of all, change your data range into a data table, so that when you add new data row into it, formatting will automatically apply on it.
  • Use Ctrl + T to convert it into a data table.
  • Now, next thing we need to insert a check box to toggle between heat map and  numbers.
  • Link that checkbox to a cell.
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
  • Now, you’ll get a new formatting rule pop-up.
  • Change format style to 3-Color Scale.
  • Now change the type to “Formula” for the minimum, midpoint and maximum values and insert following formulas.
    • =IF($K$1=TRUE,MIN($B$2:$H$25),FALSE) – This formula will give you the minimum value from your data range if the checkbox is ticked.
    • =IF($K$1=TRUE,AVERAGE($B$2:$H$25),FALSE) – This formula will give you the average value from your data for mid-point if the checkbox is ticked.
    • =IF($K$1=TRUE,MAX($B$2:$H$25),FALSE) – This formula will give you maximum value from your data if the checkbox is ticked.
Use Conditional Formatting Pop Up Box To Create Excel Heat Map Template

Now, you have to apply another conditional formatting rule to hide numbers when you un-tick the check box.

  • Open conditional formatting new rule dialog box.
  • Select “Use formula to select which cell to format” and enter below formula.
    • =IF($K$1=TRUE,TRUE,FALSE)This formula will return true if the check box is tick marked else 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

Some links to help you out while choosing your favorite color scheme

  1. Hex Colors
  2. Color Brewer

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

Sample File

Download this sample from here to learn more.

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.

Now tell me one thing. Do you think a heat map can be helpful for you in your work? And, do you know any other method to create a heat map? Please share with me in the comment section, I’d love to hear from you.

😃

And, please don’t forget to share this list with your friends. I’m sure they will appreciate it.

  • 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