Heat maps are a popular way to display varying values in a two-dimensional display. The heat map may be an actual geographic map with regions colored differently according to some variable, for example, population density or electoral results. I showed such maps, also called cartograms or choropleths, in Redrawn Electoral Maps and An Undistorted Election Results Map.

Heat maps are not just used for alternate representations of geographical maps. They can be used to map out sectors of a hard drive, or performance of various companies or industries in the stock market. In this post I’ll show a simple heat map showing relative values within a 2D worksheet grid, a crosstab of days of the week and times of day.

In Columbus Web Analytics Wednesday — Feedback Analysis, Tim Wilson of the Gilligan on Data blog surveyed attendees at a Web Analytics meeting to find out what times are good for holding future meetings. It was a simple enough survey, and there were 21 respondents. Tim broke out the results in a bubble-style chart, which I reproduce below. Obviously the respondents were not limited to one choice.

From Tim’s chart, it is evident that Happy Hour and Dinner time slots work best, and Wednesday is the best night of the week, with Monday, Tuesday, and Thursday all at nearly the same distant second place.

A well known problem with bubble charts is that we don’t perceive areas proportionally to the values they encode. Tim even said:

Compare the size of a “1″ to the size of the “14″ — does it look to you like the larger circle is fourteen times as big as the smaller one? It doesn’t to me.

It doesn’t to me, either. We seem to underestimate the differences when areas are used to encode values. It’s the opposite when diameters are used. I think we overestimate the differences to an even greater degree.

I didn’t even label the smallest data points, because the labels would have obliterated the circles.

The purpose of this chart is to get a quick idea of the survey responses, so the bubble approach isn’t really inappropriate (even the bubble chart based on diameter). Its point is readily understood, and since there is a clear winner, that winner stands out. But there are alternatives, and I decided to see how a heat map would work.

Heat Maps

Heat maps are pretty easy to generate in Excel 2007 using a worksheet range and conditional formatting. Start with a simple worksheet range.

The Conditional Formatting command is accessed from the Home tab on the Excel 2007 ribbon. Hovering the cursor over the button shows the fancy Super Tooltip below the ribbon.

Clicking on the Conditional Formatting button shows a series of built-in formatting styles plus some style management commands at the bottom. Color Scales has the built-in color gradients. For this data, I selected the first one, which goes from red at the low end, through yellow in the midrange, to green at the high end.

The result is much as expected, but I thought the red and yellow were both a bit overstated. In fact, it would seem better to use a single color that ranges from a low saturation at the low value to a high saturation at the high value. A one-color display is simpler to understand, and does not discriminate against those with color-deficient vision.

I went to Manage Rules on the Conditional Formatting dropdown, which opened the Rules manager.

I selected the Graded Color Scale rule, which I just applied, and clicked Edit Rule.

I switched to a two-color scale, kept the green shade at the high end, but selected a very lightly saturated shade of the same hue for the minimum. (I could have chosen white, but the faintest shade of green makes it obvious that the rule is being applied to the entire range.

The result isn’t too bad. Like the bubble chart, it’s reasonably self-explanatory. With little conscious thought, it is apparent light for low values and darker for higher values. The winning value stands out clearly. Another nice feature of this graphic is that is is very compact.

Related Posts:

• Map Bubble Chart of Store Closings
• Reforming a Health Care Reform Chart
• VBA Conditional Formatting of Charts by Value
• VBA Conditional Formatting of Charts by Series Name
• VBA Conditional Formatting of Charts by Category Label
• Physics Lesson
• An Undistorted Election Results Map
• Conditional XY Charts Without VBA
• Candlestick Alternative: Individually Colored Up-Down Bars
• Pivot Table Conditional Formatting with VBA