Note: The following trick uses Excel 2007. It will probably also work in Excel 2010.
I frequently use Excel’s Pivot Table functionality to examine the relationships between variables. While it is a common tool in the analyst’s toolbox I don’t like the default visualization options. This blog post will discuss an approach for visualizing pivot table data using Excel’s built-in conditional formatting functionality to create heat maps.
A Heat Map is a chart that uses colour to visualize a two-dimensional matrix of values. Since pivot tables make the creation of two-dimensional output so easy the heat map is the perfect tool for visualizing a pivot table. The process is straightforward and I demonstrate it with an example.
I start with some fictitious data that has day-of-week and hour-of-day properties. Using the standard approach I create a pivot table that has day-of-week as the columns and the hour-of-day as the rows.
Next, I highlight the pivot table data and copy it to a new worksheet (this is for future formatting). I then highlight the entire data range and select the Conditional Formatting button in the ‘Home’ ribbon. Under Conditional Formatting I select Color Scales and one of the colour ramps that are available.
This immediately colour codes the entire range and creates an attractive heat map that will dynamically adjust its formatting if the cell values change. With a small amount of additional formatting (some bolding, a border, centre-aligned text) a very nice chart can be produced that can be added into a report.
Conditional Formatting offers a quick way to produce heat maps for the kind of two-dimensional datasets analysts produce everyday. I have found that heat maps are an effective way to visualize a large volume of data in a manner that is easily understood by non-analysts. Best of all, if you have Excel 2007 or higher the tools are already installed on your computer.
You may come across the situation where you want to keep the underlying heat map cell colours but delete the numbers in the cells. Unfortunately, deleting the numbers also deletes the colour since this is how conditional formatting works. Frustratingly, copying and pasting in Excel also doesn’t work, not even with ‘Paste Formatting’, as it’s not possible to maintain the format without the numbers. Despite these headaches, I did find a trick for keeping the colours without the numbers.
First, highlight the whole heat map and copy it. Now open Microsoft Word and copy the heat map into a Word document.
Second, select the heat map in Word and copy it. Now open up a new Excel document and paste into the first cell. The heat map should be pasted in its entirety back into Excel and if you delete the numbers the colours should still be there.
Update: Reader Frank Fery writes that it is possible to remove the numbers from a heat map without resorting to the copy-to-Word trick. He provided a link to superuser.com that states that if you change the custom format of the cell to ;;; it will hide the numbers. I still prefer the Word way though because it allows me to actually lay out different numbers on top of the heat map. Why would you want this? As an example, I once created a heat map where the surface represented calls-for-service volume but the numbers on the chart were numbers of officers working. Thanks for the feedback Frank.