Heat Maps in Excel 2010

This tech tip was provided by John Leonard, Associate Dean of Administration and Finance, Georgia Institute of Technology-main campus.

This tech tip introduces a technique for helping explore patterns in worksheets – heat maps. The conditional formatting feature in Excel 2010 can be used to create heat maps on worksheets without the need for separate external programs or other software programs.

Introduction to heat maps

Suppose that a department head has been tasked with assessing the overall teaching and research activity of her faculty. The IR office provided her with the worksheet below. It contains one row for each faculty member in the department, and the columns contain various research and teaching measures.

Heat maps 1.JPG

The worksheet is quite overwhelming, and quickly identifying the outliers is challenging. Which individuals show the most activity in any given measure (column)? Which show the lowest? Which are highest in the most number of measures (columns)?

A heat map may be constructed by shading the background colors in each cell. In the heat map below, the background colors are assigned column by column. Within a column, the darkest red is assigned to the largest value and white is assigned to the lowest. Other cell background colors are assigned based on the scaled cell value within the range of high and low values.

Heat maps 2.JPG

The darkest cells within each column denote the largest activity values and the lightest colors denote the least activity. The department head can now more quickly identify who is doing the most in each measure.

Constructing heat maps

Conditional formatting is found under the “Home” menu ribbon. The figure below highlights where it can be found.

Heat maps 3.JPG

To replicate the heat map in the example, the user should move column by column, scaling the background colors across the range of values in the column. Here is an example:

  1. Select a column to format.
  2. From the menu ribbon, select “Conditional formatting,” “Color Scales,” “More rules.” The figure below shows how to navigate to the appropriate spot.
    Heat maps 4.JPG
  3. From the “New Formatting Rule,” select the top option “format all cells based on their values,” set the color of the lowest value to “white” and the color associated with the maximum value to “red.” The figure below shows the dialog after these steps have been completed.
    Heat maps 5.JPG
  4. Below is an example of the resulting column in the workbook after the conditional formatting has been applied.
    Heat maps 6.JPG

  5. When there are numerous columns to color, this process can be tedious. The user might find it useful to write a macro or VB script to automate the menu selections. Thus, a column would need only to be highlighted and the macro executed rather than working manually through the menus.

Summary

There are plenty of other options in the conditional formatting menus. For example, one can choose to color only the highs and lows, only the outliers, or anything else imaginable. By choosing different color ranges, different features in the data can be highlighted. By including multiple columns in the range, two-dimensional heat maps can be constructed.

 

 

 Comment

 
To add a comment, Sign In
Total Comments: 6
 
Irene posted on 10/18/2012 1:06 PM
What a great use of conditional formatting! Our office has used color formatting to automatically flag high and low rates (e.g., 1SD above/below mean). But this expanded use is fantastic for the large "ledger sheets" that Deans and other academic leaders typically view and make decisions with. Thank you.
Erin posted on 10/18/2012 1:36 PM
Great tip! And the additional suggestion of creating a macro to automate the formatting is a really good idea that could be applied in many ways.
Gary posted on 10/18/2012 2:55 PM
This is a nice and easy visualization technique - it makes large datasets coherent for users who also want the fine details.
Mary Jo posted on 10/18/2012 6:04 PM
This is a great Tech Tip! I would like to suggest that instead of creating a macro or VB script to replicate this to other columns, it would be easier to highlight the first column with the conditional formatting, double-click on the format painter, and then just hit each column with the format painter to give it the same type of formatting. This will work fine as long as you only apply it one column at a time. It is much faster than going through the menus each time and would be easier to do than setting up a macro or VB script.
Bamby posted on 10/18/2012 6:27 PM
Very useful tip with helpful directions and screen shots.
Onecia posted on 10/19/2012 12:13 PM
I might recommend a statement explaining that the instructions provided in this tech tip apply only to columns containing numbers, and that there are other conditional formatting rules that can be applied to text fields. Because this technique can be used in many ways, I would also suggest that in a follow-up, several more examples of “anything else imaginable” be presented. Additionally, I would suggest a follow-up on text fields (as in number 3 above) and on how to write a macro as mentioned in step 5 of this tech tip. Very nice tech tip!