Office 365 Advent Calendar – 05 Exporting large SharePoint Online List to Excel

Scenario

You have a list in SharePoint Online with more than 5,000 items, potentially much more than that. And you now plan to export all that data into Excel, for example for further analysis. The question now is – how do you get all that data out of SharePoint and into Excel? When you create a view to show all items, you’ll get a “The number of items in this list exceeds the list view threshold” error, and you’re tempted to think that you can’t do anything from here.

The good news is: you can achieve this with out-of-the-box functionality, no coding or third party tools are required. While SharePoint List Views will not work if they retrieve more than 5,000 items, you can still use the ‘Export to Excel’ command from the Ribbon in such a View! The View itself can’t retrieve the items and shows you the error, but Excel (magically) works differently and allows you to retrieve (nearly) all items.

It should be noted, however, that there is another limit in Excel – you can’t export more than 52,000 rows from SharePoint into Excel, the latter will not allow you to import any rows above that number.

Steps

Here’s my demo list with a few relevant columns as well as more than 12,000 items inside:

Side note: I was actually able to display all items even though I didn’t add any indices to my list and retrieved all 12,000+ items in the view above, which I didn’t expect to work. I expected to see an error similar to this one:

However, when I tried to filter, I got the following expected error:

Coming back to the export: I created a new view and selected all the columns that I wanted to export.

I didn’t apply any filters:

I saved the View and opened it. The known “Threshold Limit” error should be shown now. As mentioned before, we can ignore it, and I clicked on “Export to Excel” in the Ribbon. And voila, here’s all of my data, exported to Excel: