Excel Unique Records Limit



Hi all,

Not so much of an error but some say it is...

I am using Excel 2000 at work. We have a spreadsheet with autofiltering on
the columns. Lately I have noticed that excel omits to include certain rows
after a point within the filter when you try to use it. Also the Find
function won't find after a certain row either. I have read about this kind
of thing before however I cannot remember....so...

....can anybody remember how many unique records Excel 2000 can display in a
autofilter or a search/find (CTRL+F). I think they may be the same number.

Also, as each new version tends to increase these sorts of values, what is
it in Excel 2002 or 2003?
Work will let me upgrade but only to probably 2002 without a business case
or to Office 2003 with a new PC however as I am running Win2000 there might
be compatability issues.

As I said I am using Win 2000 pro with Office 2000 but others in my office
are only on Win NT4 with Office 97!!!

Thanks in advance.

PS... MS Access is out of the question as licences are hard to come by and
the IT section won't support the dbases people build either.




The limit in XL 2003 and earlier is 1000 unique values displayed in
the filter drop-down - I'm not aware of any limit in Find & Replace,
as long as you highlight the data range beforehand. You can make use
of Custom... to get values that are not displayed (eg Greater than
10000, or Begins with M), or you can introduce a helper column (eg for
text values insert a formula =LEFT(A1,1), then you can filter on this
by first letter and then filter your other column.

Hope this helps.



Thanks Pete. Shame there is a limit but your workarounds will suit...


Thanks Pete. Shame there is a limit but you workarounds will suit fine.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question