Microsoft Office Tutorials and References

In Depth Information

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

Locating cells that contain conditional formatting

You can’t tell, just by looking at a cell, whether it contains conditional formatting. You can,

however, use Excel’s Go To dialog box to select such cells.

1.
Choose Home

Editing

Find & Select

Go To Special.

➜

➜

➜

2.
In the Go To Special dialog box, select the Conditional Formats option.

3.
To select all cells on the worksheet containing conditional formatting, select the All

option. To select only the cells that contain the same conditional formatting as the active

cell, select the Same option.

4.
Click OK.

Excel selects the cells for you.

Creating formula-based rules

Excel’s conditional formatting feature is versatile, but sometimes it’s just not quite versatile

enough. Fortunately, you can extend its versatility by writing conditional formatting formulas.

The examples later in this section describe how to create conditional formatting formulas for the

following:

h
To identify text entries

h
To identify dates that fall on a weekend

h
To format cells that are in odd-numbered rows or columns (for dynamic alternate row or

columns shading)

h
To format groups of rows (for example, shading every group of two rows)

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

h
To identify text cells that begin with the same first letter as a letter in a cell

h
To identify cells that contain a value that meets a criterion entered in a cell

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

conditional formatting formulas.

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

➜

New Rule. This command displays the New Formatting Rule

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

be able to specify the formula.

Styles

Conditional Formatting

➜

➜

You can type the formula directly into the Formula box, or you can enter a reference to an

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

sign (=).