# Heatmap Tables with Excel

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, 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 :

## 8 Replies to “Heatmap Tables with Excel”

1. Daniel Peter says:

2. Andreas –

Very nice. The chart is reasonably done, and the instructions are clear and complete.

3. derek says:

This heat map has bivariate data in the matrix, and also has a sensible order in one direction (months). But if your heat map data doesn’t need to be in any particular order, you can take a leaf out of Jacque Bertin’s book and diagonalize it for clearer pattern reading. I’ve described a method for doing it on my blog here.

4. Pankaj says:

Hi,

Could you help me understand:

How the heat map in airlines dashboard has been created using micro charts?

Thanks,

Pankaj

5. Pankaj says:

Hi Andreas,

I have lost the original airlines dashboard excel file which was very helpful in understanding how and what kind of data was being used to build it.

i have been searching for that file and am unable to find it anywhere. The file that is available here is not helping me. Can you please provide me that orginal excel which was earlier available?

Regards,

Pankaj

6. Ken says:

We are trying to produce a bubble heatmap using Excel 2007. The instructions above are for 2003. Has anyone been able to produce these maps in 2007?

7. Prabhu says:

Hey very interesting chart. I tried to create one for myself. It came out very well, just that I find the bubbles smugged. The outline of the circles are not crisp. It looks like there are to ormore bubbles behing it.

This site uses Akismet to reduce spam. Learn how your comment data is processed.