How to handle Excel 2013 data queries for large lists (>5,000 items) in SharePoint online O365?

Question

I would be interested if anyone has a suggestion for handling this. I am working in Office 365 (but with Excel 2013), so I can't change any of the SharePoint throttling behavior for this list.

Recently one of our lists went over 5,000 items. We use a one item view to determine the last entry that a user made to the list (sorted, filtered by an indexed column, and limited by item limit=1). The value for the view in the browser was correct; however, in the corresponding Excel data query it was incorrect (shown below). Both the browser view and Excel agree when the number of items in the list is less than 5,000 (I deleted 50 items to get under the limit and the Excel data query table then matched the browser view).

Even following the best practices for potentially large lists to create views that filter using indexed columns and not using the item limit feature to limit the view to less than the 5,000 limit, there are issues with the Excel data query.

For Excel data queries not only is the view sorting lost for lists (not views) with more than 5,000 items, the items returned may be sorted in an unexpected manner. For instance, a view sorted numerically (1,2,3,...) by the ID field in the SharePoint view will be sorted alphabetically (1,10,11,...) in Excel, unless the Excel table column sort is used. I think this is the issue using the “Item Limit” option to only return a set number of items as the sorting may be different prior to applying the item limit window for the Excel data query.

I am going to redo all of our views that might be trouble and revise our Excel workbooks as well. Hopefully being careful to use indexed columns and then resorting in Excel will resolve my issues. For situations where I can't know for certain that I will get the correct value in Excel I will use VBA to make a SOAP call, but I would rather not. Or, we may end up changing the workflow logic to not depend on this methodology. We can't use Excel 2016 throughout our organization, but if that works, then I can wait until we upgrade. But if anyone has figured out how to make the Excel view match the browser view for large lists, please share.

An example of this issue using Excel 2013:

In the browser view ID = 5190, same view exported to Excel ID = 1017

Best resources from MS for creating a valid view:

• Use filtering to modify a SharePoint view: https://support.office.com/en-us/article/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4

• Manage large lists and libraries in SharePoint: https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59#__overview

Answer 1

Consider a different tool. Instead of exporting SharePoint views, use Power Query. It's a free add-in from Microsoft for Excel Professional 2010 and 2013 and built into 2016 as "Get and Transform". Power Query has connectors for SharePoint lists, both online and on premises.

I've just done a query that returns over 100,000 records from a SharePoint list into Excel 2010. It takes a couple of minutes to refresh, but it brings back all the data.

When you define the Power Query in the Editor window, you can use ribbon commands to manipulate the data, remove columns you don't require, filter rows you want to keep, and sort the data.

Power Queries can be refreshed with one click on "Refresh All" in the Data ribbon, individually by clicking a query, or with VBA.