The following article shows how to create Heatmaps in Excel 2003, for Excel 2007 & 2010 please see the updated article “Heatmap Tables with Excel – Revisited”
This Heatmap Table shows you the revenues and the discounts of a company over the course of one year per product group. The size of a bubble shows the revenue made in a particular month and the bubble color shows the discount rate given. The discount rate has been encoded as a range of green colors, ranging from a light green, for low discounts to a dark green for high discounts. The years and product totals are shown at the right and bottom as an integrated part of the table.
Tufte often talks about the integration of number, images, and word, and I think he’s quite right. A way archive this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to archive More Information Per Pixel. I already wrote about graphical tables here, and here.
The heatmap table is based on a regular Excel bubble chart. To integrate a bubble chart into a table the bubbles are positioned in a matrix that has the same row and column layout as our table.
In our case we generate a data series table with one column for the X-Series going from 1-12 for January -December and on column for our Y-Series going from 1-8 for our 8 product groups and one on column for revenue.
Now we create the bubble chart:
To ensure that the charts fit exactly into the table grid we set Min/Max for the X axis to 0.5/12.5 and for the Y axis to 0.5/8.5. Excel would calculate much larger auto scales otherwise. For the Grid lines we set the minor and major unit for both axis to 1.
Now we remove the legend, the X and Y axis, maximize the plot area and align the chart with the Excel table. As the bubbles are initially too large we have to make them smaller. To control the bubble size go to Data Series Options and scale the bubble size to 50%:
This already makes a nice bubble table you could use to reproduce the Twitter Charts.
For the grid lines format your table headers with light gray grid lines, enable the major grid lines in the chart and also format them with light gray. Set the chart area to transparent and reposition the chart so that the chart and the table grid lines align.
To create the heatmap we need a color ramp of 8 green colors, ranging from a light green to a dark green. You can re-define the Excel colors under Excel > Tools > Color, or simply coping the green color ramp of my sample file with Excel > Tools > Color >Copy Colors From
The trick with the colored bubbles is that by default Excel does not plot data points for #NA values. For the heatmap we overlay 8 bubble series, one series per green shade, and show a revenue bubble only if the value fits into the value range that corresponds with a green shade of our color ramp, otherwise we show #NA.
We divide the range 0…MAX(Discount) into 8 interval borders we put into the column headers: , 0, Step,2*Step,…..,MAX(Discount), where Step is MAX(Discount)/8
The data series columns use the following formula to test if a discount value corresponds with an interval / green shade:
=IF(AND(Discount>G$5-Step,Discount<=G$5),Revenue,NA())
The formula returns the revenue, if the discount values is in the interval defined in the column header G$5.
Create now the eight data series so that the bubble size refers to the eight columns in the data table:
Voila, a nice heat map table.
Now it would be nice to have some dynamic control over the size of your bubbles with a combo box in you sheet using an invisible scaling data series
You add a 9th data series:
The cell Data!C102 contains the formula = MAX(Revenue)*ScaleFactor, where scale factor is 2,3,5 for Small, Normal, Large. Having a larger bubble in the data set makes all other bubble smaller (by the factor ScaleFactor). To make this scale bubble invisible set the Border and Area to none.
To further enrich the graphical table we add two rows and columns for revenue and discounts totals :
