Use a heat map in Excel for rapid visualizations! We’ll show you various solutions to create heat maps and dynamic colored shapes. This tutorial will provide a detailed introduction of the heat map optimization using Excel. We will explain how to use simple and advanced solutions in order to improve the visual performance of your presentation.
You will learn what the heat map specifics are, how to set it for the most popular examples and how to benefit from its features.
What is a heat map? How to create it?
A heat map helps us to make a quick visualization comparison of the relations of the values in the data set. For example, in a bigger range (that contains the sales data for the past 10 years in the monthly breakdown) we immediately can point out the time periods perform under or over the desired plan.
How can we do this using Excel? This is what today’s article is all about! Use maps to build great looking dashboard templates using data visualization.
In this tutorial, you’ll learn how to:
- Create heat maps using conditional formatting
- Visualize resources using dynamic maps
Create a Heat Map Using Conditional Formatting
If you take a look at the picture below, you will get information about how the sales of different products related to each other throughout the years. How can we create a conditional formatting based heat map in a few single steps? We will show you this in the first chapter.
We don’t use Excel to do manual work and wouldn’t color the cells one by one. Conditional formatting is an excellent tool to automatically highlight the relevant values.
Apply rules because if we manually manipulate the cells we will have to raise problems! One of them is the time factor, for the process would take too long to complete.
The other is the disadvantage arising from the static depiction. In this case the changing of the values of the cells would not come with the changes in the colors.
Very important criteria are that when the values are changed in a given rang, then the colors must change also. You can find the starting data table of the heat map on the illustration below.
How to create a gradient map using color scales?
Let’s see the step-by-step tutorial:
1. Select the data set. In this example, it would be C3:H8
2. Go to Home –> Conditional Formatting –> Color Scales. It shows various color combinations that can be used to highlight the data.
If we apply the green-yellow-red color scale, then the end result will look like this:
It is worth to examine closely how colors the cells by default. It ascribes green to the highest and red to the lowest value cells. And it used a color transition in between. If we take a look at the heat map, the color orange means the average. This would be fine, but there are different shades of orange, so which one is the true average?
It seems that this solution simplifies the task because the scale is relative. We have to point out again that this solution is “only” good for displaying the relationship between the elements of a larger data set.
The high and low values easily filtered out with the use of a color scale. To make the data visualization precise, we have to use a different method.
Use classification rules for group data
We wouldn’t like to see a gradient color scale; we would like to identify what values belong to what groups.
Use the following set of rules to highlight:
- Green the cells containing numbers larger than 750
- Yellow the cells between the values of 550 and 750
- Red the cells lower than 550
Let’s see how we can accomplish this: As first, Go to Home Tab. After that, chose Conditional Formatting. Finally, add a ‘New Rule’ using ‘Format only cells that contain’ command. In the dialog box, add the following rules:
You can see the end result here:
Explanation: What is the difference between the two heat maps?
The first method compares the numbers to each other and uses a gradient color scale based on that. In the second graph, we have given those three clear rules which grouped the values.
You can freely choose between them; we recommend you make your choice based on the given task.
Tip: How can we create a heat map where we only can see the colors? In this case, we have no need for numerical values. Highlight all the cells that you would like to apply to this method too. Use the Ctrl+1 keyboard shortcut.
After this, a Format Cell dialogue box will open.
Navigate to the Number Tab and choose the Custom option. In the appearing field write “;;;” (3 semi-colons).