Chapter 5: Formatting Worksheets
55
Creating formula-based rules
The graphical conditional formats are generally used to show a cell in relation to other,
nearby cells. Formula-based rules generally apply to one cell independently. The same rule
may apply to many cells, but each cell is considered on its own.The Highlight Cells Rules and Top/Bottom Rules options under the Conditional Formatting
Ribbon control are commonly used shortcuts for formula-based rules. If you choose Home
➪ Styles ➪ Conditional Formatting ➪ New Rule, Excel displays the New Formatting Rule
dialog box. You saw this dialog box in the previous section when the built-in graphical
conditional formats needed tweaking. The entry Format Only Cells that Contain is another
shortcut for a formula-based rule.The last entry in the New Formatting Rule dialog box is Use a Formula to Determine Which Cells
to Format. This is the entry you choose if none of the other shortcuts does what you want. It
provides maximum flexibility for creating a rule.The formula must be a logical one 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 New Formatting Rule or Edit 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 format-
ting 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:- Select the range A1:B10, and make sure that cell A1 is the active cell.
- Choose Home ➪ Styles ➪ Conditional Formatting ➪ New Rule. The New Formatting
Rule dialog box appears. - Click the Use a Formula to Determine Which Cells to Format rule type.
- Enter the following formula into the Formula box:
=ISTEXT(A1)- Click the Format button. The Format Cells dialog box appears.
- From the Fill tab, specify the cell shading that will be applied if the formula returns
TRUE. - Click OK to return to the New Formatting Rule dialog box (see Figure 5.17).