Excel Heat Map
Heat map is a good way of visualizing distribution and it is very useful for quickly seeing where data is piling up, thus which data to focus. In this post I will show you how to setup an Excel Heat map.
It is actually very easy to setup an Excel Heat map. All you need to setup a grid and populate cells with results. Than all you need to do is to color your grid with conditional formatting based on cell values.
For this tutorial, I will replicate the heat map from Google Analytics that shows traffic density for each hour of a week.
Here is our example data (this table is over 6000 rows long):
Here is our grid:
All cells has values that are calculated with Sumifs Formulas:
I have added one column between each column and a one row between all rows. Then set width of additional columns to 6 pixel and height of additional rows to 6 pixel. Just for good looks.
Now it is time to apply conditional formatting. Select your cells with formulas and apply Red-White Color Scale.
Hide gridlines by unchecking related option from VIEW ribbon.
This is how it looks at this point:
Now all that left is to hide the numbers. I tried to write a small macro that would match cell font color to cell fill color but then learned the hard way that colors applied by conditional formatting do not count as cell color. But found a very easy solution after some googling.
Select cells with formulas and apply custom number format. Type ; ; ; as custom format. This will hide all numbers on your grid.
Congratulations, your Excel Heat Map is finished.
You can download and check this Excel Heat Map from here .