Microsoft Office Tutorials and References

In Depth Information

• To display a sum only when all precedent cells contain values

Some of these formulas may be useful to you. If not, they may inspire you to create other conditional formatting

formulas.

This topic's website contains all the examples in this section. The file is named condi-

tional formatting formulas.xlsx.

To specify conditional formatting based on a formula, select the cells and then choose Home
⇒
Styles
⇒
Condi-

tional 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 for-

mula. As with normal Excel formulas, the formula you enter here must begin with an equal sign (=).

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.

See Figure 19-14.

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)