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.
First open excel and go to “Data” in the ribbon and “From other sources”, select “From OData Data Feed”.
Copy and paste the URL of the site adding “_vti_bin/listdata.svc” at the end.
Select your list when prompted and a pivot table will appear. Select a few columns to populate it with data.
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.
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.
Watch the video guide below for a more detailed explanation and guide.
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
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
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.
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.
Thanks,
Tony
Excel Web Access in SharePoint was a great feature. I don’t know what happened to it with the modern SharePoint though. I have ‘SharePoint Server Enterprise Site features’ enabled in my SharePoint site but when I try to find ‘Business Data’ in my categories when inserting web parts it doesn’t exist. How can this be enabled and is this even possible with modern (not classic) sharepoint pages?
Hi Gene,
Thank you for taking the time to comment. This is a really old post and I think you are right, this is not available on Modern SharePoint. I believe that Microsoft would like you to use PowerBI dashboards for this sort of thing nowadays.
Thanks,
Tony