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:

20150302Image01

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:

20150302Image02

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…

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

  1. yeah. no problem unless you want to view #NA only. you can’t select that item because it shows at the bottom of the filter list.

  2. “Email | 2015/06/19 at 5:34 am | Reply
    yeah. no problem unless you want to view #NA only. you can’t select that item because it shows at the bottom of the filter list.”

    Not true, you can use the search bar in the filter drop down. Search “n/a” and it’ll bring it up. Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s