Microsoft Office Tutorials and References

In Depth Information

**Chapter 19: Conditional Formatting and Data Validation**

The formula must be a logical formula that returns either TRUE or FALSE. If the formula

evaluates to TRUE, the condition is satisfied, and the conditional formatting is applied.

If the formula evaluates to FALSE, the conditional formatting is not applied.

Understanding relative and absolute references

If the formula that you enter into the Conditional Formatting 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 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 you want

applied if the formula returns TRUE.

7.
Click OK to return to the New Formatting Rule dialog box (see Figure 19-16).

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