In Excel 2007 and above, it’s easy to shade alternate rows by creating a Table. However in earlier versions it was necessary to use a combination of conditional formatting and the MOD function. Note that this method will work in 2007 and above as an alternative to creating a Table.

Selecting The Range


Select the rows or cells to be formatted

Apply the Conditional Formatting Formula


Select Format > Conditional Formatting from the menu
Select "Formula Is" from the drop-down (1)
Type =Mod(Rows(),2) = 1 into the formula box (2)
Click the Format… button (3)

The MOD function returns the "remainder" after a number is divided by another number.
The ROW() function return the row number of the current row

Therefore the function =Mod(Rows(),2) divides the row number by 2 and returns the remainder. If the remainder is 1 then the conditional formatting is applied.

Apply the Conditional Formatting Required


Click the Patterns tab (1)
Select the desired colour from the palette – light grey in this example (2)
Click OK to close the format dialog box (3)
Click OK to close the Conditional Formatting dialog box

Alternate Shading


The shading has been applied to each alternate row