Refresh Pivot Table while keeping the external source data open

Occasional Visitor

Hello!

I have a pivot table created from an external excel workbook. I need to display in a Microsoft Teams meeting a computer monitor showing the pivot table while in another monitor I have the external excel workbook open. I need to make changes to the external excel workbook thru ought the meeting, refresh the pivot table being presented to show the changes; without closing the external source data file. Currently I get an error message that reads: [DataSource.Error] The process cannot access the file *** because it is being used by another process. 

I have office 365

Please help!

2 Replies

Hi @Amanda1901 

 

I assume that you have created the pivot table from a Power Query, which reads that data from the external file, right?

In this case, I guess there is no way to avoid that error message, because Power Query will always complain if an external source file is open.

What you could do instead - if this is possible at all in your case - is to create the Pivot table directly in the external file (just for demonstration purposes) and then open a new window within this external file (Menu "View | New Window"). This way you can show the Pivot table in one window in your Teams session, while changing the source table in the other window.

 

I do not know if this is feasible in your case, but this is the only option I can think of.

@DTE 

I cannot thank you enough for your suggestion. I am not using power query; however, your suggestion was on point. I am able to have the pivot table in one instance/window and the data source in another one. More importantly when I make a change in the data source all I have to do is click refresh all in the pivot table and it automatically updates the pivot table without requiring to close the data source. 

Thank you again!!!