How to Create a HEAT MAP in Excel (Simple Steps) + Template

HomeExcel ChartsHow to Create a HEAT MAP in Excel (Simple Steps) + Template

Visuals are always easily understandable comparing to values. Even in Excel, when you use a chart (advanced or basic) easy for the user to understand it.

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

To deal with this kind of situation in Excel 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.

What is a HEAT MAP in Excel?

In Excel, a Heat Map is a presentation of data using a color shade on each cell in the range in a comparative way for a user to understand it easily. It looks like a map with different colors ranging from dark to light to represent the weightage of the value in the cell.

Below is an example of a simple heat map where we have zone-wise and month-wise data and for every cell where we have sales value there is color shade applied on the cell.

a simple heat map in excel

And this color shade helps us to quickly compare values in the cells with each other. Cell with the highest value has green color as the cell color and the cell with the lowest value has red, and all the cell in the middle has a yellow color.

All the values between the highest value and the lowest value have color shade according to their rank. But you can create a heat map like this manually because applying a color to a cell according to its values can be possible every time.

Now the point is, you how you can what are the possible ways to create a heat map in Excel. And, if you ask me there are more than three. So in this post, I’ll be sharing all the possible ways to create a heat map in Excel.

When you take a printout of a heat map on paper it looks really nasty especially when you are using a black and white printer. All the shades only have black and white color which is not easy to understand for anyone.

Create a Heat Map in Excel using Conditional Formatting

If you don’t want to put extra effort and save your time then you can create a simple heat map by using conditional formatting. I’m using the same data as a sample (DOWNLOAD LINK) here which I have shown you at the starting of this post.

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

To create a heat map in Excel you need to follow the below steps:

  1. First of all, select the data on which you want to apply a heat map (here you need to select all the cells where you have sales values)
  2. After that, go to Home Tab ➜ Styles ➜ Conditional Formatting.
  3. In conditional formatting options, selects color scales. (You have six different types of color scales to choose from).
    six-conditional-formatting-color-scales-to-create-a-excel-heat-map-template
  4. Once you select an option, all the cells will get a color shade according to the value which they have and you’ll get a heat map like below.
    a-simple-heat-map-in-excel
  5. And, if you want to hide value and only want to show color shared you can use custom formatting for this.
  6. For this, first of all, select heat map data and open formatting options (Ctrl + 1).
  7. Now, in the number tab, go to custom and enter ;;; and in the end click OK.
    custom-formatting-to-create-a-excel-heat-map-template-using-color-scales
  8. Once you click OK, all the number will be hide from the cells. Well, hey are in the cells but just hidden.
    a-simple-excel-heat-map-template-using-color-scales

Steps to add a Heat Map in a Pivot Table

You can also use heat in a pivot table by applying conditional formatting. These are the steps to follow:

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

  1. Select any of the cells in the pivot table.
  2. Go To Analyze Tab ➜ Active Field ➜ Field Settings.
    chnage-pivot-table-number-format-to-create-a-excel-heat-map-template
  3. Click on number format.
  4. In number tab, go to custom and enter ;;; in type.
  5. Click OK.

You can download this sample file from here to learn how we can use a slicer with a heat map pivot.

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 used 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 you have a dynamic heat map that 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 the marked checkbox you need to create a separate conditional formatting rule. Here’s how to do this: 

  1. Open conditional formatting new rule dialog box.
  2. Select “Use a formula to select which cell to format” and enter the below formula.custom-formatting-to-create-a-dynamic-excel-heat-map-template
    • =IF($K$1=TRUE,TRUE,FALSE)
  3. 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.

  1. Hex Colors
  2. Color Brewer

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

More Charting Tips and Tutorials

14 thoughts

Leave a Comment

Your email address will not be published.

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

    Reply
  2. 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.

    Reply
  3. 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

    Reply