Excel First

Excel Solutions

Can’t Open Pivot Table Source file

Site White Background

Can’t Open Pivot Table Source…

Like a good Excel fan, I switched to Excel 2016 to try the new version.While working on a Dashboard for a client, with a lot of Pivot Tables and charts in it, at some point, after moving some pivot tables and charts into other sheets, within the same workbook, I received this unexpected error message – Can’t Open Pivot Table Source:
Cannot Open Pivot Table Source file

Can’t Open Pivot Table Source file

I never saw this error before, and I have a 20 years experience in working with Excel. Apparently, the pivot table cannot be Refreshed, because the Data Source reference is not relative anymore, it contains the file name too:Before moving pivot tables and charts:
Change Data Source

Change Data Source

After moving a pivot table or a chart, the reference contains the file name:
Change Data Source Absolute reference

Change Data Source Absolute reference

And this is just the beginning… 🙂The file is unusable, any time a user changes the name, the pivot table will fail to refresh. 

I tried the following solutions, with no result:

  • manually removing the file name from the Data Source reference in all pivot tables had no effect, the problem still comes back;
  • even if I used a code to change the data source for all pivot tables, and then removed duplicate Pivot Caches, the file keeps changing the source reference to absolute.

After 2 days of work, I finally narrowed down the source of the error…

 The problem, which is an Excel bug after my opinion, will appear under these circumstances:
  • the excel file was received from another person, and it was created in a previous version of excel, this is one of the cases when the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is checked.
  • the file contains a pivot table, with or without a pivot chart.
  • the pivot chart, or the pivot table, (or both), is moved into another sheet (the chart with cut-paste, pivot with the option-Move Pivot Table)
The bug is caused by the Document Inspector…Jon Peltier wrote an article about this problem, way back in 2010 and provided a workaround developed by Bill Manville, which basically consists in:
  •  Make a copy of the worksheet with the old pivot table and pivot chart in a different workbook;
  • Move the copied worksheet back into the original workbook;
  • Change the new chart’s source data to the new pivot table;
  • Change the pivot table’s data source to the new range;
  • Refresh the pivot table.
What if you have a large number of pivot tables and charts? You will have to work hard to make all these steps for each pivot table and for each chart… 

My solution is in fact a hack, because there is no easy way to fix the workbook…

In previous versions of Microsoft Office, files created in Microsoft Office Excel, Microsoft Office PowerPoint, and Microsoft Office Word were saved in a proprietary, single file format; they were known as binary files.The Office Open XML Formats are based on XML and ZIP archive technologies. However, unlike legacy files, Office Open XML Formats files can be opened to reveal component parts that give you access to the structures that compose the file.To view the structure of the excel file, you can change the file extension from .xlsx to .zip (ignore the warning message) and simply double click the file to open it; don’t forget to change back the extension to .xlsx after you finish editing the file.Or, you can right click the excel file, and Open With WinRaR, WinZip, 7Zip or any other archive software.The key is in the excel archive: if you right click the excel file, and open it with an archiver, this is our guilty folder:xl\pivotCache\_rels , the problem is related to pivot cache relationships…Inside this folder, there should be at least 1 file, named: pivotCacheDefinition1.xml.relsIf there are multiple caches, the rest of the files will be: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels and so on.The normal content of this file should look like this:
PivotCache Definition

PivotCache Definition

 In the broken file, the Pivot Cache Definition will look like this:
PivotCache Definition-Broken

PivotCache Definition-Broken

As you can easily notice, Excel added a new relationship, even there are no external links in the file:http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissingNow, when you open the renamed file, you might get an error message saying that “Data Connections are disabled”, even there there is no external connection.It’s a mistery why Excel considers that we have external content and adds a xlExternalLinkPath/xlPathMissing relationship, and which is the connection with the Document Inspector.If you look in the archive, in xl\pivotCache folder, there are 2 files:pivotCacheDefinition1.xml and pivotCacheRecords1.xml.The pivotCacheDefinition1.xml will contain the following expression (note the relationship ID in red):<worksheetSource name=”DataTable” r:id=”rId2″/>The red part will never be found in a normal file, the normal value should be:<worksheetSource name=”DataTable”/> 

And now, the solution:

Warning: Please apply these steps on a copy of your workbook, in some circumstances you can corrupt the workbook and it cannot be repaired! (as I said it’s mostly a hack)

Step 1:

Open the file in excel, uncheck the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”, then save and close the file.

Step 2:

Open the excel archive with an archiver, and go to xl\pivotCache\_rels folder.From the pivotCacheDefinition1.xml.rels file (and from all other pivotCache Definition files), simply delete the red part, but ONLY the red part, otherwise excel will not be able to open it, it will become corrupted:
Remove Relationship

Remove Relationship

Save the changes and close the file.

Step 3:

Open the excel workbook.All pivot tables data source will have this weird looking reference: !DataTable . Note that the reference to the file name is missing, but there still is an exclamation mark.All you have to do is to click the SAVE button and close the file! Now, when you open the file, the bug is gone forever…


The relationship ID from pivotCacheDefinition1.xml will not contain the wrong relationship ID r:id=”rId2″, excel took care of that itself, and rewrote the pivotCacheDefinition1.xml.rels.


