Software

A clever trick for filtering odd and even rows in Excel

If you want to see only odd or even rows in an Excel worksheet, use this quick filtering trick.

Excel's filtering feature is powerful and the new table-filtering feature offers even more. However, there's one thing neither can do: neither can filter a data set by odd or even rows. Fortunately, it's easy to do. Simply add an extra column to the data set as follows and filter by the results:

  1. In an adjacent column, use the =ISEVEN() or =ISODD() function, combined with a ROW() function that references any cell in that row.
  2. Double-click the cell's fill handle to copy the formula to the remaining range.

Next, enable a simple filter by selecting any cell in the data range and clicking Filter in the Sort & Filter group on the Data tab.

Click the new column's filter dropdown and choose False or True. False will display odd rows; True will display even rows.

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.

14 comments
textier
textier

i want to refer to a1, a4, a7, a10 and so on (3 steps). How can a do this?

jamesmorrill
jamesmorrill

This was a great method and a quick solution to my simple problem.  Thank you for sharing!

sanjyn
sanjyn

^_^ thanks a lot, this just made my work easier.

Gordon Or-8
Gordon Or-8

I have been using =MOD(row(cellreference),2) to show 1's and 0's for a long time now.... (I sum every other row in a column by using a MOD() formula too) :-) @Why? because we have forecasted figures on even rows and actual figures on odd rows down the column and I want to sum them both individually.

Proportal
Proportal

OK - its nice to see the use of these functions, but when would you possibly want to filter by odd or even rows? I cannot think of a practical example of when this would be of use. If you know of an actual example - please share.

jbenton
jbenton

just enter 1 in D3, 0 in D4, select both, double-click fill handle and filter (can be extended to do every 3rd row, every alternate pair etc - not that i can imagine why you'd want to...)

wes.christiano
wes.christiano

Sorry, I must have missed something but why is row 6 false and row 7 true?

Editor's Picks