Forum Discussion
Excel Filter only works for 10000 unique values
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.
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.
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.
- 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.
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.
