Excel Filter only works for 10000 unique values

[AlexanderPacha]

We have a quite large spreadsheet with more than 10000 rows (each containing a different value). We like to use filters to filter in the spreadsheet, however, the filter dialog only allows filtering for the first 10000 unique values.

Please note, that I use numbers only for demonstrating the problem below, but we have more than 10000 different textual values in our actual spreadsheet.

To reproduce the bug, I've created an extremely simple spreadsheet that only contains a single column with the numbers 1 to 11000 in a single column. Then applied a filter and search for different values, note that the value 10002 exists (in A10003):

Filtering for 10000 works as expected: 

Filtering for 10001 (the 10000th unique value) works as expected: 

Filtering for 10002 does not work anymore and there is no way to fix this:

Please fix this serious bug as soon as possible or let me know how you can extend the number of elements that can be filtered by.

Environment information:

OS: MacOS

Product: Excel for Mac

Version: 16.65

---

Excel in Office 365 does not seem to have this problem:

[#2]

@AlexanderPacha That is not bug. That is Excel limitation and it is documented. You can use number filters option to define your filtering criteria. See the below screenshot.

[#3]

@AlexanderPacha The filter dropdown is indeed limited to show 10000 items. That's the way it is.

And the Search field in Excel for the Mac behaves differently than in the PC version. Tested it and indeed, you can not search for and item that is not in the 10,000 items in the the dropdown. 

But you can use the Filter areas where you can select "Equals", "Greater than" etc. and that works. I created a list of numbers up to 100,000 and filtered two rows outside the first 10,000. See picture.

From your screenshots I note that you are Filtering with the "And" parameter selected. Obviously, no number will be both equal to 10,001 and 10,002. You'll need to select "Or" or use the "Between" option.

[#4]

1.) Where is that behavior documented? And why is it different between the offline version and the online version?
2.) The number filters that you mention are not working for us, because we don't have numbers. We have textual values. I just simplified the spreadsheet to demonstrate the problem.

[#5]

Same here. We have textual values, not numbers. I just used numbers in the example to demonstrate the problem. We have textual values and more than 10000 unique values. Is this limitation configurable somewhere?

And no, I'm NOT filtering with the and parameter selected. When you type something in the search-box, it automatically updates the value above and when I entered 10002, it failed to find anything, thus kept the value 10001 in the equal box. If you want, I can record a screencast to show this behavior.

[#6 · Accepted solution]

@AlexanderPacha Google for "Excel limitations" and you'll find it. You can't reconfigure it. And the Filter option also work with texts. It has options like "Begins with", "Contains" etc.

[#7]

The funny thing is that Excel on my iPad Pro does not have that limitation. It is goofy at best that this quick, easy and commonly used function is different on different platforms.