Excel Autofilter Capacity

[Mike Christie]

We appear to have hit a barrier on the number of records autofilter will
return.

From a spreadsheet of over 5000 rows, only about 999 can be returned using
autofilter.

Is this a real capacity limit others have encountered or just us?

Is there a solution within Excel?

Mike Christie

 

[Pete_UK]

Yes, the autofilter pull-down can only display 1000 different values.
There are ways round it, like filtering another column first (eg if
your values are text, put =LEFT(A1,1) in a spare column and copy down -
this will give you the first letter, so you can filter on this first -
eg selecting "C" - and then when you filter on A1 you will only have
values beginning with C displayed). A similar principle can be applied
with numbers, dividing them by a constant, or taking the integer value.
You can also use a Custom filter (greater than, not equal to, less than
etc).

Hope this helps.

Pete