Use a heat map in Excel to create quick data visualizations! Today’s guide will be about how to build maps using conditional formatting.
What is a heat map?
Heat Map is the perfect solution for you to highlight the crucial data points in a table or a range.
Let us see a simple example:
You have a data table that contains various data points (values) about the performance of five products by year. Use the heat map method to show the highs and lows (not least peaks) with a few clicks.
In the next section, we’ll show you how to use a color map. Don’t forget: a heat map can be a great choice if working on a sales-related excel dashboard.
Build Conditionally Formatted Heat Maps
Please, take a look at this example below!
We want to get information about the sales performance by products and year. But, unfortunately, time is money, so we need to take a quick overview throughout the periods.
If you want to find the best-performing product in 2020, it’s easy to find the solution. But what if you have hundreds of different products? It looks like manual work can be a time-consuming project.
Why is manual work in a spreadsheet a waste of your time? You will get the same result if you follow the suitable old method – find the highest value in a given range and apply color to highlight it. Okay, now refresh or update your data! The cell color remains, and the data can be changed. So, avoid manual work!
We recommend you use conditional formatting to automatically highlight the key information ASAP based on a simple rule.
How to create a gradient heat map using color scales
Our goal is to track changes in the given data table. If the cell value change, the color scale must be followed by the changes. Check the prepared data table for the heat map in the picture below.
In the example, select the range D3:D7.
Click on the ribbon and locate the Home Tab.
Select ‘Conditional Formatting’ and apply ‘Color Scales’. You can pick your preferred combination using built-in presets. You can create your own scale for the heat map visualization also.
For simplicity, choose the most frequently used scale. For example, green is the highest value, yellow is the mid-range, and red is the lower range of values.
The table will look like this:
It’s easy to highlight and track the highest and lowest values for 2020. The highest value will be highlighted on the heat map using green; it is 957. The lowest value is 533.
Take a closer look at the heat map! Which color define the real mid-range? The yellow or the light orange? Or the dark orange?
The main problem is the relative scale: if we are using gradient color scales on the heat map, we cannot classify the data for high, mid, and low ranges.
Let’s talk about how to highlight the data using a different way. To make the heat map visualization more accurate, use classification rules for your data.
Use classification rules for group data
In the example, we’ll forget the color scale build intervals for a better result. As first, identify the intervals; after that, create three rules.
We’ll split the values based on the following rule:
- If a cell contains a value which is greater than or equal to 800, we’ll apply green shading.
- If a cell contains a value between 600 and 799, the cell is yellow.
- In the case of values that are under 500, we’ll use red.
Check the result!
Compare the heat maps, and let’s see the differences. Using the gradient scale, you’ll see the relationships between numbers without a clear structure. You will get an easy-to-read map if you group the values and split the range using conditional formatting rules.
Okay, which method should I prefer? It depends on your project; we recommend you use the second method.
Tip: Remove numbers from the Map
In some special cases, we don’t want to show the values on the map. How to do that? Believe it or not, the solution is straightforward.
First, press the ‘Ctrl+1’ keyboard shortcut!
The ‘Format Cell’ window will appear. Next, click on the Number Tab (you can find it on the left side of the box) and select the ‘Custom’ option.