We’ve revisited one of our more popular guides Heatmap Tables with Excel as they can be a very effective way of presenting data on a dashboard, and have now updated it for Excel 2010.
Heat Map Excel To Showcase Revenues and Discounts
This Heatmap Table is designed to show 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 numbers, images and words; I think he’s quite right. A way to achieve this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to show “More Information Per Pixel“.
How to Create a Heat Map in Excel Using a Regular Bubble Chart
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 one column for our Y-Series going from 1-8 for our 8 product groups and one column for revenue.
In the sample spreadsheet we’ve setup some simple excel formula to translate data from the classic grid layout:
to the required format:
Now we can insert 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. Also set the Major units to 1 so we can use that later to set some grid lines.
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 and grid lines with light gray grid lines. Resize the plot area, remove the border and re-position the chart so that the chart and the table grid lines align.
To create the heatmap with different colored bubbles we use the fact 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 MAX(Discount)..0 into 8 groups to define the colours.
The data series columns use the following formula to test if a discount value corresponds with an interval / colour shade:
=IF(AND($E7>I$6-Step,$E7<=I$6),$D7,NA())
The formula returns the revenue, if the discount values is in the interval defined in the column header I$5.
Now create the eight data series so that the bubble size refers to the eight columns in the data table:
And use the Excel chart styles to pick a colour range – make sure you remove the border from the chart area.
And you could use the chart styles to quickly switch between different colours – or customise each series to refine the colors.
You can download a starting point for these files here: HeatmapSample.xlsx. Most of the formulae should adapt to data values that you can feed into the data sheets, including data straight from Analysis Services if using XLCubed grids or formulae.
You can see an interactive version of the Heatmap – we added a link to some cube data, some Slicers for driving the parameters and then published to XLCubedWeb.
