how to: create alternate row-shading in Excel

Why would I want to do this?

I think it’s easier to scan along lines if you colour alternate rows, particularly when you print out your sheets. Gridlines are somehow a little disorienting. You may not find this, and you may prefer the big black borders approach. But if you don’t, read on.

What’s wrong with the way I do it?

Well, if you’re doing it by hand, you’re going to run into two problems.

  • Depending on how many rows you want to shade, it can take a long time.
  • When you delete or add cells, you can throw off the whole thing, and have to start again

These are sufficiently irritating that it’s worth sharing this approach.

More to the point, I wanted to test out some new screen capture software (

Select all the cells you want to change. I usually select all. On the Format menu, click Conditional Formatting. Under Condition 1, select Formula Is. In the data entry box, type =MOD(ROW(),2)=1. Click the Format button. In the Format Cells dialog box, click the Patterns tab. Select a light-blue color, and then click OK. In the Conditional Formatting dialog box, click OK.


Alternate row shading in Excel