Use heat map in Excel for rapid visualizations! We’ll show you various solutions to create geographical state 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 using Excel?
Heat map is a tool in Excel that 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 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 heat maps to build great looking excel dashboard.
In this tutorial you’ll learn how to:
- Create a heat maps in Excel using conditional formatting
- Visualize HR resources using a dynamic heat map in Excel
- Build a geographical map with VBA
Create a Heat Map in Excel Using Conditional Formatting Rules
If you take a look at the picture below you will get information how the sales of different products related to each other throughout the years. How can we create a heat map in a few single step? 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 values relevant to us.
Apply rules because if we manually manipulate the cells we will have to arising 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 of the colors.
Very important criteria is that when the values are changed in a given rang than 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 heat maps (color scale)?
Let’s see step by step the making of the heat map.
Select the data set. In this example, it would be C3:H8
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 than the end result will look like this:
It is worth to examine closely how Excel 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.
How to use classification rules to create Heat map
We wouldn’t like to see a gradient color scale, we would like to exactly identify what values belong to what groups.
We will 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:
Go to Home –> Conditional Formatting –> New Rule -> Format only cells that contain
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 gradient color scale based on that.
At 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 the numerical values. Highlight all the cells that you would like to apply this method to. Use the Ctrl+1 combination.
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).
Visualize resource usage – Dynamic Heat Map in Excel
In our second example we’ll build a dynamic project heat map. We’ll show you how to display the resource usage in daily, weekly and monthly breakdown. How can the heat map be relevant to this task? We will highlight critical activities by colors. With the help of our forecast system we can avoid project delays caused by lack of capacity.
We take our example from real life: There are more projects running simultaneously, and our HR capacity is limited. We have to continuously analyze the use of resources and the peaks so we can intervene in time.
With the use of some form controls we can make our heat map logical and transparent. Even large amounts of data wouldn’t cause any problems.
If our goal is a one-page-report the best tools are the “slider” and the “list box.” With the list box we can change between views. And by clicking on the arrow on the slider we can view the next time period also.
Let’s bring our attention to the heat map. About the use of Excel form controls we will have another article.
How to fill up the cells with values?
This is a very large and long formula, but it contains everything we need. From the range of the given time period it will seek out how many activities belong to the given project at the moment.
On the figure below we show the monthly view and we can assess that for Project 1 there only one resource needed. The other peak is at Project 5 December, where the resource needed is six times larger.
Heat map is a really useful tool because when we dynamically change between the views we’ll get real-time information.
How is the best to create the alerts? The darker the cell the more attention we need to pay to that activity.
And this is all. We should give a rule that Midpoint should be 3, and going towards the maximum we use preferably darker shades of colors.
Finally let’s see a little overview to explain why heat map is the best tool in Excel for tasks like these.
Below you can see the finished heat map! We can see by first glance what time the most resources (6) is needed. And by examining the summed up data of the last row we can easily determine that the November and December period is most critical where 23 and 26 activities are running simultaneously.
Create a Geographical US State Heat Map in Excel
Data visualization is a rather popular subject and Excel provides endless possibilities for collecting, organizing and displaying continuously growing data. If you work with the newer versions of Excel than you probably know PowerMaps and 3D Maps applications. To run these you must have at least Excel version 2013. They can create excellent Geographical Heat Maps! They support country, county and city level analysis.
What about Excel 2010 and earlier versions? Fortunately there are solutions for the users of these versions also. They don’t have to demit the user enjoyments offered by the Geographical heat maps!
In today’s example, we show a modern and easy to use method! With the use of Custom Excel shapes and formulas we can create our own heat maps in Excel in a short time. Some knowledge of programming is required, but you don’t have to worry about this. We provide a complete source code in the article and the map is free to download. As a precursory take a look at the map below for the end result. It looks good, isn’t it?
In this article we will go over every single step. Our goal is for you to be able to create you own country’s geographical heat map later.
The development of this tool consists of 4 main parts:
- Preparing Shapes in Excel
- Create the Data Table and Textboxes
- Create the Legend and Color Scale
- Write a short VBA code
Preparing Shapes in Excel
In today’s example we’ll create a special USA state heat map. For the first step we’ll have to import into Excel a map that contains all of the states of the US. This is not too complicated to get, but we’ll spare you the search. You can download from HERE the detailed maps of all the countries of the world in ready to use format.
Their common characteristic is that they are all free-form editable maps so you don’t have to manually draw the states. The characteristics of the shapes that they are grouped, so we have to separate them with the use of the “Ungroup” function.
So we’ll be able to color every single state individually. After the ungrouping we’ll have a map just like this one below.
There are other solutions if we’d like to create some specific maps. With the help of Inkscape (free software) we can create unique shapes. Finally you can use Excel to make special heat maps. Use the following: Insert > Shapes > Freeform > Shape.
Create the Data Table and Textboxes
Create a similar database as you can see on the figure below:
The column “State” contains the names of the states.
In the second column you can find the codes, these will be very important later. Each and every shape gets a unique name and by this we will be able to identify them automatically when making the heat map.
In the third column there are the values by which the VBA decides what color each state will be.
Our next task is to assign to each object the proper identification. First highlight the shape and use the name box. We should name the highlighted area by the state codes in the data table.
We’ll display the names of the states for better understanding for everyone. All we have to do is to insert a textbox and position it over the state. For consistency we should aspire to use the data table’s proper codes.
The next step is the most time consuming, but it is worth it, because the end result will be remarkable. Name all 50 states using the Namebox and also make the labels using the textbox.
Build the Legend and define Color Scales
Let’s define the color scales and values with formulas that our macro will use to update the colors and the texts of our heat map.
The data range definition in this case is rather simple. The minimum and maximum values can be simply read out of the data table “Values” column. We will break up this range into groups with the use of a statistical formula.
In our example the interval between the minimum and maximum values we’ll divide into 16 parts for the most detailed visualization. In other words, we’ll be able to create such heat map that will show the difference between the values in 16 separate colors.
Try for the most realistic display! Is we would work with only a 5 color scale we would excessively simplify the end result and it wouldn’t be the best.
With the help of the PERCENTILE formula we can set between minimum and maximum values an arbitrary interval:
The first parameter of the formula is the “scale_values” range, we’ll define the lowest and highest values that we’ll divide. In our example the second parameter of the formula means that we create the categories in every 6%, altogether 16 of them. Because 100/16=6,25, with the ROUND formula we round it up to an integer, this is how we get the 0,06 value which equals 6%.
In the first column we displayed the received intervals, these change dynamically depending on the actual values. The second column shows the given interval lowest value. And in the third column we displayed the color belonging to the given range.
How to automate maps with excel vba
We are done with our data tables, nothing left to do only to implement to color and refresh all shapes with regard to all relevant values.
With three short macros we make the heat map dynamic. Let’s see the first one! Here we determine every shape’s characteristics, every single state’s characteristics of the heat map. After this we determine how thick should be the line dividing the shapes. Finally we fill the shapes with color.
To insert macros, open the VBA window (Alt+F11) and insert a module and copy/paste the following codes into the module.
The “ColorArea” macro works really simply. We store the start points of the intervals in the range named “scales.” With the help of Next loop the macro reads all the intervals and the colors assigned to them, this will be drawn out on the heat map.
The “ColorHeatMap” will do the dirty work. There are two information can be found in the “data” range: the identifier (area) of the given shape and the actual value (aval). With these the macro defines the color code of the given state and colors the map.
In order to start the macro easily, we’ll insert a command button. (In the Developer tab, go to Insert > Button). If the developer tab is not visible we have to authorize that manually. Display the developer tab on the ribbon if it is not shown!
After we inserted the button give it a name so everyone can understand its function. Finally assign the code responsible for the coloring of the heat map.
We are all done. From now on you can freely change the values in the data collector table. All we have to do is to click on the command button placed on the Heat map sheet. The map will automatically refresh based on the given settings.
Let’s see a little supplementation: We have created 6 color schemes so everyone can find the most suitable for them. You can change between them by the radio buttons.
Best Practices and Rules to designing Heat Maps in Excel
Let’s take a look at four important rules that are best to keep:
1.Always choose simple borderlines. There’s no need to over-complicate it or the focus will shift from the essence. You should choose the color scale very carefully. The goal is not to create a rainbow colored map. Or to choose a scale that is too contrasting. It is best to use one or maximum two colors and their shades.
2. If you choose to use patterns (we don’t recommend them at all) you have to make it so it is the same on all the elements of the heat map.
3. If there are several different patterns on one map that could be troublesome. So just be frugal with the colors.
4. Choose the data range carefully, build it up the intervals according to distribution.
Questions and Answers regarding heat maps
Q: Is there any disadvantages to heat maps?
A: To our best knowledge there is only one notable. One of the backsides of conditional formatting is that is considerable can increase the size of the workbook. We recommend that if you make a heat map in Excel prefer the method based on shapes.
Q: How long does it take to create a more complex geographical heat map?
A: As we mentioned before the maps of every country can be downloaded from HERE. Because you get the codes ready, it takes about 30 minutes to create the data visualization.
Q: Is there any limit regarding the usable objects? Can I only use the Excel built-in objects to make a heat map?
A: Probably not, virtually any object can be shaped to the needed from. For example take a look at the resource section, there we introduce you a special heat map visualization.
Q: I see that the VBA project contains open source codes. I would like to use them for private purposes. Can I do that?
A: Above all, the article was created so every reader can get interesting experiences regarding the heat maps. Source codes can be used freely for private and commercial purposes also.
As you can see with the help of heat maps we can give solutions to manifold business and economy problems using data visualization. Some people think that this procedure only means the coloring of cells. We hope that applying the today represented examples proved that virtually in any situation this tool is an effective solution.
Additional Resources and Downloads
Create dynamic shapes using conditional formatting!