Problem Reopening Excel 2013 PivotTable Workbooks

We’ve had a few reports recently from people who have built new PivotTables in Excel 2013, and when they try to reopen the book, receive a message saying:

We found a problem with some
content in <filename>. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

When they click “Yes,” and the file opens, they see a second message:

Removed Part: /xl/pivotCache/pivotCacheDefinition1.xml part with XML
error.  (PivotTable cache) Load error. Line 2, column 0.

Removed Feature: PivotTable
report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)

 The data associated with the PivotTable is still there, but the PivotTable no longer functions. This can also occur if you open a PivotTable from Excel 2010 is opened in Excel 2013, the data refreshed, and the file saved. When you try to reopen the file, it reports a problem with the content.

We have found that this will occur if Excel tries to read a 0 value in where it expects a date. Microsoft is currently investigating the issue further. In the meantime, you can work around this issue as follows:

  1. Right Click on the PivotTable
    Click PivotTableOptions
  2. On the Display tab, clear the checkbox labeled
    “Show Properties in ToolTips.”
  3. Save the file with the new setting intact. Note:
    If you fail to use the workaround before saving and closing the file, we cannot
    recover the PivotTable.

For an explanation about how Excel stores dates as serial numbers, see

Comments (20)
  1. Anonymous says:

    Just confirming that this fixes the issue with the Project Server OLAP Cube:

    Right Click on the PivotTable / Click PivotTableOptions / On the Display tab, clear the checkbox labeled “Show Properties in ToolTips.” Of course, this needs to be done before the PivotTable is corrupted.


  2. DanOliva1 says:

    Maybe unrelated however if you save filtered pivot tables to XML Spreadsheet 2003, the tag causes excel 2013 to crash when it’s loaded. Unfiltered pivot tables work OK because they don’t have the tag. You can load an unfiltered XML Spreadsheet 2003, filter
    it in Excel, save it as XML Spreadsheet 2003 and then try to load it and Again, Excel crashes. This time, however, excel added the tags itself. Seems like a bug because it should be able to load a file that it creates. If you remove the tag from the XML file,
    then Excel 2013 is able to load the file again.

  3. DanOliva1 says:

    That’s a Hidden tag (the blog filtered it out of my last comment)

  4. Ricardo Diaz says:

    I had the same problem and I could solve it, by removing the number formats of the fields. The problem occured after opening the file in an Excel and Windows different language than the one I created in.

  5. Pissed off says:

    Maybe you should inform your users UP FRONT before they waste hours of work only to have it lost, then stumble along this article? Very disappointing.

  6. dorothy says:

    I have Excel documents created in 2007 version that include a pivot table and a pivot chart. Recently 2013 version was installed on my computer and the pivot table works however the link from the pivot table to the pivot chart appears to be broken. Do you have any solutions? I have rebuilt the pivot chart in 2013, however when I update with additional data the link will break again. Thank you for your assistance.

  7. Aman Sood says:

    Hi Anita, is there a fix to this problem? we are trying to migrate our users to excel 2013 but this issue has been causing concerns as the file gets corrupt specially if people don’t know the workaround at the first place.
    Thanks for your help on this in advance.

    Best Regards,

  8. Dan Mc says:

    still having this error, it’s a nightmare, stll no fix

  9. Angela Hemphill says:

    I’ve received same error message however when clicking ‘Yes" for excel to recover – NOTHING happens, it’s just a blank screen, no spreadsheet ever populates… Where do I go from here?

  10. Mike Ross says:

    I also have that issue if the Filter option is set and I try to send data from Internet Explorer to Excel it crash, but the problem is that I have to many files so I need to have a fix for that, I need the filter option to be ON, if any ideas arround please
    let me know, thank you

  11. Rutger Kroon says:

    Hi Anita,

    For non-OLAP pivots the ‘Show properties in tooltips’ option in greyed out anyway. The above mentioned problem remains therefore. You mentioned Microsoft was investigation this issue. 18 months have passed and still no fix?!
    It’s a really frustrating problem which forces me to save the file in the old .xls format, bringing other problems again. Is a fix to be expected or should we just accept that this is something we have to live with?

    Kind regards,

  12. Oleksandr says:

    Excel 15.0.4649.1003, the issue is still there. Is there any fix available? Existing workarounds do not help.
    the message is a bit different this time: Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)

  13. Sohail Ausaf says:

    I am still not able to get a solution to this problem, can anyone help!!

    1. Hello Sohail,
      Please open a support ticket. Thank you.

    2. Neil says:

      Had the same corruption but it wasn’t a date issue. Customer was running Microsoft Office 2013 Pro w/ all updates as of 10/17/2016. Did a full clean of Office to try and resolve as well. All dates were accurate.

      After working through 10K rows, we found three rows that a -0 in the price column.

      We were pasting data from MS Access into Excel that populated our pivot table. Cleaning the data in MS Access and then pasting again into Excel solved the problem.

  14. Tim says:

    This is still broken and solution doesn’t work. I am using power query to populate data which is then read into pivot table – changing the data intermittently causes this problem.

  15. Atila Akal says:

    The steps below solved my problem;
    1- Find out the pivot table which cause the trouble via VBA cacheindex .

    Function ShowCacheIndex(rngPT As Range) As Long
    ShowCacheIndex = rngPT.PivotTable.CacheIndex
    End Function

    Appliying this function for each worksheet and for each pivot table you should find out the problamatic table such as above mentioned pivotCacheDefinition1 .
    2- From Pivot Table Tools Menu Pivot Table Fields on the bottom tick “Defer Layout Update” and save&close the workbook.
    I hope this works on your workbooks too.

  16. Marla says:

    I am having this problem. I created my file in excel 2013. A few days later, I have opened the file and all of the pivot tables on every tab don’t work. I didn’t even go from one excel type to another. I see it has (compatibility mode) behind the file name. I don’t have hours to recreate all of the pivot tables. I tried right clicking on the table (it is viewable, but not working). The pivot table options is not evident. Any other ideas?

  17. Onil says:

    I was having this issue today and resolved it by removing a calculated field from the PivotTable that contained the STDEV.S function.

Comments are closed.

Skip to main content