Excel pivot caches: why the source data survives deleting the source sheet, and how to take it out

Last updated: 19 October 2026

Short answer: a pivot table does not read its source sheet each time; it reads a cache saved in the workbook, which can hold every source row. Delete the source sheet and the rows are still in the cache, and Excel's Show Details can put them back on a new sheet. The PivotTable options that sound like the fix are not one: Microsoft says "Save source data with file" "should not be used to manage data privacy". If the recipient needs the figures, send them as values; otherwise remove the pivot table and its cache, and check the saved file.

Pivot caches are one of several places covered in hidden data in Excel and hidden sheets, names and personal information. This page goes into them in more detail.

How the cache works

In the .xlsx format (ECMA-376), a pivot table is a view over a pivot cache, and the cache has two parts:

The cache definition (xl/pivotCache/pivotCacheDefinition1.xml and so on) names the source range and sheet, lists each source field, and can list the distinct values of each field as shared items: every name, every department, every postcode that appeared in the source.
The cache records (pivotCacheRecords1.xml) hold the rows. ECMA-376 §18.10.1.68 says this part "stores the underlying source data that the PivotTable aggregates". A saveData attribute on the definition says whether the records are saved with the cache.

So a summary that shows one total per department can carry, inside the same file, the salary line of every person in every department. The pivot table on the sheet shows the summary; the cache holds the detail.

The cache definition can also record who last refreshed it, in a refreshedBy attribute that holds a user name.

How the detail comes back

Show Details. Microsoft's page PivotTable options describes the option Enable show details as "drilling down to detail data from the data source, and then displaying the data on a new worksheet". The PSNI penalty notice, quoted in hidden Excel data in FOI replies, notes that the underlying data "can be accessed simply by double-clicking the pivot table".
Items deleted from the source. The cache can keep items that are no longer in the source. The same Microsoft page has a section, Retain items deleted from the data source, with a setting for the number of items for each field "to temporarily cache with the workbook"; the choices are Automatic (the default), None and Max.
Refresh. Refreshing re-reads the source, if the source is still there; the page also describes "Refresh data when opening the file".

Settings that do not take the data out

Save source data with file. Microsoft describes it as saving or not saving "the data from the external data source with the workbook", and adds: "This setting should not be used to manage data privacy."
Enable show details, turned off. It decides whether Excel offers the drill-down. The page does not say the cache is emptied, so treat the records as still in the file.
Number of items to retain per field set to None. It limits items kept after they are deleted from the source. It does not touch the rows that are still in the source.

Microsoft's Document Inspector page says workbooks "can have cached data for PivotTables, PivotCharts, slicers, timelines, and Cube formulas that might not be visible", and lists them among the things Document Inspector finds but cannot remove.

What to do instead

If the recipient needs the figures: copy the pivot table and paste it as values into a new workbook, or save the one sheet they need as CSV. The new file has the figures and no cache.
If the pivot table should stay: remove from its source what the recipient may not see, set Number of items to retain per field to None, refresh, and save a copy. Then check the saved file as below, in both the shared items and the records, for old values. Anything still in the source is in the cache.
If neither is needed: delete the pivot table, save, and check that the cache parts are gone from the saved file.

Before you remove a pivot table, look for what depends on it: a pivot chart, a slicer, a timeline, or a formula using GETPIVOTDATA.

What ScrubDrop does with them

ScrubDrop reads the .xlsx in your browser; the file is not uploaded. For free, it lists the pivot tables and caches it finds, with the other kinds of hidden data, and removes the user name in a cache's refreshedBy along with the other properties, from one file at a time. Removing pivot tables and their caches is part of ScrubDrop Pro: the pivot table's definition and its cache go, and the figures the pivot table was showing stay on the sheet as plain values. If a pivot chart, a slicer, a timeline or a GETPIVOTDATA formula depends on a pivot table being removed, ScrubDrop refuses and says what to change in Excel first. The result is read again with separate parsers and offered only if nothing of the chosen categories remains.

How to check this yourself

Make a copy of the .xlsx, rename it to .zip and unzip it.
If there is an xl/pivotCache folder, the workbook has pivot caches. In each pivotCacheDefinition file, a worksheet source is named by worksheetSource (with a sheet and ref, or a name for a named range or table); an external source is named by a connection instead. The s v="…" values inside sharedItems are the distinct values of each field. These elements differ by kind of source, so a missing one does not mean nothing is cached.
A pivotCacheRecords file next to it holds the rows; its count attribute is the number of records.
xl/workbook.xml lists the caches in a pivotCaches element.

Questions

If I turn off Enable show details, is the source data gone?

No. The setting decides whether Excel lets someone drill down to the detail data; it does not say the cache is emptied. The records can still be in the file. Send the figures as values instead, or remove the pivot table and its cache.

Does Document Inspector remove pivot caches?

No. Microsoft lists cached data for PivotTables, PivotCharts, slicers, timelines and cube formulas among the things Document Inspector finds in Excel but cannot remove. Remove the pivot table yourself, or send the figures as values.

What does ScrubDrop leave on the sheet when it removes a pivot table?

The figures the pivot table was showing stay on the sheet as plain values; the pivot table's definition and its cache go. That is part of ScrubDrop Pro. If a pivot chart, a slicer, a timeline or a GETPIVOTDATA formula depends on the pivot table, ScrubDrop refuses until you change that in Excel.

Sources: the Microsoft pages linked on this page were checked on 19 October 2026.

See whether a workbook carries pivot caches, in your browser. Free: one file at a time; the supported kinds of hidden data it finds are listed, and properties, comments and tracked changes are removed. Check a file.