Microsoft

Use conditional formatting to format even and odd rows

Formatting every other row is easily accomplished using conditional formatting. Fortunately, conditional formatting can handle something more specific, formatting every even or odd row.
Many users like to shade every other row to make sheets more readable, especially when there's lots of data. Sometimes restrictions can complicate things, or at least you might think so initially. For instance, you might think that shading only odd or even rows a harder task than shading every other row, but you'd be wrong. Using conditional formatting, formatting only odd or even rows is simple:
  • To format even rows only, use the conditional formula =EVEN(ROW())=ROW().
  • To format odd rows only, use the conditional formula = ODD(ROW())=ROW().

Now, let's work through a quick example:

  1. Select the rows you want to format. To select the entire sheet, click the Sheet Selector (the gray cell that intersects the row and column headers).
  2. Click the Home tab.
  3. Click the Conditional Formatting dropdown in the Styles group and choose New Rule.
  4. From the Select A Rule Type list, choose Use A Formula To Determine Which Cells To Format.
  5. In the Format Values Where This Formula Is True field, enter =EVEN(ROW())=ROW().
  6. Click Format.
  7. Specify any of the available formats. For instance, to shade all even rows red, click the Fill tab, click Red, and click OK twice.

Notice that the even rows are now red. To shade odd rows, repeat the above steps. In step 4, enter the formula = ODD(ROW())=ROW(). In step 6, choose a contrasting color, such as green. This technique isn't just for shading, it's for formatting in general.

Okay, that's hideous, but it makes the point well—with little effort, you can format all even or odd rows. Please don't ever do this to a real sheet unless you're pranking someone!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

22 comments
Marshwiggle
Marshwiggle

1) From the Format menu, click Conditional Formatting ... 2) Use the drop-down list on the left to select Formula Is. 3) In the text box on the right, enter one of Susan's formulas (or an alternate from the Comments -- ISODD and ISEVEN may not be available). 4) Click the Format button and select a color on the Patterns tab; click OK, then click OK again.

cdanko
cdanko

Great, thanks for the tip

Stuck-In-Kentuck
Stuck-In-Kentuck

Thanks - Good tip. Works with columns, too: =ODD(COLUMN())=COLUMN() and/or =EVEN(COLUMN())=COLUMN()

amg8589
amg8589

Just asking - why not use one of the many pre-set table formats that will do this for you? Am I missing something? Why work this hard?

jalbertini
jalbertini

Conditional Formatting is grayed out in Excel 2007. What is wrong?

diana.ibay
diana.ibay

I've used this for a long time with Excel 2003: =MOD(ROW(),2)=0. Try it.

ebsfrmr
ebsfrmr

Great tip. Thanks very much to you and all the others who posted alternate formulas.

RogerAIA
RogerAIA

doesn't seem to work in excel 2003, is this only for 2007 or 2010?

ymorua
ymorua

I followed your instructions and it doesn't work. I received a message saying there was an error in the formula. I cut & paste the formula right out of your article. What's up?

jbenton
jbenton

to identify odd rows I use =MOD(ROW(),2) this can then be easily extended to identify every 3rd row: =MOD(ROW(),3)=0 or every 4th row starting at row 3: =MOD(ROW(),4)=3

jbenton
jbenton

to achieve the 'gingham look' of shaded alternate rows and columns with darker crossovers: set the first condition's formula to be: =mod(row(),2)*mod(column(),2) and the format to dark shading set the next condition's formula to be: =mod(row(),2)+mod(column(),2) and the format to light shading

jbenton
jbenton

1. autoformatting is limited 2. if you remove or add a row your effect is ruined and you have to reformat it 3. makes your spreadsheet bigger (autoformatting a full sheet (in Excel 2003) led to a filesize of over 35MB; using the conditional route it was only 14KB) 4. your "last cell" will be the last one formatted if the last cell with data is before it 5. it's more fun, and impressive when you have formatting appear only on the rows up to where you have data (add "*counta(rowref)" to your condition to make this happen) 6. any more?

gechurch
gechurch

For anything requiring a bit of formatting it is likely to be easier to apply this than to modify a template. It's also a lot easier to do this on existing tables than to start again from a template. I for one didn't know this was possible in Excel, so thank the author for the tip. I've got a contact list (thankfully a fairly short one) I had previously been applying background fill to manually. I just changed it to use this tip - took all of about 60 seconds. I couldn't have even found a similar template in that time.

TobiF
TobiF

The tools for conditional formatting were changed between 2003 and 2007. So my guess is that you're working with a document in compatibility mode (i.e. xls, rather than xlsx).

al
al

I've tried all the different variations and have come to my personal favorite. Something so simple that makes a world of difference when working with large sheets. Thanks to all!

charlac
charlac

The formulas work fine in 2003. In the conditional formatting box, for Condition 1, select "Formula Is" in the dropdown list, then enter the formula into the box to its right. Remember to select the Patterns tab in the formatting options if you are trying to apply a color to the rows (its easy to mistakenly apply the formatting to the font, as that is the default tab).

ladychevelle69
ladychevelle69

To format odd rows only, use the conditional formula = ODD(ROW())=ROW(). re move the space and it should work. worked for me and it worked in off xp pro.

Scruitol
Scruitol

Simpler formula and far more flexible...

RC51
RC51

i just came across this thread. How would one limit the shading on a row? For example, shading all odd rows in columns A through H.

TobiF
TobiF

Does this work to create a checkered format? =iseven(row())=isodd(column()) EditAdd: I just noted that iseven and isodd need the "analysis toolpack", at least in 2003. So a more generic approach would be: =mod(row(),2)mod(column(),2)

jbenton
jbenton

by only formatting those columns!

Editor's Picks