Excel filter works only partially

Posted on 2014-08-27
Last Modified: 2014-08-27

One user here where I work has got a very strange problem with Excel in one particular sheet of one Excel: when she filters on a column which has only pure text in it and she wants to find the rows where the cells in that column are blanks, then the filter works for about the first 2/3 of all rows, and then the last 1/3 of the rows is ALWAYS shown regardless of the filter in that column is set to ! And strange is that the properly filtered rows have their row numbers correctly shown in blue font (which is the colour of filtered rows), and the last 1/3 of rows have their row numbers in black font, as has every row when no filter is active. It's as if the last third of rows have a special "status" which takes them out of the "filterable" rows. There are only about 130 rows, that's a very small sheet.

When we copied the whole sheet and pasted the values in a new sheet, the same filter on that column worked !

Anybody seen that one before ? Ideas about what it could be due to ?

Thanks for hints
Question by:bthouin
    LVL 31

    Accepted Solution

    Try removing the filter altogether, not just disable but remove, and re-applying. It may be that the last rows haven't been included in the filter.

    Rob H
    LVL 27

    Expert Comment

    by:Glenn Ray
    This is happening because there is/are blank rows in the data table and the auto-filter is limiting the range to the first blank row.  That means any data after that is being excluded from the filter.

    Rob is correct:  you need to remove Data Filters completely.  Then, highlight the entire range of data by pressing [Ctrl]+[Home], then press [Ctrl]+[Shift]+[End] to select all used cells.  THEN, turn on data filters again.  It should work.

    Ideally, you should have no blank rows at all in a data table and I suggest that you remove them.

    LVL 1

    Author Comment

    Hi Rob, hi Glenn

    Thanks for your speedy answers.

    Yes Rob, in the mean time that's what the users had the idea of doing and the filter ineed worked propoerly after remove and reapply.

    @Glenn: I didn't check, but I don't think there were/are any blank rows, just blank cells in the column being filtered

    Also, there were merged cells on the first row (header texts), I asked them to unmerge them because I remembered that merged cells can be quite a problem in many situations. But what we didn't do was to remove the filters and reapply them then. They did that on their own an hour after I'd been there and that's when it started to work, but I had already made my post..

    Thnaks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now