通过

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

Comments

  • Anonymous
    January 01, 2003
    thanks

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    That's a Hidden tag (the blog filtered it out of my last comment)

  • Anonymous
    May 05, 2013
    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.

  • Anonymous
    September 08, 2013
    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.

  • Anonymous
    September 17, 2013
    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.

  • Anonymous
    March 10, 2014
    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

  • Anonymous
    July 17, 2014
    still having this error, it's a nightmare, stll no fix

  • Anonymous
    August 15, 2014
    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?

  • Anonymous
    August 25, 2014
    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

  • Anonymous
    September 15, 2014
    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,

  • Anonymous
    September 24, 2014
    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)

  • Anonymous
    May 08, 2016
    I am still not able to get a solution to this problem, can anyone help!!

    • Anonymous
      May 20, 2016
      Hello Sohail, Please open a support ticket. Thank you.
    • Anonymous
      October 17, 2016
      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.

  • Anonymous
    July 20, 2016
    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.

  • Anonymous
    August 01, 2016
    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'http://www.contextures.com/xlPivot11.html ShowCacheIndex = rngPT.PivotTable.CacheIndexEnd FunctionAppliying 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.

  • Anonymous
    September 09, 2016
    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?

  • Anonymous
    April 20, 2017
    I was having this issue today and resolved it by removing a calculated field from the PivotTable that contained the STDEV.S function.