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 http://support.microsoft.com/kb/214094.

Comments (15)

  1. hassan sayed issa20014 says:

    thanks

  2. 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.

    See: social.technet.microsoft.com/…/project-server-2013-olap-data-connection-problem

  3. 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.

  4. DanOliva1 says:

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

  5. 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.

  6. 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.

  7. 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.

  8. 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,
    Aman

  9. Dan Mc says:

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

  10. 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?

  11. 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

  12. 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,

  13. 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)

  14. 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.