Ask Amy

Question: Recently I created a large worksheet and was asked to make the “final” worksheet easier to read by shading every other row. However, it wasn’t really “final” and I ended up removing and reshading the worksheet more than once. Is there an easier way?

Answer: Yes, there is a way to do this so that the shading always affects every other row no matter how may rows you insert and/or delete.

Excel 2007 and 2010

1.    Highlight the area that is to contain shaded rows. Don’t skip this step because the dynamic updating will only work within this range.

2.    Click on the Home tab.

3.    Within the Styles group, click on the Conditional Formatting button.

4.    From the menu, select Highlight Cells Rules.

5.    From the shortcut menu, select More Rules… The New Formatting Rule dialog box displays.

6.    From the Select a Rule Type: list, select Use a formula to determine which cells to format. The bottom half of the dialog box changes.

7.    In the Format values where this formula is true: edit box, enter: =MOD(ROW(),2)=0  .

8.    Click on the [Format] button. The Format Cells dialog box displays.

9.    Click on the Fill tab and select a color for the shading. Lighter colors work best.

10. Click on the [OK] button to close the Format Cells dialog box.

11. Click on the [OK] button to close the New Formatting Rule dialog box. Every other selected row is shaded.

Excel 2003

1.    Highlight the area that is to contain shaded rows. Don’t skip this step because the dynamic updating will only work within this range.

2.    Click on the Format menu and click on the Conditional Formatting button. The Conditional Formatting dialog box displays.

3.    From the first drop-down list, select Formula Is.

4.    In edit box, enter:  =MOD(ROW(),2)=0  .

5.    Click the Format button. The Format Cells dialog box displays.

12. Click on the Patterns tab and select a color for the shading. Lighter colors work best.

13. Click on the [OK] button to close the Format Cells dialog box.

14. Click on the [OK] button to close the New Formatting Rule dialog box. Every other selected row is shaded.