Filter limits in excel

[mtevensorrison]

Hi there

My accountant is having an issue regarding filter limits in excel 2003.

We have a spreadsheet with around 9,500 rows of data, he is attempting to apply a filter to this data.

It seems that the filter will only filter through 1,400 or so jobs at a time.

can someone please let me know if this is the limit on these filters, or if there is a way around this problem?

Thanks in advance

 

[SkipVought]

You can have tend os thousands of rows of data in an AutoFilter.

Do you have EMPTY ROWS in your table? That will hose MANY Excel features that relate to tables.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

oops, meant to say tens of thousands.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[mtevensorrison]

We checked through the spreadsheet and there are no empty rows at all!

ive seen a few articles on the web stating that excel is only capable of filtering 1400 jobs at a time.

Have you tried this?

Thanks for the reply by the way

 

[xlbo]

What Skip is referring to is the number of records in the data set. A data set of many thousands of records can be filtered

In terms of what is available in the drop down list - 1400 (it used to be 1000) is the max number of individual items that can be displayed in the drop down list

If you need to filter for an item that is not shown in the list, you may use the "Custom" option and enter the value in there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"...excel is only capable of filtering 1400 jobs at a time.."

Did you mean list items in the drop down?

Yes, thre is a limitation, but having thousands of dropdown items to slog thru is not a good design approch.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[combo]

You could add a helper column with group identifier, as first letter of name (can be done with formula), etc. If the database will be filtered in this column first, the autofilter list (<=1000 items) could be large enough to display all subitems.

combo

 

[mtevensorrison]

Thanks for the help guys

This clears things up a bit

Jst wanted clarification.

Thanks again