MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Autofilter not showing all data

Posted by Mike on February 14, 2001 9:26 PM

I recently assisted a user with a excel filter problem. He had a large spreadsheet with Autofilter applied to all columns. His problem was the pulldown menu would not show all the items in the column, i.e., the number 832267 was in the column but did not show up in the pulldown list. I corrected the problem by sorting that column and following the sort everthing works fine. Is this a fluke or is this symptom caused by something else.

Posted by Mark W. on February 15, 2001 7:23 AM

No it's not a fluke -- that's a trematode worm :-).
Sorry, I could help myself.

The AutoFilter drop down list will only show 1000
unique entries. You can prove this for yourself
by creating a series begining with 1 in cell A2
and going to 1001. AutoFilter this list and you'll
never find the value 1001. When you sorted your
friend's list you merely shuffled 832267 far enough
up to "make the list".