Page 478 - Excel 2007 Bible

27_044039 ch21.qxp 11/21/06 11:12 AM Page 435
Visualizing Data Using Conditional Formatting
Understanding relative and absolute references
If the formula that you enter into the Conditional Formatting dialog box contains a cell reference, that refer-
ence is considered a relative reference, based on the upper-left cell in the selected range.
For example, suppose that you want to set up a conditional formatting condition that applies shading to
cells in range A1:B10 only if the cell contains text. None of Excel’s conditional formatting options can do
this task, so you need to create a formula that will return TRUE if the cell contains text and FALSE other-
wise. Follow these steps:
1. Select the range A1:B10 and ensure that cell A1 is the active cell.
2. Choose Home ➪ Styles ➪ Conditional Formatting ➪ New Rule to display the New
Formatting Rule dialog box.
3. Click the rule type labeled Use A Formula To Determine Which Cells To Format.
4. Enter the following formula in the Formula box:
=ISTEXT(A1)
5. Click the Format button to display the Format Cells dialog box.
6. In the Format Cells dialog box, click the Fill tab and specify the cell shading that will be
applied if the formula returns TRUE. 21
7. Click OK to return to the New Formatting Rule dialog box (refer to Figure 21.16).
8. In the New Formatting Rule dialog box, click the Preview button to make sure that the for-
mula is working correctly and to see a preview of your selected formatting.
9. If the preview looks correct, click OK to close the New Formatting Rule dialog box.
Notice that the formula entered in Step 4 contains a relative reference to the upper-left cell in the selected
range.
Generally, when entering a conditional formatting formula for a range of cells, you’ll use a reference to the
active cell, which is normally 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 format-
ting to all cells in the range that exceed the value in cell C1. Enter this conditional formatting formula:
=A1>$C$1
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:
=A2>$C$1
The relative cell reference is adjusted, but the absolute cell reference is not.

435