Shading Alternative Rows in Excel
Using a combination of the ROW and MOD functions and Conditional Formatting, you can easily shade alternative rows in Excel, making a list of data easier to read.
Step 1: Highlight the range that you wish to shade - this includes ALL rows, not just the rows to be shaded:
Step 2: Select Format > Conditional Formatting from the menu to display the Conditional Formatting dialog box.
Step 3: Select Formula Is from the drop down on the left and type the following formula into the box on the right:
=MOD(ROW(),2) = 1
=ROW() will return the row number of the current row (i.e. if you type =ROW() into a cell in row 20, the result is 20
The MOD function returns the remainder after a number is divided by a divisor.
So the formula is dividing the current row number by 2 and if the remainder is 1, the formatting (see next step) is applied.
Step 4: Click the Format… button, click the Patterns tab, select the colour that you wish to use for the shading.
Step 5: Click OK to complete the process:
