Refreshing data in Excel Pivot Table on Sharepoint needs the data source files to be open to work

Question

I have a pivot table in Excel that gets its data from 2 other Excel files that are in the same folder on a Sharepoint.

But when I try to refresh the data for that Pivot Table, I get an error with the suggestion to open the source files.

And indeed when I open both source files, then the data refresh works.

How do I make this data refresh work without having to open those source files every time?

Answer 1

Hi Mersie,

Thank you for posting your query in our community.

Per your description, when you try to refresh the pivot table in Excel, it will ask you to open the source file. May I know that whether you insert the pivot table via Insert > PivotTable > From External Data Source > Choose connect… > Tables > select the table of the opened source file? If yes, I tested it on my side, and the result was same as yours.

And I found a workaround that you may could first create a power query for the source file, and then insert the pivot table, you can check these steps:

1. First, go to the SharePoint library > select the file > click Details icon > scroll down and copy the path;

2. Then go to the workbook you want to insert the pivot table, then go to Data > From other sources > From web > paste the path you just copied from SharePoint > Next > select Organizational account and sign in with your account > Connect > Load the items(table or sheet);
3. After creating power query for the source file successfully, then you can create a new sheet and go to Insert > PivotTable > From External Data Source > Choose connect… > Connections > select the query you just added in this file and load it.
4. Now you no need to open the source file to refresh this pivot table. Please note that you could only open the file from Excel client to refresh the data via this method.

Best regards,

Jazlyn