Re: Excel Autofilter Capacity

Giganews Newsgroups
Subject: Re: Excel Autofilter Capacity
Posted by:  Pete_UK (pashur…
Date: 15 Jan 2007

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

Hope this helps.


Mike Christie wrote:

> 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



In response to

Excel Autofilter Capacity posted by Mike Christie on Mon, 15 Jan 2007