Microsoft Office Tutorials and References

In Depth Information

**Tip 19: Shading Alternate Rows Using Conditional Formatting**

Shading Alternate Rows Using Conditional

Formatting

When you create a table (using Insert➜Tables➜Table), you have the option of formatting the table in

such a way that alternate rows are shaded. Alternate row shading can make your spreadsheets easier

to read.

This tip describes how to use conditional formatting to obtain alternate row shading for any range of

data. It’s a dynamic technique: If you add or delete rows within the conditional formatting area, the

shading is updated automatically.

Displaying alternate row shading

Figure 19-1 shows an example. Here’s how to apply shading to alternate rows:

1.
Select the range to format.

2.
Choose Home➜Conditional Formatting➜New Rule.

The New Formatting Rule dialog box appears.

3.
For the rule type, choose Use a Formula to Determine Which Cells to Format.

4.
Enter the following formula in the box labeled Format Values Where This Formulas Is True:

=MOD(ROW(),2)=0

5.
Click the Format button.

The Format Cells dialog box appears.

6.
In the Format Cells dialog box, click the Fill tab and select a background fill color.

7.
Click OK to close the Format Cells dialog box, and click OK again to close the New Formatting

Rule dialog box.

This conditional formatting 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
, and cells in that row are formatted.
0

For alternate shading of columns, use the COLUMN function instead of the ROW function.