• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Filtering Limit of 10,000 Rows

G

garylundblad

Member
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
 
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.

upload_2016-3-11_15-7-21.png

Edit: Luke beat me to it
 
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.
 
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!
 
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.

68532
 
You must log in or register to reply here.
Back
Top