Pivot table stripped after "unreadable content" error encountered

Pivot tables can be difficult enough to work with by themselves, but it's even more frustrating when Excel finds unreadable content in your workbook and strips the pivot table off the sheet. You may notice this problem after you refresh data in your pivot table, save the file, close it and then try to re-open it. Excel may present you the following error:

"Excel found unreadable content in '[Workbook Name]'. Do you want to recover the contents of the workbook? If you trust the source of the workbook, click Yes."

Clicking Yes to recover the workbook will likely get the workbook to open, but you may find that your pivot table is now a flattened collection of data and no longer a pivot table. This post examines one such cause of the problem and how to prevent it from occurring.

Pivot Tables get their data from the Pivot Cache. The Pivot Cache is a container that holds a static copy of your data. The ability to massage your data in the way you see fit comes from having maintained the source data beforehand in a static go-to container, the pivot-cache. It's what makes pivoting the data possible.

But the pivot cache doesn't like fields that contain a duplicate value when using unique records only, or a non-unique value. Take a date, for example. Excel has a lower bound date limit of 1/1/1900 as the earliest date. So if you were to use a date such as 5/31/1899, Excel wouldn't be able to handle the date and will revert it back to the earliest possible date that it can handle, which is 1/1/1900, assuming the 1904 data system is turned off. If you already had 1/1/1900 in the pivot table, and your date of 5/31/1899 is converted to 1/1/1900, you suddenly have a duplicate value. When saved this way and reopened, Excel runs a check that sees duplicate entry and treats it as a corrupted cache and hacks up the error that you see.

The workaround for this problem is to eliminate the duplicate entry from your data before saving the Excel file. This will satisfy the unique records requirement and Excel won't see the cache as being corrupt.

Comments

• Anonymous
  June 01, 2016
  Hi Will,I have a excel report template set up with lots of pivot tables, but a lot of times when I change my pivot source with new set of data I get the error message you mentioned above and all pivots are removed. I am not sure what you mean by "eliminate the duplicate entry from your data before saving the Excel file". Does only "Date" factor affects the pivot cache? How should I set up my pivots in the template in order to avoid this problem when I want to refresh my data source?
• Anonymous
  July 20, 2016
  This is a terrible workaround and this problem still exists in 2016. It is so frustrating, this has cost me so much time and bother you can't imagine.