Microsoft Office Tutorials and References
In Depth Information
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
• Highlight Cell Rules: Examples rules include highlighting cells that are greater than a particular value, are
between two values, contain specific text string, contain a date, or are duplicated.
• Top Bottom Rules: Examples include highlighting the top ten items, the items in the bottom 20 percent, and
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 for-
• 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.
Formatting types you can apply
When you select a conditional formatting rule, Excel displays a dialog box specific to that rule. These dialog
boxes have one thing in a common: a drop-down list with common formatting suggestions.
Figure 19-2 shows the dialog box that appears when you choose Home ⇒ Styles ⇒ Conditional Formatting ⇒ High-
light Cells Rules ⇒ Between. This particular rule applies the formatting if the value in the cell falls between two
specified values. In this case, you enter the two values (or specify cell references) and then use choices from the
drop-down list to set the type of formatting to display if the condition is met.
Figure 19-2: One of several different conditional formatting dialog boxes.
The formatting suggestions in the drop-down list are just a few of thousands of different formatting combina-
tions. If none of Excel's suggestions is what you want, choose the Custom Format option from the drop-down
list to display the Format Cells dialog box. You can specify the format in any or all of the four tabs: Number,
Font, Border, and Fill.
The Format Cells dialog box used for conditional formatting is a modified version of the
standard Format Cells dialog box. It doesn't have the Alignment and Protection tabs,