Shading Alternate Rows in Excel

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. Shading Alternate Rows 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. 

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 formula in the box labelled 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 0, and cells in that row are formatted.

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