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.

Leave a Reply

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