Share via

Issue: Modifying Excel Workbook Sync'd with OneDrive or SharePoint breaks Pivot Table Data Source

Anonymous
2025-03-05T23:54:18+00:00

Hi everyone.

I've discovered a significant pain point when working with Excel 365 workbooks saved in OneDrive or SharePoint.

In my workbook I have multiple Pivot Tables that source data from another table in same workbook. The data source for each Pivot Table will look something like: "Table1".

I have discovered that if the workbook is synchronised with OneDrive or SharePoint and you move / change the name of / make a copy of that workbook then the data source for the Pivot Tables is essentially broken and tries to reference the original file and table in the online OneDrive / SharePoint location. So the Pivot Table data source essentially goes from being "Table1" to being "xxxxx.sharepoint.com/xxxx/xxxx/-workbook name-!Table1

This then means there is an an error whenever trying to update and refresh the data for those Pivot Tables, and the user gets warnings about external connections when opening the workbook.

The only way around this is to open and "Save As" each workbook any time you want to make a change (or modify the data source for each Pivot Table). This is not very practical when you have many, many workbooks that each contain many Pivot Tables and other people are collaborating on them to make changes, etc. which break the Pivot Table data source.

So my question is, can this be prevented and/or can it be fixed?

Thank you.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-03-15T06:58:11+00:00

    I think my scenario is very commonly related and it’s driving me crazy.

    Our company files are created and we copy typical files such as excel based templates into the original folder.

    I have created a master sheet (we’ll call it WBS) that is linked to all of the templates allowing the user to populate repeating information only one time. I have shared WBS with my region via a SharePoint link that my team copies to the one drive folder. When they enter information into the newly copied WBS, the templates do not recognize it as they are still linked to MY version of the WBS, not the newly copied WBS.

    Any help is hardly appreciated!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-03-06T22:57:18+00:00

    Thank you.

    Just to point out that if the file is saved in SharePoint (eg, working in an organisation), disabling OneDrive doesn't appear to help. This is probably obvious, but just thought I would point that out.

    Thanks again.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-03-06T05:55:21+00:00

    This is by design when you copy workbooks on OneDrive/Sharepoint.

    As workaround, you may quit OneDrive when you want to make a copy of that file. Then all the datasource will be changed to local. Then copy that file will link correct source when you open OneDrive and sync that new copy back to Sharepoint.

    I will also report it to relevant team. Hope Microsoft will make improvement on copying files with pivot tables. Thank you for your understanding.

    Was this answer helpful?

    0 comments