How to Alternate Row Colors in Excel With Conditional Formatting

If you're using Microsoft Excel 2000, 2003, or 2007, here's a way to use conditional formatting to alternate row colors. Alternating row colors comes in handy in lengthy or wide spreadsheets as it makes it easier to read and follow data.

Here is the formula.

=mod(row(),2)=1

There is at least one other formula I have come across and used, but this one is much better since it accomodates the adding and deleting of rows. The other formula I used required reformatting when changing the number of rows.

Here is how you would implement alternating row colors with this formula:

1) Click CTRL+A simultaneously to highlight the entire worksheet. Then on the menu bar at the top of the screen, click on Format.

2) When the menu drops down, click on Conditional Formatting (depending on your settings you might have to click on two downward pointing arrows to expand the offerings and view this option).

3) Change the condition to "Formula is" and then paste in the formula =mod(row(),2)=1


4) Click on the format button and then the Patterns tab and select a color from the palette and then click OK.

5) The Conditional Formatting window preview will change from "No Format Set" to a preview of your chosen color.

6) Click the OK button and you're done.

7) The sheet should now look something like this:



Note: In Excel 2003 you can opt to use auto formatting, but your color choice is limited; here's the link to the Microsoft tutorial for alternating row colors with auto formatting:

Shading alternate rows on an Excel worksheet

And in Excel 2007 you can use the "Format as Table" option. Here is the link to the Microsoft tutorial:

How to shade every other row in an Excel 2007 worksheet

You'll notice that each tutorial also mentions conditional formatting as a way to alternate row colors. I find conditional formatting to be the most powerful and flexible method for alternating row colors.

2 comments:

  1. By the way, if you want to alternate row colors but do it, say every 5th row, you could do something like this:

    =MOD(ROW(),5)=0

    ReplyDelete
  2. =MOD(ROW(),2)=0 If this formula don't work ( at least in my case didn't work as it is) try to change "," with ";" ==> =MOD(ROW();2)=0. This is what worked for me (W7, MO 2003)

    ReplyDelete

Note: Only a member of this blog may post a comment.