Making heat maps with spreadsheets

http://commons.wikimedia.org/wiki/File:Geothermal_heat_map_US.pngSpreadsheets have a lot of neat ways to graph things, but there’s one common type of graph that you won’t find on the built-in list: heat maps.

Most people are used to the idea of heat maps, if not the name. One example is the colored maps you see on weather reports, where the redder an area is, the hotter it is. Although these same visualizations are used to depict things other than heat, such as humidity, precipitation, crime rates, and so on, the type of visualization is called a heat map.

Even though it’s not a built-in function, it’s pretty easy to get modern versions of Excel to make one for you, and possible to other spreadsheet programs do so as well, although somewhat more tediously and therefore less precisely. All you have to do is take advantage of conditional formatting.

For example, look at the table below.

Median income by age and education, based on the 2000 census

21-24

25-34

35-44

45-54

55-64

65-74

Not a high school graduate $17,170 $21,470 $25,345 $26,687 $27,980 $20,450
High school graduate $21,078 $28,262 $34,002 $36,634 $36,519 $26,396
Some college $22,088 $32,063 $40,855 $42,852 $43,036 $32,524
Bachelor degree $30,258 $41,854 $56,923 $57,663 $59,420 $49,109
Advanced degree $30,618 $50,888 $69,764 $71,239 $70,990 $65,264

Although it’s simple, this table shows the power of the heat map. You can easily tell at a glance which numbers are high and which are low, and which ones are pretty steady. The fact that median income drops significantly for non-college graduates between the ages ranges of 5-64 and 65-74 really stands out, without having to look at the numbers.

To do this in Excel 2007 or above, just access the conditional formatting menu and select any of the “color scales”.

For other programs, the process is more involved.
1) Figure out where your breakpoints are.
Your breakpoints are the numbers at which one interval changes to another. For example, on a scale of 0-10, logic tells you that if you you want to have two equal intervals then you want everything up to and including 5 to be one color, and everything above that the other color. That makes 5 your breakpoint. The actual formula is that breakpoints occur at intervals equal to the maximum minus the minimum, divided by the number of intervals you want. In spreadsheet terms, that would be IntervalLength=(max(range)-min(range)/NumberOfIntervals. So the breakpoints are the minimum plus the interval length, then the minimum plus the interval length times two, and so on. For example, If you want to divide the range of 1-11 into M4 intervals, the interval length would be (11-1)/4 = 2.5. So the breakpoints would be 3.5, 6, and 8.5.

2) Format all the cells to have the background color you want the highest interval to have.
I recommend green (assuming higher numbers are positive).

3) Set up a rule for each interval, setting all cells less than or equal to that number to have a background the color of the appropriate tier.
If you’re separating it into 4 intervals I recommend red for the lowest, orange for the middle, and yellow for the third. Again, this is assuming high is good and low is bad. Don’t forget to pay attention to the order of the rules. The lowest one should be the most important.

And that’s it! When you’re done, you might end up with a table that looks something like this:

Median income by age and education, based on the 2000 census

21-24

25-34

35-44

45-54

55-64

65-74

Not a high school graduate $17,170 $21,470 $25,345 $26,687 $27,980 $20,450
High school graduate $21,078 $28,262 $34,002 $36,634 $36,519 $26,396
Some college $22,088 $32,063 $40,855 $42,852 $43,036 $32,524
Bachelor degree $30,258 $41,854 $56,923 $57,663 $59,420 $49,109
Advanced degree $30,618 $50,888 $69,764 $71,239 $70,990 $65,264