Shade alternate rows using conditional formatting in Excel 2007

The rows are numbered 1, 2, 3, 4 and so on, which is odd number followed by even number. You create the rule such that when the row number is even, you apply the shade. If the row number is odd then do not shade. You use the MOD and ROW functions to return the odd and even numbers.

ROW function - ROW(reference) is the syntax. If the reference part is empty then it assumes the reference of the cell in which the function appears. For example, if =ROW() is entered in cell B7 then 7 will be displayed.
MOD function - MOD(number,divisor) is the syntax. Supply ROW() as the number parameter and 2 as the divisor.

You nest the ROW function inside the MOD function. The final formula is = MOD(ROW(), 2) = 0. This formula divides the row number by 2.

• If the remainder is 0, the function returns true.
• If the remainder is 1, the function returns false.

Perform the following steps to shade alternate rows:

1. Click View tab, in the Show/Hide group, click to remove the checkmark besides Gridlines. This removes the gridlines.
2. Select the cells for which you need to apply the alternate rows shade. (Example: select cells A1 to M22).
3. On the Home tab, in the Styles group, click Conditional Formatting and click New Rule...
4. In the New Formatting Rule dialog box, do the following:

• In the Select a Rule Type: box, click Use a formula to determine which cells to format.
• In the Format values where this formula is true box, enter =MOD(ROW(),2)=0
• Click Format... button to open Format Cells dialog box.
• Click Fill tab. Select a background color. You can also select a pattern or gradient. Click OK to close the Format Cells dialog box.
• Click OK to close the New Formatting Rule dialog box