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?


    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.


  2. joe

    Does the Loading of the Data take time? I haven’t had a successful completion of the data feed to Excel…just get the spinning loading symbol.

    1. Tony Phillips Post author

      Hi Joe,

      This is quite an old post for classic SharePoint and older versions of Office. I haven’t tried it recently but i guess it depends how much data you have in a SharePoint list, it was pretty much instant for my sample data.



Leave a Reply to Melvin Cancel reply

Your email address will not be published.