Microsoft Office Tutorials and References
In Depth Information
Notice that the formula entered in Step 4 contains a relative reference to the upper-left
cell in the selected range.
Figure 19-14: Creating a conditional formatting rule based on a formula.
5. Click the Format button to display the Format Cells dialog box.
6. From the Fill tab, specify the cell shading that will be applied if the formula returns TRUE.
7. Click OK to return to the New Formatting Rule dialog box.
8. Click OK to close the New Formatting Rule dialog box.
Generally, when entering a conditional formatting formula for a range of cells, you'll use a reference to the act-
ive cell, which is typically the upper-left cell in the selected range. One exception is when you need to refer to a
specific cell. For example, suppose that you select range A1:B10, and you want to apply formatting to all cells
in the range that exceed the value in cell C1. Enter this conditional formatting formula:
In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected
range. In other words, the conditional formatting formula for cell A2 looks like this:
The relative cell reference is adjusted, but the absolute cell reference is not.
Conditional formatting formula examples
Each of these examples uses a formula entered directly into the New Formatting Rule dialog box, after selecting
the Use a Formula to Determine Which Cells to Format rule type. You decide the type of formatting that you