Chapter 5: Formatting Worksheets
55
Using Conditional Formatting
You can apply conditional formatting to a cell so that the cell looks different depending on
its contents. Conditional formatting is a useful tool for visualizing numeric data. In some
cases, conditional formatting may be a viable alternative to creating a chart.
Conditional formatting lets you apply cell formatting selectively and automatically, based on
the contents of the cells. For example, you can apply conditional formatting in such a way that
all negative values in a range have a light-yellow background color. When you enter or change
a value in the range, Excel examines the value and checks the conditional formatting rules for
the cell. If the value is negative, the background is shaded; otherwise, no formatting is applied.
Specifying conditional formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of
the commands from the Home ➪ Styles ➪ Conditional Formatting drop-down list to specify
a rule. The choices are as follows:
Highlight Cells Rules Examples include highlighting cells that are greater than a par-
ticular value, are between two values, contain a specific text string, contain a date, or are
duplicated.
Top/Bottom Rules Examples include highlighting the top ten items, the items in the bot-
tom 20 percent, and the items that are above average.
Data Bars Applies graphic bars directly in the cells, proportional to the cell’s value.
Color Scales Applies background color, proportional to the cell’s value.
Icon Sets Displays icons directly in the cells. The icons depend on the cell’s value.
New Rule Enables you to specify other conditional formatting rules, including rules based
on a logical formula.
Clear Rules Deletes all the conditional formatting rules from the selected cells.
Manage Rules Displays the Conditional Formatting Rules Manager dialog box in which
you create new conditional formatting rules, edit rules, or delete rules.
Using graphical conditional formats
This section describes the three conditional formatting options that display graphics: data
bars, color scales, and icon sets. These types of conditional formatting can be useful for
visualizing the values in a range.
Using data bars
The data bars conditional format displays horizontal bars directly in the cell. The length of
the bar is based on the value of the cell relative to the other values in the range.