Microsoft Office Tutorials and References

In Depth Information

**Understanding relative and absolute references**

Understanding relative and absolute references

If the formula that you enter into the New Formatting Rule dialog box contains a cell

reference, that reference 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
otherwise. 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.
The New Formatting

Rule dialog box appears.

3.
Click the Use a formula to determine which cells to format rule type.

4.
Enter the following formula in the Formula box:

=ISTEXT(A1)

5.
Click the Format button.
The Format Cells dialog box appears.

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 (see Figure 19.22).

FIGURE 19.22

Creating a conditional formatting rule based on a formula

8.
Click OK to close the New Formatting Rule dialog box.