Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional
In This Chapter
• An overview of Excel's conditional formatting feature
• How to use the graphical conditional formats
• Examples of using conditional formatting formulas
• Tips for using conditional formatting
This chapter explores the topic of conditional formatting, one of Excel's most versatile features. 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.
About Conditional Formatting
Conditional formatting enables you to 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. If not,
no formatting is applied.
Conditional formatting is an easy 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 to identify.
Figure 19-1 shows a worksheet with nine ranges, each with a different type of conditional formatting rule ap-
plied. Here's a brief explanation of each:
• Greater than ten: Values greater than ten are highlighted with a different background color. This rule is just
one of many numeric value related rules that you can apply.
• Above average: Values that are higher than the average value are highlighted.
• Duplicate values: Values that appear in the range more than one time are highlighted.
• Words that contain X: If the cell contains X (upper- or lowercase), the cell is highlighted.
• Data bars: Each cell displays a horizontal bar, the length of which is proportional to its value.
• 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.