Apply Shading To Alternate Rows Or Columns In A Worksheet

One way to apply shading to alternate rows or columns in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered and then applies the shading accordingly. The formula is shown here:

=MOD(ROW(),2)=0

Note: If you want to apply shading to alternate columns instead of alternate rows, enter =MOD(COLUMN(),2)=0 instead.

1. On the worksheet, do one of the following:

   • To apply the shading to a specific range of cells, select the cells you want to format.

   • To apply the shading to the entire worksheet, click the Select All button.

     

2. On the Home tab, in the Styles group, select the arrow next to Conditional Formatting, and then select New Rule.

3. In the New Formatting Rule dialog box, under Select a Rule Type, select Use a formula to determine which cells to format.

4. In the Format values where this formula is true box, enter =MOD(ROW(),2)=0, as shown in the following illustration.

   

5. Select Format.

6. In the Format Cells dialog box, select the Fill tab.

7. Select the background or pattern color that you want to use for the shaded rows, and then select OK.

   At this point, the color you just selected should appear in the Preview window in the New Formatting Rule dialog box.

8. To apply the formatting to the cells on your worksheet, select OK

Note: To view or edit the conditional formatting rule, on the Home tab, in the Styles group, select the arrow next to Conditional Formatting, and then select Manage Rules.