Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
Conditional formatting is a useful way to quickly identify erroneous cell entries or cells of a
particular type. You can use a format (such as bright red cell shading) to make particular cells easy
Figure 19-1 shows a worksheet with nine ranges, each with a different type of conditional
formatting rule applied. Here’s a brief explanation of each:
h Greater than 10: Values greater than 10 are highlighted with a different background
color. This rule is just one of many numeric value-related rules that you can apply.
h Above average: Values that are higher than the average value are highlighted.
h Duplicate values: Values that appear more than one time are highlighted.
h Words that contain X: If the cell contains the letter X (upper- or lowercase), the cell is
h Data Bars: Each cell displays a horizontal bar, proportional to its value.
h Color Scale: The background color varies, depending on the value of the cells. You can
choose from several different color scales or create your own.
h Icon Set: This is one of many icon sets, which display a small graphic in the cell. The
graphic varies, depending on the cell value.
h Icon Set: This is another icon set, with all but one icon hidden.
h Custom Rule: The rule for this checkerboard pattern is based on a formula:
This workbook, named conditional formatting examples.xlsx , is available on
the companion CD-ROM.
Specifying conditional formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the
commands on the Home
Conditional Formatting drop-down list to specify a rule. The
h Highlight Cell Rules: Examples include highlighting cells that are greater than a particular
value, are between two values, contain specific text string, or are duplicated.
h Top Bottom Rules: Examples include highlighting the top ten items, the items in the
bottom 20 percent, or items that are above average.
h Data Bars: This applies graphic bars directly in the cells, proportional to the cells’ values.
h Color Scales: This applies background color, proportional to the cells’ values.