Auto filter does not pick up all values in a column

[Guest]

Hi, when I apply an auto filter to a range and I try to filter by a certain
column, the filter does not show all the values in that column. How can I fix
this?

Tx

 

[David Biddulph]

Did you select the entire range before applying the filter? If there are
gaps in the data, Excel might guess the extent of the range differently from
what you intended.

The other possible problem if you have too many different values in the
column. The drop-down list will show only 1000 unique values, so you may
need to split out, for example, the first letter for an initial filter. I
do this with a helper column =LEFT(A1) to give an A-Z list, and then pick
the individual name I want after that.
http://www.contextures.com/xlautofilter02.html#Limits

 

[Guest]

Excel 2003 and earlier versions limit the Auto Filter list to the first 1000
entries. The filter still works on all rows, but only the first 1000 will be
selectable from the drop-down list. Excel 2007 increases this limit to 10000
entries.

There really isn't a way to increase this limit, but there may be some ways
around it. You can use the "Custom" field on the filter, then type in the
value you want to filter by. Or, depending on your data, you may be able to
filter by a different column first, thus reducing the number of entries to
choose from in your desired column.

HTH,
Elkar

 

[Guest]

Thanks, this solved my problem. There were blank cells and just like you
said, it looked like Excel assumed the blanks might have been the end of the
data I wanted to filter.

Thanks a lot for your help (Thanks to Elkar too!)

Ciao