Shading rows automatically

I’ve seen spreadsheets with shading on every second row. Is there an easy way to do this, other than manually formatting the rows?

By Neale Blackwood

Conditional Formatting is the easy way to achieve this effect. Using alternate shading makes reading wide spreadsheets easier. First select the range you want the effect to apply to. Then click the Format menu, select Conditional Formatting. In the drop down box on the left, select Formula Is. Then enter the following formula in the box on the right.

=MOD(ROW(),2)=1

Then click the Format button, select the Patterns tab and select a colour shading to apply to the rows. Click OK and then click OK again. The shading should then be applied. When you insert or delete rows the shading will automatically change.

The MOD function above returns the remainder of the first element divided by the second element. Hence, it divides the row number by two.

When the result is one – for every odd-numbered row – the shading is applied. Even-numbered rows will have no shading applied. To change the shading to even-numbered rows in the formula above, change the 1 to a 0.


Extras

Excel 2007 introduced a built-in table format which does the alternate row shading. See the Home ribbon tab, Style section, button for Format as Table. If you don’t want to use the Table format see below.

To use the above formula in Excel 2007+ click the Conditional formatting button, click New Rule, Click Use a formula to determine which cells to format. Enter the above formula in the box and click the Format button, click Patterns tab and select a colour click OK twice.

Excel 2007+ has no limit on the number of conditional formats you can apply. Previous versions were limited to three.