Microsoft Office Tutorials and References

In Depth Information

**Creating conditional formatting formulas**

Figure 9-31
Use the last rule type in the list to create your own conditional formatting formulas.

You can create formulas to perform tasks such as identifying dates that fall on specific days

of the week, specifying particular values, or doing anything you can’t quite accomplish

using the built-in conditional formatting tools. For example, using our worksheet example,

we typed the following formula in the Format Values Where This Formula Is True text box in

the New Formatting Rule dialog box:

=IF(ISERROR(B4),0)=0

Then we clicked the Format button and selected a color on the Fill tab. The formula

applies the selected ill color to any cell that generates an error value. (The cell

reference B4 is the relative reference of the upper-left cell of the range to which the format is

applied.) When you use this technique, you can type any formula that results in the

logical values TRUE (1) or FALSE (0). For example, you could use a logical formula such as

=N4>AVERAGE($N$4:$N$37), which combines relative and absolute references to apply

formatting to a cell when the value it contains is less than the average of the specified

range. When you use relative references in this situation, the formatting formulas adjust in

each cell where you apply or copy them, just as regular cell formulas do.

For more information, see “Using cell references in formulas” in Chapter 12 and “Understand-

ing logical functions” in Chapter 14, “Everyday functions.” Also, see other topics in Chapter 14

and Chapter 15, “Formatting and calculating date and time.”