When working with SharePoint list data in Microsoft Excel, it is often necessary to report based on user data (e.g. name, department, job role, etc.) and most of the time this information isn’t immediately available as User fields only store the user’s unique ID and extended metadata is not pulled in from SharePoint automatically.
When using MS Excel to provide reports from SharePoint (SP) data, a common problem is managing the size of the dataset being retrieved. SP sites are notoriously long-lived and data can grow and sprawl over time, with lists having 100,000s or even millions of items in them, and Excel reports linked to those lists can take so long to load they are all but unusable. Using an OData query can help get around these issues.