Creating Powerful Conditional Formats

Businesses often use Excel to track corporate spending and revenue. The actual figures are very important, of course, but it's also useful for managers to be able to glance at their data and determine whether the data exceeds expectations, falls within an acceptable range, or requires attention because the value falls below expectations. In versions prior to Excel 2007, you could create three conditions and define a format for each one. For example, you could create the following rules:

• If monthly sales are more than 10 percent ahead of sales during the same month in the previous year, display the value in green.

• If monthly sales are greater than or equal to sales during the same month in the previous year, display the value in yellow.

• If monthly sales are fewer than sales during the same month in the previous year, display the value in red.

In Excel 2007, you can have as many rules as you like, apply several rules to a single data value, choose to stop evaluating rules after a particular rule has been applied, and change the order in which the rules are evaluated without having to delete and re-create the rules you change. You can also apply several new types of conditional data formats: data bars, which create a horizontal bar across a cell to indicate how large the value is; color gradients, which change a cell's fill color to indicate how large the value is; and icon sets, which display one of three icons depending on the guidelines you establish.