Know Moore About Excel

Conditional formatting can be a great tool to add quick, visual ques to your spreadsheet and help call attention to extremely high values, extremely low values, values above or below the average or any other metric that you want to measure.  Excel 2007 includes many standard color indicator formats that can be used as shaded color bars to identify highs and lows.  Color icons could also be used to help make those call outs.  Either method is available under the Conditional Formatting button on the Home ribbon.

Conditional Formatting Options

Examples

A couple of examples of these techniques in use are as follows.  The first example shows three different applications of color shading.  In the first column shows shades of red in varying degrees of opacity from 35 to 70.  Any number under 35 in the first column is left with no background color.  Any number over 70 is colored solid red.  The second column displays a 3 color shaded scale with the highest values (preferred values) in the column being blue, the middle values being yellow and the lowest values (potential problems) being red.  The third column uses the same color shading scale but in the opposite manner – the highest values (potential problems) are red, the middle values are yellow and the lowest values (preferred values) are blue.

Color Shade Example 1

In the second example, the same numeric data is used to demonstrate data bars and icon sets.  The second column formatting has been changed to apply a blue data bar.  The third column formatting has been changed to apply an icon set.

Data bar and icon set example

Shout out to Amit Velingkar for writing a great blog post on Conditional Formatting Simplified in Excel 2010.