Excel filter works only partially
Hi
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
Bernard
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
Bernard
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Bernard
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.
Bernard
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.
Regards,
-Glenn