Alternate row shading

Alternate row shading is quite useful if you’ve got particularly wide data (lots of columns) and when readers need to scan across individual lines. Bug listings, contact lists etc. I usually use a light yellow shading to help with this, alternating with the default white. In the more recent versions of Excel can be achieved by formatting your data as a table (from the Home tab). But if that's not suitable, or if you're on an older version of Excel, you may still want th feature.

To generate the alternate row shading, I used to insert a new column at A, fill it alternately with 1s and 2s, filter the 1s, shade the filtered rows, then delete column A. The problem with this method is that if you insert a new row some way down, the shading goes a bit awry, leaving two consecutive shaded or unshaded rows.

The better way to do this is to use conditional formatting, and base the conditional format on the row number, or more specifically, whether the row number is odd or even. If the cells you want to highlight start in row 2, highlight rows 2 through n (n being the bottom of your list). Select Conditional Formatting from the Home ribbon. In the first condition, select Formula from the dropdown and type the following into the condition area:

=mod(row(A2),2)=0

Then choose your desired shading through the Format button.

Because the first cell of the range highlighted is A2, it will apply this conditional format to A2, but change the reference accordingly for all of the other cells in the range. The formula itself takes the row number (for row 2, this is 2), divides it by 2 and calculates the remainder (in this case 0). If the remainder is 0 (i.e. the row is even), it will shade it; otherwise, it won’t.

• Tweet