Creating excel heat map is an art.
You need to know which colour combinations work better for your scenarios.
According to a research conducted by Prof. Richard Wiseman, “about 25% of people found documents with colours are easier to analyse”.
In fact, colours can play a pivotal role in your analysis when you use it wisely.
For example, if you’d like to highlight high-cost scenario then you might have to use Red, similarly Green for the low cost.
With the help of Excel heat map, you can analyse your data quickly and you can make meaningful conclusions.
So, creating excel heat map is difficult..?
Good news is “no”.
With the help of simple Conditional Formatting, you can create awesome heat maps.
So, here is a step by step tutorial on how to conduct heat map analysis with the help of excel.
Creating Excel Heat Map: Back to basics
What is heat map?
According to Wikipedia “A heat map is a graphical representation of data where the individual values contained in a matrix are represented as colours”
In other words, data analysis with the help of colours & conditions.
What you need to know to get started:
i) Basic understanding of Row/Column width adjustments, simple formatting, arranging data with the help of tables.
ii) Conditional formatting and it’s usage
iii) In case if your data is unstructured then you might have an understanding of various excel text formulas.
Step by step guide:
For this article, I’m using the United States retail operating expenditure data.
Basically, with this exercise, we are trying to identify patterns & trends to analyse and interpret results.
As you can see by looking at data it’s highly difficult to analyse & interpret results.
So, can we do it with the help of heat map..? let’s have a look.
Step1:
We are trying to understand the yearly trend, hence, we need to create conditional formatting by selecting only individual rows.
For example, to check “GAFO” Operating Expenses trend you need to select data from C5 to K5.
Step2:
Let’s starts with the first row, so select C3:K3.
Conditional Formatting → Color Scales → Red-Yellow-Green colour scale
Red – Highest value
Yellow – Median value
Green – Lowest values based on percentile
Step3:
As you can see by default excel uses very dark colours, so when you apply the same formatting for all the Rows then your heat map will be little difficult to interpret.
So, let’s change colours according to your preference (light colours will perform better).
Select C3:K3 → Go to Conditional formatting → Manage Rules → Edit Rule.
i) For lowest value: Click on Colour drop-down → Select any colour that you like, in my case I’m using light Green
ii) For midpoint: I’d prefer light Yellow
iii) For Maximum: I’ll select light Red
Step4:
Now that we have set our colour coding, it’s time to apply same conditional formatting for all the rows.
i) Select C3:K3 → double click on Format Painter option
ii) Then go to each row and use mouse left click to apply formatting
iii) Apply formatting to the last row
Note: please don’t select the entire range and apply formatting, if you do so your logics will go for a toss*.*
Step5:
Let’s do some simple formatting.
i) Select entire range and remove all the borders
ii) Now click on “Thick Box Border” set only outside border
iii) Select entire column(s) C:K → set Column Width to 7
iv) Select Row 3 to 49 → set Row height to 25
Step6:
Let’s adjust font & font size to look better
i) Select B3:K49 → Font name → Bell MT (or any lighter font that you like) → Font size 12
ii) Select C3:K49 → Allignment → Center and Middle align
Once you complete all the above steps, your heat map should look like this.
Analysis & interpretation:
Well, don’t you think creating excel heat map is relatively easy then you would have thought..?
Of course, it’s easy but it’s not complete yet.
The purpose of creating this heat map is to understand and interpret results, so what do we make out of it.
Outcome i)
Based on this, operating expenses related to “Health and personal care stores” are steadily increasing (above highlighted in red).
Interpretation: It’s easily understandable. Generally, these type of business will need to phase up with the current market trends. Expenses related to Licencing, Equipment & Training are becoming expensive year on year.
Outcome ii)
Operating expenses related to “Accommodation and food services” are increasing.
Interpretation: There is a rigorous computation around Food and Accommodation industries. Today most of the hotels are running based on discounts resulting in huge operating costs.
Outcome iii)
There is a strange pattern across all business lines during 2009. As you can see most of the cells are in Green.
Interpretation: we have seen worst recession during 2007-2009 so because of that every business had to cut down their spending, hence there is green everywhere.
If you analyse further, Departmental & Grocery stores cells are not entirely green. Generally, these stores sell basic needs, hence, recession can’t stop people to buy products related to food, shelter & clothing.
Other Shades in creating excel heat map:
So for so good, we have created awesome heat map using excel and we have also added some analysis & interpretations in place.
So, the next question is: is it possible to create excel heat map without any numbers..?
Indeed “yes”
i) Heat map with only colours (numbers are hidden)
Step1:
Select C3:K49 → Hit Ctl+1 excel shortcut key to launch format cells dialogue box.
Step2:
Under Numbers tab → Custom → Type → input three ;;; (semicolons) → Then click ok
Boom, that’s it.
Your heat map will show only cell colours and hides numbers. This type of heat maps is really useful to understand overall trends.
For example, in 2009 entire column H is mostly showing “Green” colour. And we interpreted it’s because of a global recession.
ii) Heat map for highest trend
Creating excel heat map that shows the only highest trend is still possible. In general, these type of heat maps is highly useful in web analytics to understand user clicks.
Companies like crazy egg offer these type of services to webmasters.
So, with that in mind can we create a heat map that’ll show only highest trend..?
Yes,” we can”.
If you look at below heat map you can see only highest trend which is marked in Red and rest all are in Yellow colour
It’s a simple technique.
You just need to change colour coding for median & lowest values to “Yellow” in your Conditional formatting.
ii) Heat map for lowest trend
There are some scenarios where you’ll have to identify lowest values using heat map. These type of heat maps tend to perform better for a month on month cost analysis.
Again, the technique is simple.
You just need to change highest & median colour coding to “Yellow” and lowest one should be “Green”.
Now, your heat map will look like below.
You can download my heat map working file here.
Conclusion:
There was a time when we used to say data analysis is a boring job. Indeed, it was.
But, with the help of various data visualisation techniques, it’s now easy and moreover, demand for such skills are very high in the market.
There are numerous tools available in the market with expensive price tag. But when it comes to simple data analysis excel will serve our purpose.
With the help of above “creating excel heat map” tutorial, you can analyse any sort of data and present in such a way that everyone will be impressed – why not your boss.
It’s now your turn – did you also tried anything creatively like excel heat map? if “yes” what was that? please comment below.