Filter Blanks option unavailable

[Guest]

I have a master database that I build by pasting smaller databases into it
and now contains over 4,000+ rows. Every so often I'd like to check if there
are empty rows included by filtering and selecting the "(Blanks)" option at
the bottom of the pull-down filter menu on the specific column. I'm just
surprised that when my data exceeds 2,700+ the options (Blanks) and
(Non-blanks) are no longer displayed at the bottom of the pull-down menu even
if the (Blanks) and (Non-Blanks) option appear on the other columns'
pull-down list. Although I could use the customize option, I'm beginning to
think that there might be a problem with the file. In addition, sometimes
data at the bottom of the database don't appear in the pull-down menu.
Please help and hope someone can explain why this happens so I can prevent
this in the future.
Thanks!!!

 

[Debra Dalgleish]

An AutoFilter dropdown list will only show 1000 entries. If your first
blank cells are too far down in the list, they won't be displayed in the
dropdown list.

To AutoFilter for an item that doesn't appear in the dropdown list, you
can choose Custom from the dropdown list, and type the criteria. Also,
there are a couple of workarounds here:

http://www.contextures.com/xlautofilter02.html#Limits

 

[Guest]

Thank you very much Debra for replying! Your reply was very useful. You
mentioned the list will only display 1000 entries at a time. However, I have
an excel file of 500 rows and it also doesn't show the (Blanks) and
(Non-blanks) option and a file over 2000 rows that show the (Blanks) and
(Non-Blanks) option. How do I resolve this? Is this an excel flaw knowing I
can filter all the blanks using the (custom) option?

Hope you can still reply back. Thanks!

 

[Debra Dalgleish]

In the small file, are there blank cells in the column? Blanks and
Non-Blanks will only be listed in columns which contain blank cells in
the first 1000 unique items.

 

[Guest]

I get it now. Thanks very much for your help!

In the small file, are there blank cells in the column? Blanks and
Non-Blanks will only be listed in columns which contain blank cells in
the first 1000 unique items.