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