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.
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.
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.
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:
Select a column to format.
From the menu ribbon, select “Conditional formatting,” “Color Scales,” “More rules.” The figure below shows how to navigate to the appropriate spot.
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.
Below is an example of the resulting column in the workbook after the conditional formatting has been applied.
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.
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.