Graded Stoplight Formatting in Excel 2007
Gayle Fink (Gayle Fink)

This corner of the newsletter is reserved for readers to share practical tips, techniques and shortcuts that can make a difference in our work. Send your tips to Gayle Fink (gfink@bowiestate.edu).

This month’s Tech Tip is provided by Viktor Brenner, Institutional Research Coordinator, Waukesha County Technical College (vbrenner@wctc.edu). Thanks Viktor!

We are all familiar with red/green/yellow stoplight formatting from dashboards. Excel 2007 introduces a new tool that allows you to apply conditional formatting that applies a color gradient based on the value of the cell. This allows the color of a cell to reflect performance and produce great impact. Consider the following example of course passing rates:

Green formatting is applied to the highest passing rates, red to the lowest, and color gradations fill in the values in-between.

To apply this formatting, select the cells you wish to format, then from the "Home" ribbon select "Conditional Formatting" and then "Color Scales." You may already have something like red-yellow-green to choose from, or you can create your own rule by selecting Conditional Formatting>New Rule. You will get an option box that allows you to specify if you want two or three colors, what colors to use, and how to use them. The formatting above follows this rule (see image at right). Using this rule, it is immediately apparent which courses had the highest and which the lowest passing rates—even if readers are not used to understanding numbers!

Here’s a note from a reader about last month’s Technical Tip, “Fun with the Excel 2007 Format Painter.” Bob Daly, Assistant Vice Chancellor of Strategic Academic Research & Analysis, University of California, Riverside (rdaly@ucr.edu), writes:

The Format Painter has been a feature in Office for as long as I can remember, not a new feature in 2007/08. It was definitely in Office 2003/04 and probably in versions before those. But you are right, it is one of the best tools in Office – be it 2003, 2004, 2007, or 2008.