Microsoft Office Tutorials and References
In Depth Information
Tip 19: Shading Alternate Rows Using Conditional Formatting
Shading Alternate Rows Using Conditional
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
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:
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.