Microsoft Office Tutorials and References
In Depth Information
and some of the Font formatting options are disabled. The dialog box also includes a
Clear button that clears any formatting already selected.
Making your own rules
For maximum control, Excel provides the New Formatting Rule dialog box, shown in Figure 19-3. Access this
dialog box by choosing Home ⇒ Styles ⇒ Conditional Formatting ⇒ New Rule.
Use the New Formatting Rule dialog box to re-create all the conditional format rules available via the Ribbon,
as well as 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 (Format All
Cells Based on Their Values), which doesn't have a Format button (it uses graphics rather than cell formatting).
Figure 19-3: Use the New Formatting Rule dialog box to create your own conditional formatting rules.
Here is a summary of the rule types:
• Format All Cells Based on Their Values: Use this rule type to create rules that display data bars, color
scales, or icon sets.
• 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, not between). You can also create rules based on text, dates, blanks, nonblanks, and errors.
• Format Only Top or Bottom Ranked Values: Use this rule type to create rules that involve identifying
cells in the top n, top n percent, bottom n, and bottom n percent.
• 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.