Excel 2010 - How Can I filter more than 10000 lines

Hi,

I have a spreadsheet with 15000 lines. When I add a filter on my header column and try to filter my column, excel states that only the first 10000 lines are only filtered.

How can I filter my entire 15000 lines?

Any help will be grateful.

Did the book originally come from Excel 2003?

Hi Twista,

Did you get hte reply?

I am facing the same problem.

- Nital

This is weird.. I found this article http://support.microsoft.com/kb/295971 from microsoft that supposedly applies to 'Excel 2010'. It mentions the 10,000 row limit.

However I find that to not be the case at all! I just tested it on 20,000 rows with unique values (just 1-25000) and filtered for a neighbouring (y applied to 1-20000, and n applied to 20001-25000), and it happily hides rows 20001 to 25000 if I filter for Y only.

.. and I just tested it for 80,000 rows and it still works.

The limitation is not on the number of lines Excel will filter through but on how many unique items it is placing in the dropdown filter.

For example, if you have the numbers 1-20,000 in a column and add a filter to that column ... when you try to use the filter you will get the message telling you that it could only list the first 10,000 unique items. But if you use the custom filter and select "Equals..." and then pick 15000 (which will not be in the list) it will filter through all 20,000 line items and display the line with 15,000 in it.
And if you still aren't convinced then change 2 or 3 other lines to 15,000 and then try the search again.

This limitation shouldn't be a big deal because lets face it, if I have a list of over 10,000 unique entry items (names, numbers, alphanumeric, whatever) I'm NEVER going to scroll through the dropdown list to find the one I what ... I'm going to use the number or text filter with the "Equals..." or "Begins With..." option - and type it in!

By now the thread is nearly FIVE (5! ) years old and I ran into the exact problem. That's what I like about forums like this. There's always somebody that already ran into the same problem and fixed it.

Thanks hard2figure, by simply adding some columns to reduce the number of lines, I got the dropdown filter to be working like I wanted to.

:-)

Originally Posted by Bainiet

By now the thread is nearly FIVE (5! ) years old and I ran into the exact problem. That's what I like about forums like this. There's always somebody that already ran into the same problem and fixed it.

Thanks hard2figure, by simply adding some columns to reduce the number of lines, I got the dropdown filter to be working like I wanted to.

:-)

hi Bainiet,
you're right.. and I hope it works for me as well!
what do you mean by "simply adding some columns to reduce the number of lines" I don't get how it helps ..!

hello please need a help i need to edit a range in name manager for a defined name range and i can't it is a pale i can't even delete it any suggestion please