Highlighting Every Other Row Using Conditional Formatting

Conditional Formatting (accessed via the Format menu) often makes spreadsheets seem to behave like 'magic' because when a user copies cells, the formatting can change automatically without the user knowing why.
That is, unless the user knows about conditional formatting. Anyway, here is a common but useful tip for highlighting every other row within a table. Excel 2007 can autoformat tables quite nicely, but if you're like me and still need to use older versions, this tip can come in handy.

The following formula, when used as a conditional formatting condition, will result in the odd numbered rows having the format you define (such as a gray background, bold font, different color font, etc.)
=MOD(ROW(),2)=1

To highlight even numbered rows:
=MOD(ROW(),2)=0

Below is an example of how I modified my Checkbook Register template using this approach:



Hint: after you add the conditional format condition to one cell, you can use the format painter to copy the format to other cells. The formatting is copied and pasted just like any other formatting.

3 comments:

Anonymous said...

It may be helpful to point out a specific area can be highlightedto apply this rule instead of doing it to an entire sheet. Thank you for posting this btw.

Bri said...

Very useful, thank you :)

Jenna77 said...

Thank you I used this rule... However I need to do highlighting of a different color on some of the rows that have the gray highlight and it will not let me because of the rule. Any suggestions?