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:

=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)

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:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))>0

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:

=MOD(ROW(),2)=0

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.