Excel Filtering Limit of 10,000 Rows (2024)

I am using Excel 2016 and would like a way to get past the filtering limit, even in a table, of only showing the first 10,000 rows. How do people work with larger ranges? 10,000 rows isn't really that large, relatively speaking.

Thank you!

Gary

AFAIK, there is no such limit on the number of rows XL 2016 can filter. It is a limit for how many can be displayed in dropdown...perhaps that's what you mean?

If you need help on the latter, easiest is to build an auxiliary column showing first letter of primary column.
http://www.mrexcel.com/forum/excel-questions/597833-filtering-more-than-10-000-lines.html

10,000 is just filtering drop-down list limit and not filtering limit.

You can use "Number Filters" or "Text Filters" to use logic to filter those columns with more than 10,000 unique values.

Edit: Luke beat me to it

Chihiro said:

See Also

How can you analyze 1M+ rows in Excel? - Earn & ExcelAre there data limits in a CSV file?How can I store more than 1 million rows in excel sheet.one million rows in a google sheet.

Luke beat me to it

Click to expand...

Cool Hand Luke .... always quick on the draw ...

Although he sounds like a gunslinger, I think he might be more 20th Century ....

Good thinking- that might work! I have a list of check numbers that I keep adding to, actually will be tens of thousands, and I like to be able to view all of them in the filter. I'm surprised that Excel has this limit. I'll give it a try.

Thanks guys!

Gary

garylundblad said:

Good thinking- that might work! I have a list of check numbers that I keep adding to, actually will be tens of thousands, and I like to be able to view all of them in the filter. I'm surprised that Excel has this limit. I'll give it a try.

Thanks guys!

Gary

Click to expand...

I doesn't think that looping that serail in filter dropdown show is good idea instead of using pivot or something else.

The limit is self explainable, viewing 10000 is too much...

@Deepak I disagree. I can't think of any good reason why MS shouldn't let us see all items. In fact, I think MS have screwed up the code in these search boxes because they take ages to load.

@garylundblad I'm working on an add-in that you can use on Tables OR PivotTables OR DV lists that let you see and filter items far more easily, not to mention invert selections quickly, or filter a Pivot based on an external range. Sneak peak at http://dailydoseofexcel.com/archive...-pivottables-with-vba-deselect-slicers-first/ . I'm about a week or two away from finishing it, so you can pm me here if you are interested in hearing more.

You might also find my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ handy.

jeffreyweir said:

@Deepak I think MS have screwed up the code in these search boxes because they take ages to load.

Click to expand...

That's why i don't suggest the large DB to check there by
dropdown.

David Evans said:

Cool Hand Luke .... always quick on the draw ...

Although he sounds like a gunslinger, I think he might be more 20th Century ....

Click to expand...

*groan*

As a reminder Excel was very not designed like a database software,​

the reason why when Excel 2007 version came I very did not understand why the rows grew up to a million​

as many Excel features are still size limited …​

​

In any sort of dropdown control. Be it Web, Excel or any other application. It's never good idea to have more than 100 or so items to choose from (personally I don't like more than 10~15 items in dropdown menu).
Unless there's other filtering mechanism in place to limit selection(s).

In Excel, you can always use Text Filters and Starts with or some other filter criteria to limit results returned.

Alternately, you can set up helper column (or dimension table) that will group unique names into smaller subset. Use slicer to select subset, then use dropdown to pick from narrowed down list.

garylundblad said:

To revisit this old post, I'm still surprised that Microsoft has not expanded the filter display beyond 10,000 unique values. I have a large list of users that have been enabled for a particular application, over 25,000 unique names, and not being able to find a specific one seems like a significant challenge. What seems odd is that I do see names that are beyond the first 10,000 unique names, so I'm a little puzzled by what is supposed to be displayed here. I took the entire list, removed duplicates, separated the first 10,000 from the rest, and then found a name in the second set of 10,000 that wasn't in the first set. Then I looked for this name in the filter display, and I found it. Does anyone understand how Excel determines which values to display? Thank you!

Click to expand...

Hi,
Most of the geniuses are already commented in the thread. But I have one doubt, @garylundblad if you are searching for a particular name then what is the necessity of displaying all name in the filter or separate the list. You can directly type the name in the search bar of filter option.