Excel 2010 Bible

Chapter 20: Visualizing Data Using Conditional Formatting

495

On the CD
The companion CD-ROM contains all the examples in this section. The file is named conditional
formatting formulas.xlsx.

To specify conditional formatting based on a formula, select the cells and then choose Home ➪

Styles ➪ Conditional Formatting ➪ New Rule. This command displays the New Formatting Rule

dialog box. Click the rule type Use a Formula to Determine Which Cells to Format, and you can

specify the formula.

You can type the formula directly into the box, or you can enter a reference to a cell that contains a

logical formula. As with normal Excel formulas, the formula you enter here must begin with an

equal sign (=).

Note
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 con-
ditional formatting is not applied. n

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 shad-

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


8. In the New Formatting Rule dialog box, click the Preview button. Make sure that the
   formula 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.