Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
Making your own rules
For do-it-yourself types, Excel provides the New Formatting Rule dialog box, shown in Figure
19-3. Access this dialog box by choosing Home
Figure 19-3: Use the New Formatting Rule dialog box to create your own conditional formatting rules.
The New Formatting Rule dialog box lets you re-create all the conditional format rules available
via the Ribbon as well as create new rules.
First, select a general rule type from the list at the top of the dialog box. The bottom part of the
dialog box varies, depending on your selection at the top. After you specify the rule, click the
Format button to specify the type of formatting to apply if the condition is met. An exception is the
first rule type, which doesn’t have a Format button. (It uses graphics rather than cell formatting.)
Following is a summary of the rule types:
h Format All Cells Based on Their Values: Use this rule type to create rules that display
data bars, color scales, or icon sets.
h Format Only Cells That Contain: Use this rule type to create rules that format cells based
on mathematical comparisons (greater than, less than, greater than or equal to, less than
or equal to, equal to, not equal to, between, or not between). You can also create rules
based on text, dates, blanks, nonblanks, and errors. This rule type is very similar to how
conditional formatting was set up in previous versions of Excel.
h Format Only Top or Bottom Ranked Values: Use this rule type to create rules that
involve identifying cells in the top top n percent, bottom or bottom n percent.
h Format Only Values That Are Above or Below Average: Use this rule type to create rules
that identify cells that are above average, below average, or within a specified standard
deviation from the average.