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!


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.

Editor's Picks