If the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is unchecked, the pivot chart and pivot tables can be moved, the bug will not appear.Excel will check this option automatically if the file was created in a previous version, or if you use the Document Inspector to remove personal information, the next time you open the Privacy Options you will notice that the option is checked, even if it was not checked before using the Document Inspector. If the option is greyed, you can only activate it if you use the Document Inspector.Users should do this on a copy of their file, it’s not bullet proof, if they make a mistake, they can corrupt the file irreversibly, excel will open with attempts to repair the corrupted file.Or, you can download the file below and apply the above steps to fix it, to make sure you understand the process, even if it’s fairly simple:Functional Pivot Table – Broken.xlsxAs a conclusion, I hope that Microsoft engineers will fix this bug, from what I’ve seen, there are many users with this problem, I even found an old topic started in 2013, with no functional solution: data-source-path-in-pivot-table-changes-to-absolute-on-its-own 

An alternative:

 As mentioned above, another way to solve this absolute reference problem is to copy the data source data sheet and the sheets with pivot tables and charts into a new “clean” workbook, and to change the data sources for all pivot tables to the data source from the new workbook, this solution is more accessible to most users.If the workbook is too complex to move all the sheets into a new book, then hacking the archive is the way to fix the current workbook.It is an extreme solution, but it was the only one that worked for me, without the need to manually move the sheets and reconstruct the entire workbook.If this solution worked for you, please share this information 🙂Cheers,Catalin

23 Comments to Can’t Open Pivot Table Source file

  1. NickC says:

    Catalin, thank you, thank you!

    After literally dozens and dozens of hours of searching for answers and trying different proposed solutions without success, finally yours has proved successful.  I closely read (and re-read) and carefully followed your described process and, despite having no previous knowledge or experience of editing the code within Excel, I was able to successful undertake the task with the desired result! I can now make a copy of or save my pivot table-containing worksheet as a new version (with a new name (e.g. …version2.xlsx) or to a different place on the network) and when the new file is opened the pivot tables within seek their data from the source table within the same workbook rather than, as previously, from the previous version of the workbook.


    • Hi Nick,
      Glad to hear that your problem is solved, hopefully Microsoft will solve this problem someday, without the need to disect the archive…
      It took me a lot of time too, to create this unique solution.

  2. Prasad says:

    Hi Catalin, thanks a ton for the solution… similar problem was resolved at our end with detailed and clear step wise instructions….

  3. Gavin says:

    Hi Catalin,

    In step 2, what is the excel archive and what is archiver? Where can I find the xl\pivotCache\_rels folder?



  4. David says:

    Can this be done for an Excel Binary file (.xlsb)?

    • Yes, in the PivotCache/_rels folder you will see the relationship files, the only difference is that it will have names like xl\pivotCache\_rels\pivotCacheDefinition1.bin.rels instead of xl\pivotCache\_rels\pivotCacheDefinition1.xml.rels (bin instead of xml), but they are still xml file types.

  5. Reem says:

    but I have a problem that when I open the pivotCacheDefinition1.xml.rels. I can’t delete the link! it does not allow me
    any idea?

    • How are you opening the file? Are you using a text editor, or just a browser?
      Open with Notepad, or Wordpad to make changes.

      • Lunar says:

        I faced the same problem in editing the code. It can open like a read-only on MS Edge. When I click to open with the text program, I can delete but can’t save to the same file type. Please give me your suggestion. Thank you.

        • Control Panel\Programs\Default Programs : Set your default programs by file type
          Or, from Win start button in bottom left: Settings, then search for default app, you should see an entry : Choose a default app for each file type. Then set the app for .xml instead of MS Edge to: Notepad++ (best choice IMO), or Notepad, or Wordpad.
          You can try also moving the xml file outside archive for editing, then move it back to archive when changes are saved.

  6. Emilia Castillo says:

    What if the pivotCacheDefinition1.xml.rels fil
    coding does not look as the one sample picture you provided? What then?

  7. Francis says:

    I just wanted to point out that, after having faced this same issue with an Excel workbook, and before diving into your more complex solution, I started by trying the first simple solution that you mentioned didn’t work (manually removing the file name from the Data Source reference in all pivot tables) and it seems this simple procedure now does the trick. I have edited, saved, closed and reopened the affected workbook a few times and the issue has not reappeared. Plus, I’ve checked and the data source reference stays relative. (Of course this is still a cumbersome solution if you have many pivot tables that need fixing…) I hope this might be of help to someone else coming across this issue.
    I’m running Excel for Microsoft 365 MSO (16.0.14326.20062) 32-bit, btw.

  8. LuckyStar says:


    Worked out this very annoying issue for me (EXCEL 2013):
    1) Create a new excel file with the new name you want
    2) Move (not copy!) all the worksheets of the old name file into the new name file
    3) Update and save the new file

  9. Adrien says:

    Thanks so much for this! The unzip/edit procedure worked perfectly to repair a file that was causing massive headaches.

    For what it’s worth this was on Excel 2021 on a Mac, and after reopening the edited file I was prompted to “Repair” it which seemed to work and produced a report showing the broken pivot having been removed. The (very large) workbook also runs dramatically faster afterwards.

  10. Polina says:

    Hello Catalin,

    Thank you very much for your article!

    I am just wondering which part is to be deleted so that the Excel file is not subsequently corrupted. Apparently, I have less content in my code that is is displayed in your example.

    My code looks like as follows:

    Could you please advise which part should be removed?
    If you could help me with this issue, I would be really grateful.


  11. Fernando says:

    Catalin, you’re a hero and a genious!!!
    Thank you so much for this, simply amazing!

Leave a Reply

Your email address will not be published. Required fields are marked *

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: