Microsoft Office Tutorials and References

In Depth Information

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 that cell A1 is the active cell.

Highlighting a row based on a value

Figure 19-15 shows a worksheet that contains a conditional formula in the range A3:G28. If a name entered in

cell B1 is found in the first column, the entire row for that name is highlighted.

The conditional formatting formula is:

=$A3=$B$1

Notice that a mixed reference is used for cell A3. Because the column part of the reference is absolute, the com-

parison is always done using the contents of column A.

Displaying alternate-row shading

The conditional formatting formula that follows was applied to the range A1:D18, as shown in Figure 19-16, 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 condi-

tional formatting area, the shading is updated 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.