Apply alternate row shading that updates automatically (Excel 2003/2007/2010)

Worksheets often span hundreds or thousands of rows, which can wreak havoc on your eyes. Alternate row shading makes it easier to read large data tables, but if you manually shade rows, any changes you make to the data later will cause problems with your shading.

Here’s a quick way to apply row shading to every other row in your data using conditional formatting. Just follow these steps:

1. Select the data range to which you want to apply alternate row shading.
2. Choose Format | Conditional Formatting from the menu bar to open the Conditional Formatting window. (In 2007/2010, click on the Home tab and then click on Conditional Formatting in the Styles area and choose New Rule to open the New Formatting Rule window.)
3. Select Formula Is from the dropdown list. (In 2007/2010, select Use A Formula To Determine Which Cells To Format.)
4. In the corresponding text box, enter: =MOD(ROW(),2)=1
5. Click the Format button to open the Format Cells dialog box and select the Patterns tab.
6. Select the color you want to use for your shading in the color palette and click OK.
7. Click OK in the Conditional Formatting window (New Formatting Rule window in 2007) to apply the alternate row shading.

Excel shades the first row of your selected data range, and then every other row following. If you add or delete rows, the row shading updates to accommodate your changes.