Why does AutoFilter not show all the data in the pull down?

[Guest]

In an Excel spreadsheet with 12,000 lines, and autofilter on, all the
possible selections are not displayed in the filter pull down. I can use
custom filter in the pull down, but is more time consuming. I'd rather
scroll in the pull down. Is there a limit on the amount of lines shown in
the pull down? Or am I missing something?

 

[Guest]

Go to Excel help and enter limits.
Excel has dozens of limits. I was surprised not to find a limit on
Autofilter which is 1000. Most irritating is only 1024 characters can
display in a cell and only 256 characters can be copied to another worksheet.
Honorable mention of shame goes to the limit of 65536 rows when QuantroPro
has 1000000 limit. There must be someone in charge of Excel development that
enjoyed using punch cards from the 60s/70s. It is time to bring Excel into
the 21st century.

When you click an AutoFilter arrow, a list is displayed of the items in the
column, in alphabetical or numeric order, up to a total of 1,000 items. In
the list, click the item you wish to filter on. The rows containing that item
will be displayed in the worksheet. All other rows will be temporarily
hidden.

 

[Roger Govier]

Hi

Debra Dalgleish has a workaround for this on her site
http://www.contextures.com/xlautofilter02.html#Limits

With regard to all the other limits which can be PITA, then there are
lots of changes planned for Office 12 when it comes out next year.
Take a look here
http://blogs.msdn.com/excel/default.aspx

Regards

Roger Govier

 

[Dave Peterson]

You can display much more than 1024 characters in a cell by including alt-enter
(to force a new line) every 80-100 characters.

And I can copy a cell with more than 256 characters to a different worksheet.
(There is a problem that does occur if I copy an existing sheet to a new
worksheet and there are cells with constants longer than 256 characters--but
that's easily overcome just by copying the cells and pasting them.)

You may be interested in looking at some of the limits in the upcoming version
of excel:

http://blogs.msdn.com/excel/default.aspx