How to Create a Heat Map in Excel (Quick and Easy)

Heat Maps are a really handy visualization technique when looking at large tables of data to quickly and easily see which values are highest and lowest. Making a heat map in Excel can be done in a couple of different ways – manually or dynamically using conditional formatting.

In this simple guide we are going to run through how to create a heat map in Excel that automatically updates the colours as values change.

Let’s get started.

Our Table Data

The dataset we are working with for this example is a pretty simple one – we just have total sales volume for 5 different people across a one year period broken out by month. We want to be able to quickly scan this and see which people had the highest sales volume and when did it tend to occur. This will also allow us to see if there are any trends – for example were the later months best for everyone?

Our data looks like the below:

Looking at the above, there is clearly a pretty big range in some of the values, but it can be hard to really intuitively see while scanning across the table which really are the highest. Let’s add the heat map and see how it looks.

Making a Heat Map with Conditional Formatting

First, head up to conditional formatting in the home ribbon, and under the drop down head to the colour scales box. By default lets just use the top left option which has green at the top and red at the bottom:

Here is the result:

Definitely a lot easier to read, and there is a very clear trend where September-November looks to be the strongest part of the year for everyone.

Tweaking our Heat Map

Since this example isn’t necessarily looking at good or bad we probably don’t need a green/yellow/red scale. So let’s just pick one colour – in this case green, and use a light to dark shading to see where the top sales came from. Let’s head back to the colour scales menu, and pick the left bottom option this time for green. The resulting output is as follows:

This is much cleaner looking, and paints a better picture of the stronger periods, while not necessarily highlighting anything wrong with the lower sales months with the harsh red colour we saw in the first image.

Next up, we have some more custom options to work with, which we can see if we select more rules:

This brings up the following menu:

Most of the options here aren’t really going to use a scaling range like the heat map approach, so let’s only look at the top option which is selected by default. Here we have the option to change the format style from 3 colours to 2 (and some options involving icons – but again not really a heat map). We can also choose the minimum and maximum values to work with when choosing the formatting scale.

Let’s stick to the green theme, but run with an option where we don’t really care about any variation in values under 600 and only want to see the cells get darker at higher values so we can see the standout performers more distinctly.

This would look like the below. We set minimum to number and enter 600. Then we leave maximum at highest value since there is no upper limit we are about. Then just select two different shades of green in the colour box:

The resulting output is as follows:

This can be really handy, and also re-enforces our view earlier that September-November were the standout months, although October not quite as much as originally thought.

Dynamic Updates

As mentioned earlier, because we arent just manually shading cells and using conditional formatting with specific rules, this means that when the numbers change, so do all of the cells shading in line with what then becomes the higher and lower values.

Let’s look at an example where we have kept the formatting rules identical to the first green example above (not the one with the 600 minimum rule). If we completely change the numbers the shading also changes with it:

This saves a ton of time when visualising results, and means that this template can easily be expanded on or used for following years without a whole lot of manual work.

This sums up our guide on how to add a checkbox in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.