Excel Online – Consuming SharePoint Lists


Here is how to Consume a list in SharePoint Online via Excel Online:

Open Excel > Data tab > From Other Sources  > From Odata Feed


Important: You need to enter the location and call the List Data Service (you need to add _vti_bin/listdata.svc to the end):

Example: https://microsoft.sharepoint.com/teams/officetechnologies/_vti_bin/listdata.svc

Click Next


Select Tables.  In this example, we chose TestList

 

Modify the File Name: & Friendly Name: click Finish

 

Choose the radio button next to PivotTable Report > OK

 

Build the PivotTable and Save it to SharePoint Online.

When you open it, you will see the PivotTable

Now add to the SharePoint List

Go back to the Excel Online and choose Data > Refresh All Connections

Click Yes

 

You will now see the updated values in Excel Online!

Comments (4)

  1. Anonymous says:

    Update:
    You can consume a SharePoint Online list in Excel Online:
    Excel Online – Consuming SharePoint

  2. Gejza Horvath says:

    Great post,
    I was looking for exactly this. Thank you.

  3. naga says:

    Hi

    I am trying to connect to the sharepoint list provided by u in the blog for testing but it is asking login credentials. Tried with Microsoft account also but not working.

  4. Jithin Karlose says:

    I tried to do the same but the Excel Online that came with the office 365 Enterprise E3 with sharepoint doesnt have these options for adding external data. https://technet.microsoft.com/en-us/library/excel-online-service-description.aspx#Create external data connections
    Also in the above article it mentions the option to add external data isnt available. I am confused please help me out with this.

Skip to main content