How to show SharePoint list data in a pivot table

List data inside SharePoint can be pulled into Excel as a data source which can in turn be embedded on a SharePoint page as a graphical representation of the SharePoint list data. Great way to spice up your SharePoint page with graphical content and the functionality is built into SharePoint online. It’s also available in SharePoint on-premises 2010, 2013 and 2016 if you have Office Web Apps configured or excel services enabled.

Excel graph on SharePoint page

First open excel and go to “Data” in the ribbon and “From other sources”, select “From OData Data Feed”.

import sharepoint list data to excel

Copy and paste the URL of the site adding “_vti_bin/listdata.svc” at the end.

connecting to sharepoint

Select your list when prompted and a pivot table will appear. Select a few columns to populate it with data.

Pivot chart in excel

Save the excel file and upload it into a SharePoint document library on your site.

Edit the page and add the “Excel Web Access” web part under the business data category.

adding excel web access web part to sharepoint page

In the web part properties select the excel document and optionally enter a named item such as a pivot chart or table. Your chart is now on the SharePoint page and can be configured to refresh at intervals. It’s a great way to visualise SharePoint list data easily without any third party products.

web part on page

Watch the video guide below for a more detailed explanation and guide.



4 thoughts on “How to show SharePoint list data in a pivot table

  1. Melvin

    Hi Tony,

    Great guide. However when I try to refresh the data within sharepoint, I get an error saying external data refresh failed, unable to connect to the following data.
    I am using sharepoint online. Any idea what else needs to be done?

    Thanks,
    Melvin

    Reply
    1. Tony Phillips Post author

      Hi Melvin,

      I wouldn’t be sure how to resolve this without taking a closer look. I’m guessing that the data source has changed or authentication is not working.

      Tony

      Reply
  2. Bernard

    hi Tony, great video! thanks for sharing. It worked for me…. but, I need to refresh the data in Excel then save the file and then I go to Sharepoitn site and refresh the data. If I do no’t save the file on excel the chart remains the same. How to solve this?
    Thanks

    Reply
    1. Tony Phillips Post author

      Hi Bernard,

      Thank you for your comment. Not sure why that isn’t working for you, the refresh in the web part properties usually refreshes the data from the source. There are other ways of doing this that are more reliable. I would check out PowerBI which now has great integration with SharePoint online. These excel web parts are no longer available in the modern SharePoint experience.

      Thanks,
      Tony

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *