Why cant I find the source data for a pivot table?

Question

Hello, on one particular tab of my spreadsheet, when I try to see the data source the "Change Data Source" option is unavailable. When I check the connection properties the connection name seems to be pointing to a place that I have no knowledge of. Since this is a work spreadsheet, under normal circumstances I can see the path to the file - in this situation I do not. What is it about this pivot table or spreadsheet that could be causing the 'Change Data Source" to be unavailable and the "Connection Name" to point to a location that is foreign to me? What could have been broken? See screenshots below. Any feedback is appreciated.

Answer 1

Dear Jcmu99,

Thanks for posting the screenshots for us to confirm the issue in your environment.

Generally, the Connection Properties option is available when you use external data sources such as network sources or other local files' sources to create the pivot table. So I'd like to confirm whether the pivot table is created from external data sources. If yes, what external data sources are you using?

Does the issue only happen in the specific pivot table?

Please also check whether you are using the latest version of the Office applications. If no, I suggest you update the Office applications' version, restart the computer and then do a test to see the result. Meanwhile, upload a full screenshot of the Account page in the Excel application you are using for our reference. You can access the page via clicking on File>Account.

Note: Remove any private information before uploading the screenshots.

Moreover, I'd like you to log into the computer with the administrator account, go to Control Panel>Programs>right-click on the Office applications' icon>Change>check whether there is an Online Repair option. If yes, run an online repair, restart the computer and then do a test to check the result.

Best Regards,

Cliff

Answer 2

Dear Jcmu99,

Thanks for posting the screenshots for us to confirm the issue in your environment.

Generally, the Connection Properties option is available when you use external data sources such as network sources or other local files' sources to create the pivot table. So I'd like to confirm whether the pivot table is created from external data sources. If yes, what external data sources are you using?

Does the issue only happen in the specific pivot table?

Please also check whether you are using the latest version of the Office applications. If no, I suggest you update the Office applications' version, restart the computer and then do a test to see the result. Meanwhile, upload a full screenshot of the Account page in the Excel application you are using for our reference. You can access the page via clicking on File>Account.

Note: Remove any private information before uploading the screenshots.

Moreover, I'd like you to log into the computer with the administrator account, go to Control Panel>Programs>right-click on the Office applications' icon>Change>check whether there is an Online Repair option. If yes, run an online repair, restart the computer and then do a test to check the result.

Best Regards,

Cliff

The data is from an external source - I just have no way of finding out what that external data source was as this was passed around my company and no one has any knowledge of it. So, is it possible that the data source no longer exists or the directory on the network where it used to reside is gone? Just trying to understand a couple of things..

1. Why I can create a brand new pivot table and grab the data from a spreadsheet on my network, then save it.
2. And then delete that spreadsheet on the network (essentially breaking the connection to the data source)..but when I open the spreadsheet with the table it STILL shows the original path to the data source - which is great!
3. So essentially my question is, what do you think happened with the spreadsheet I mention above? That location "Worksheet Connection" is totally foreign to me - is that some kind of generic term Excel uses when a connection to a data source is broken?

Answer 3

Dear Jcmu99,

Thanks for your updates.

Based on my test, the Change Data Source option and the Connection Properties option should be available after you create a pivot table from an external source. You can change the data source to local source as well.

So to find the root cause of the issue, you may need to find the original source of the pivot table first. As some other local files are also external source, I'd like to go to the local computer to see if you can get the source file.

After you delete the source file, the previously inserted pivot table will exist but you can't refresh it and some refresh issues may happen. But the Change Data Source option and the Connection Properties option are available and you can change the source file to fix the issue or you may need to recover the source file to do a refresh.

Best Regards,

Cliff

Answer 4

Dear Jcmu99,

Welcome to share any updates when you have time if you need further help on this issue.

Best Regards,

Cliff