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

a simple heat map in excel

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 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 it values can 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 with all the possible ways to create a heat map in Excel.

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. All the shades only have black and white color which not easy to understand for anyone.

1. Create a Heat Map in Excel using Conditional Formatting

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:

  • 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)
  • After that, go to Home Tab ➜ Styles ➜ Conditional Formatting.
  • 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
  • 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
  • And, if you want to hide value and only want to show color shared you can use custom formatting for this.
  • For this, first of all, select heat map data and open formatting options (Ctrl + 1).
  • 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

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

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.

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.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

11 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!!!

  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.

  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