CP056: So which formulas you should care to learn?

21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”

1. Vijay says:

   January 5, 2011 at 10:03 am

   Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())

   So it takes away any chance of wrong referencing.

   Reply
2. Hui... says:

   January 5, 2011 at 11:44 am

   I like Daily Dose of Excel

   Reply
3. vimal says:

   January 5, 2011 at 12:41 pm

   I like it.

   Reply
4. Luke M says:

   January 5, 2011 at 1:42 pm

   Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
   =MOD(ROW(),2)=0

   Reply
5. Debbi says:

   January 5, 2011 at 5:42 pm

   rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.

   Reply
6. Paul S says:

   January 5, 2011 at 6:04 pm

   Just for clarification, is Ashish looking to filter by even or odd Characters or rows?

   Reply
7. Fred says:

   January 5, 2011 at 6:13 pm

   so many functions to learn!

   Reply
8. Istiyak says:

   January 5, 2011 at 7:00 pm

   Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......

   Reply
9. Arps says:

   January 6, 2011 at 4:33 am

   In case you want to delete instead of filter,

   IF your data is in Sheet1 column A
   Put this in Sheet2 column A and drag down
   =OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
   (This is to delete even rows)

   To delete odd rows :
   =OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)

   Reply
10. Pippa says:

    January 6, 2011 at 10:00 pm

    If your numbered cells did not correspond to rows, the answer would be even simpler:
    =MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.

    Reply
11. Matthew D. Healy says:

    January 12, 2011 at 12:28 am

    I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.

    Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.

    Extending this approach to more than 3 cases is left as an exercise for the reader.

    Reply
12. Paulo says:

    February 12, 2011 at 9:10 pm

    Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.

    Reply
13. Introduction to Excel Advanced Filters - What are they & How to use them | Chandoo.org - Learn Microsoft Excel Online says:

    October 10, 2011 at 8:56 am

    [...] How to Filter Even or Odd rows only [...]

    Reply
14. Makhan Butt says:

    April 26, 2012 at 6:12 am

    very different style Odd or Even Rows very easy way to visit this site

    http://www.handycss.com/tips/odd-or-even-rows/

    Reply
15. Terhile says:

    November 2, 2012 at 9:09 am

    Thanks for the tip, it worked like magic, saved having to delete row by row in my database.

    Reply
16. Youssef Rafiq says:

    October 30, 2013 at 10:51 am

    Thanks!

    Reply
17. majid says:

    February 13, 2015 at 9:09 am

    Thankssssssssssssssss

    Reply
18. Bhanu says:

    May 2, 2017 at 2:56 pm

    Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
    For Example-
    A 1 odd
    B 3 odd
    C 4 even
    D 6 even
    I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
    A 1 odd This is odd number
    B 3 odd This is odd number
    C 4 even
    D 6 even

    Reply
19. Adriana says:

    January 10, 2020 at 7:51 pm

    Hi! Could you please help me find a formula to filter by language?
    Thank you!

    Reply
20. avinash says:

    May 16, 2021 at 2:21 pm

    Chandoo SIR,

    I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW

    The fig is fresh - row 1
    Figs are fresh - row 2
    The Pomegranate is red - row 3
    Pomegranates are red - row 4

    Reply
21. Arshad Hussain Shah says:

    December 30, 2022 at 9:43 am

    =IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)

    Reply

Leave a Reply