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.

enter image description here

Why on the earth is this happening? And how to fix it?


I just found something else fishy:

enter image description here 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)

You can download file here

  • 1
    May I take a look at this file in order to help you with this issue? Please erase all non-related data, share it and provide us with a link. What software and what version are you using? – Mike Mar 8 '15 at 16:32
  • @Mike: I've added the link at the end. Check it out. – claws Mar 8 '15 at 16:39
  • Why would any one want to close this question? What guidelines is it not meeting? Day be day Superuser is becoming more restrictive! – claws Mar 8 '15 at 16:39

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.

enter image description here

Now it will works like a charm. :)

  • @claws You are highly welcome! – Mike Mar 8 '15 at 17:42

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.


  • I've dowvoted you for 2 reasons: 1. This question is 5 years old, with an upvoted answer that's been accepted. Your time would be better spent on recently asked questions without good solutions. 2. Your solution won't solve the issue - in the accepted answer you'll see the problem is blank cells/rows that break up the data, which prevents the "Filter" button from finding all the data. These blank cells will prevent Convert to Table from working properly, so you'll have the same problem (albeit with a bit more visibility into where excel thinks the data is). – sandyscott Jul 5 '20 at 15:47
  • Based on the screenshots attached by the author and his posts, blank cells might not be the only problem that he encounters. I'd prefer knowing his opinion, whether the instructions of the most upvoted answer helped him. Apart from that, I wasn't able to find the right thread related to the group of problems caused by filtering in Excel and the date of creation doesn't tell much in this case. The only relevant thread on Microsoft forum is closed: answers.microsoft.com/en-us/msoffice/forum/all/… – JacobTheKnitter Jul 6 '20 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.