What is a Heatmap?

A heatmap is a graphical representation of data where the individual values contained in a matrix are represented as colours.

In the screenshot below, each cell contains a value representing the number of people in a (fictitious) department store during May 2016. It has been broken down into days and hours so that Management can identify the busiest/least busiest days and times.


As you can see, it’s near-impossible to make sense of these values. However, if they were displayed as a heatmap, it could be done fairly quickly. In the screenshot below, the cells containing the darker colours contain larger numbers. Management aren’t interested in the numbers themselves, they just want an overall birds-eye view of the date/hour breakdown.


Creating the Heatmap

Select the cells containing the numbers that you want to “convert” into colours and click the Conditional Formatting button on the Home tab of the Ribbon. Select Color Scales and then click on the desired scale from the set of choices.


Each cell now has a coloured background. The depth of colour is determined by the value of the number in the cell. However, the numbers are still visible. To hide the numbers, click the Number Formatting dropdown arrow and select More Number Formats (bottom of the list).


Select Custom from the list on the left hand side and in the Type field (just above the list in the body of the dialog box), type 3 semi-colons (;;;) and click OK


**Download the demo file here**