Using Power Query to Load SharePoint List Data in Excel
Power Query is an Excel add-in which can be used to grab data from a number of sources including OData. Using the OData feed will allow you to grab SharePoint list data using the list data service and load it into Excel. Power query can be used to access both on premise SharePoint sites and SharePoint online.
To connect to the list data service select the “From Other Sources” dropdown in the “Power Query” tab and select “From OData Feed”.
You will then be prompted to enter the URL for the OData feed you want to access and to sign into your account using the same credentials you would use to log into SharePoint.
Once you are connected you will see a list of SharePoint lists you can grab data from.
You select a single list or multiple lists to load into excel. Once you have selected the lists click Load and the data will be loaded onto a new Excel worksheet. You can then use this data in Excel.