Hello Excel Funs!

In this lecture we are going to learn how to create a Heat Map in Excel. Basically, a heat map is a presentation of data with colors according to the values.

We can visualize trends or relationships using color scales. For example I can easily spot which are the months for a company when the sales were low (highlighted in red) as compared with other months. Or I can spot the low and high temperatures by city , by month. Keep in mind that in order to have a nice heat map you have to use intuitive color scales like red to green for earnings or blue to red for temperatures.

First we will create a simple heat map using conditional formatting to highlight cells based on the value. This way, in case you change the values in the cells, the color/format of the cell would automatically update the heat map based on the pre-specified rules in conditional formatting

In this table you can see earnings of a company by month, from January to December, and from year 2000 to year 2017.

The steps are very simple. The first step is to highlight cells from B2 to S13 and from Home menu find conditional formatting options and go to build in color scale. Here I can choose various color combinations that can be used to highlight the data. The most common color scale is the first one where cells with high values are highlighted in green and low in red. Note that as you hover the mouse over these color scales, you can see the live preview in the data set. I will choose the third one that serves my purpose, more green more earnings more red less earnings.

How to Create An Excel Heat Map Table With Conditional Formatting

Now, if you want to show only the colors and not the values in the cells, a great tip is to use custom formatting rule from Format Cells properties, to the bottom of this category list, select 0, delete it and just type three consecutive semicolons and ok.

How to Create An Excel Heat Map Table With Conditional Formatting 2

The numbers are invisible , the conditional formatting is still based on the values but the user doesn’t see them. In that way you just visualize the trends. Another option is to change the color scale by managing the rules from this window.

How to Create An Excel Heat Map Table With Conditional Formatting 3

Edit the rule and change the color scale , for example I’ll change the color of the lowest value. Okay, okay again and see the different shades of green. So Heat map is a wonderful tool to tell a story.    

 

Download the Excel Project & Sample Files:

HeatMapData.xlsx

HeatMapData-SAMPLE.xlsx

 

Watch the YouTube Video Tutorial: