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.

13 comments
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?

aretina
aretina

@Proportal If you want to anylyse the stability of an estimator from large data sample containing a time seria, it is more practical to take every second one, to get 2 samples, as taking the first&last halves of the time seria might have been impacted by time.

ssharkins
ssharkins

Ack! My mistake -- some mornings, I just shouldn't get up. The correct function is =ISEVEN(ROW(cellreference)) or =ISODD(ROW(cellreference)). I'll fix this later today -- I apologize for the mistake and the confusion. Thank you so much for pointing this out.

ssharkins
ssharkins

You know, a clever trick is only clever if it works! Thanks Wes for pointing out the problem -- I think it's fixed now. I apologize to everyone for the error and I hope it didn't cause too much confusion. Video of my public flogging will be available via youtube later today.

ssharkins
ssharkins

Well, when you're wrong, you're wrong! Admitting my mistakes here is easy. Most everyone is kind about it, which makes me work even harder to avoid them! :)

Marshwiggle
Marshwiggle

... not laughing at your humiliation, but enjoying your humility. It's pretty rare in the tech world.

ssharkins
ssharkins

You're laughing at my public humiliation and pain!!!!!!! >> Seriously... I am so lucky to have such kind and patient readers. :)

Editor's Picks