Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
The companion CD-ROM contains all the examples in this section. The file is named
conditional formatting formulas.xlsx .
Identifying weekend days
Excel provides a number of conditional formatting rules that deal with dates, but it doesn’t let
you identify dates that fall on a weekend. Use this formula to identify weekend dates:
This formula assumes that a range is selected and also that cell A1 is the active cell.
Identifying cells containing more than one word
You also can use conditional formatting with text. For example, you can use the following
conditional formatting formula to apply formatting to cells that contain more than one word:
This formula assumes that the selected range begins in cell A1. The formula works by counting
the space characters in the cell (using the TRIM function to strip out multiple spaces). If the count
is greater than 0, the formula returns TRUE, and the conditional formatting is applied.
Displaying alternate-row shading
The conditional formatting formula that follows was applied to the range A1:D18, as shown in
Figure 19-17, to apply shading to alternate rows:
Alternate row shading can make your spreadsheets easier to read. If you add or delete rows
within the conditional formatting area, the shading updates automatically.
This formula uses the ROW function (which returns the row number) and the MOD function
(which returns the remainder of its first argument divided by its second argument). For cells in
even-numbered rows, the MOD function returns 0, and cells in that row are formatted.
For alternate shading of columns, use the COLUMN function instead of the ROW function.