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