Why are filters in an excel sheet showing unmatched rows?
I've an excel sheet with around 1000+ rows. I've applied filter on the first column with value "GS1 - Indian History". It is just supposed to show 16 rows, but it is showing rows containing other values (including blanks) for this column.
I've tried clearing formatting & reformatting entire column. I've changed the format of the cell contents from "General" to "Text". Still it isn't working.
Why on the earth is this happening? And how to fix it?
EDIT:
I just found something else fishy:
There are lot more records than 512. Any by any chance is this 512 any limit imposed? (as 512 fits in the series of 512, 1024, 2048 etc)
3 Answers
Issue caused by blank (empty) cells at the column you are filtering. The actual problem occurred upon filter creation, while you made filter when you had first cell selected instead of whole column.
How to fix it:
- Click at the first cell and press Filter again to delete current filter;
- Then click at the first column and press Filter to create a new one.
Now it will works like a charm. :)
1I had the same problem you had mentioned when you were having trouble with the filter in excel, when you were trying to filter just "GS1 - Indian History".
I was able to fix the problem in my case. It turns out I had somehow converted the spreadsheet to a table, whatever that means. But I had done it while I was still making the spreadsheet. I later added more rows, and those weren't being included in the filter.
I somehow came across the option to "convert table to range" while one of the earlier cells was highlighted. That did the trick; the filter started working as intended, even with blank cells.
Right click on any cell within the blue range -> Table -> Convert to Range -> Yes
Convert an Excel table to a range of data
This way, the typical characteristic of the table was restored, as well as filtering within the whole table or single column only.
Source:
2
