2D Tabular Heatmaps with Microsoft Excel
Inspired by a NY Times chart, Juice Analytics recently had a post and a discussion on bubble chart heat maps: Bubble, bubble toil and trouble. Chris Gemignani wrote:
“The first tool we tried, simply on principle, was Excel 2003. As expected, making a NY Times quality bubble chart in Excel 2003 is a hard problem.”
Juice Analytics is one of my favorite blogs on visualization and I learned a lot from the blog and website. But in this case I do not agree at all. And it seems as if I am not alone.
What had to come, came. Some of us – including myself – could not let this rest.
- I used Fabrice Rimlinger’s famous Sparklines for XL (free download here) and created a replica of the NY Times chart. Fabrice was kind enough to publish this on his blog (Yes, we can) and his own version with an improved visualization using bar charts (Stick to the classics?).
- Two days later my friend and Excel MVP Chandoo showed Visualizing Search Terms on Travel Sites, a bubble-chart solution with plain old Excel (no VBA).
- Last, but not least: Andreas Lipphardt of xlCubed was ahead of his times and had a post on creating heatmap tables with Excel based on bubble charts already in August 2008.
Conclusion: Yes you can. It is not a hard problem to create quality heat maps with Microsoft Excel.
But let’s take one step back. What if you don’t want to use the size of the bubbles for visualization? What if you want to create a classic heat map, i.e. the higher the value, the darker the fill color of the cell and vice versa? Following a definition like the one on Wikipedia:
“A heat map is a graphical representation of data where the values taken by a variable in a two-dimensional map are represented as colors.”
Can you create a classic 2-dimensional tabular heatmap with Microsoft Excel as well?
Yes, you can. And there is more than one way to skin the cat. This post shows the different options and includes all examples for free download.
The data
In order to avoid boredom, I decided to use different data for this post’s examples. Everybody is talking about the recession at the moment. So I searched and found an article on marketingcharts.com: Americans Deep in 'Culture of Recession'. The included heatmap visualizes the results of a survey on the question “at which price increase would you stop spending?” for 16 product categories.
Interesting question and interesting results, but I will not discuss the results of the study here. Let’s just take this data and create some tabular heatmaps with Excel.
Depending on the version of Excel you are using and depending whether or not you are willing to use VBA, you have different options for building tabular heatmaps with Microsoft Excel:
Option 1: Excel 2003 without VBA
Even if you shrink back from using VBA in your workbooks, you still can create a simple tabular heatmap with Excel 2003. Use conditional formatting to change the fill color of the cells according to the cell value. It already looks like a heatmap but the result is not really compelling:
The problem is Excel 2003’s limitation to maximum 3 conditions for conditional formatting, i.e. you have only 4 different fill colors on the heatmap. Here is the file for download:
Download 2D Tabular Heatmap XL 2003 Conditional Formatting (Excel 97 – 2003, 53.0K)
Option 2: Excel 2003 with VBA
You don’t mind using VBA in your workbooks? Good. A very simple VBA routine (12 lines of code), changing the color palette of the workbook and an additional mapping table to assign RGB colors to the values produce the following result:
This looks better, doesn’t it? But there is a shortfall again: Excel 2003 and earlier versions are limited to maximum 56 different colors in a workbook. You can change every color in the color palette of the workbook, but there is no way to get more than 56. In this example I changed all colors of the palette to different shades of grey. Maybe I was taking this too far and you would probably get along if you only change – let’s say – 10 or 20 colors to grey shades. In this workbook, however, you don’t have any other colors than white, grey and black available for formatting cells, charts, etc:
Download 2D Tabular Heatmap XL 2003 VBA (Excel 97 – 2003, 76.0K)
Option 3: Excel 2003 with Fabrice Rimlinger’s Sparklines for XL
As already mentioned in the introduction to this post, Fabrice’s Sparklines for XL include UDFs (user defined functions) to create 2- or 3-dimensional heatmaps. With one single formula in one single cell you can make a heatmap like this:
Note: I added one line to Fabrice’s original code to make the shapes of the heatmap transparent. Download the workbook here:
Download 2D Tabular Heatmap XL 2003 Sparklines XL (Excel 97 – 2003, 583.5K)
Fabrice’s Sparklines for XL are an incredible piece of work and offer much more than just heatmaps. You can easily create sparklines, spark bar charts, bullet graphs and much more. I am planning to have one or two posts dedicated to the use of Sparklines for XL during the next months.
Option 4: Excel 2007 without VBA
This one is a piece of cake. Since version 2007 Excel provides a color scale conditional formatting option. Select the range, choose conditional formatting and the option “format all cells based on their values”. Select a 3-color-scale and choose white, a lighter grey and a darker grey for the color scale. That’s it. And here is the result:
Download the file here:
Download 2D Tabular Heatmap XL 2007 Conditional Formatting (Excel 2007, 18.7K)
Option 5: Excel 2007 with VBA
You may ask: If it is that easy to create a tabular heatmap in Excel 2007 using conditional formatting, what do I need VBA for?
Well, the color scale is defined manually by selecting colors in the conditional formatting dialog. Sometimes you want to have the color scale of the heatmap automatically changed after a user input (another variable, another scenario, applying filters, etc.). Therefore you will need VBA. The coloring of the table is still done by conditional formatting, but the color scale is changed by a small VBA routine called in a worksheet_calculate routine or after changing a drop-down-list:
Here is the file for download:
Download 2D Tabular Heatmap XL 2007 VBA (Excel 2007, 30.1K)
Somehow off topic: other options than Excel?
If you are only looking for the visualization and don’t need the heatmap in your Excel model, there are more than enough software packages providing heatmaps. With Tableau, for one, creating the following chart took exactly 3 minutes:
Download the Tableau workbook:
Download 2D Tabular Heatmap Tableau 4.0 (Tableau Packaged Workbook, 39.6K)
To open this workbook you will need Tableau 4.0 (14-day free trial) or the free Tableau Reader.
Leave a Reply to Michael Q Cancel reply