The “Cannot filter more than 10000 rows” error in Excel is misleading…

A recent help desk case took me longer to resolve than I expected, mostly because what the error message said and what I thought it was saying were two different things. A customer called about a spreadsheet that was being generated by SSRS, and they were thinking they weren’t seeing all the data due to Excel showing an error saying it couldn’t filter more than 10000 rows. I’ll admit I was a bit concerned, because this spreadsheet would *always* have more than 10000 rows, so what’s the problem?

I found my answer here:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/cannot-filter-more-than-10000-rows/8ab9d300-d6ec-4c3b-90a3-98002862e281

That message refers not to the number of rows displayed in the spreadsheet, but the number of items that appear in the column filter dropdown. For instance, you’ll see all 28000 rows in the spreadsheet, but you see that 10000 rows message when you click on the dropdown for the filter:

That 10000 refer to the number of entries you would see under the (Select All) line of the checkboxes. It simply means that you would not be able to see all 28000 items in that filter list, but you’d still see all the rows of data in the spreadsheet.

A better example would be the dropdown for one of the date/time fields:

If the filter list just had YYYY/MM/DD, you would have maybe 1000 unique dates over the last three years. But since it goes down to HH:MM:SS, close to every one of the 28000 rows has a unique date/time combination. The filter can’t show all those unique values, so you get the error message. You could still use the filtering to find all 2014 items for January through May, but you couldn’t drill down to also refine the filter to the exact times because you couldn’t show and expand all those combinations.

Basically… all the data is there. Nothing to worry about…