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
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.
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.
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.
And, here are the steps:
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.
I always prefer to use a heat map in a pivot table. Please follow these simple steps.
Further, if you want to hide numbers (which I don't recommend) please follow these simple steps.
Bonus Tip ==> Use Conditional Formatting in a Pivot Table
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.
And, following things we need to incorporate into this dynamic our heat map template.
Please follow these simple steps.
Now, you have to apply another conditional formatting rule to hide numbers when you un-tick the check box.
After applying all the above customization you will get a dynamic heat map.
Some links to help you out while choosing your favorite color scheme
Download this sample from here to learn more.
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.