Established onExcel 365 and 2016 and later: document inspector, pivot cache settingExcel: a second hidden state for sheetsGoogle Sheets, web: no equivalent pivot cache
A workbook contains more than its sheets, and all of it travels whenever the file does.
Hidden rows, columns and sheets
Present in the file and one menu item from visible. Excel has a second hidden state, settable only through the object model or by editing the file, which does not appear in the ordinary unhide list; it is still not security and anybody who knows where to look can reverse it.
The filtered view
A filter changes what is displayed. Every row is still there, and sending a filtered file sends all of them.
The related trap is copying: a copied range includes hidden and filtered-out cells unless the selection is explicitly restricted to visible cells. Pasting a filtered selection therefore brings back everything, which is a surprise in both directions.
The pivot table's own copy
This is the one that catches organisations. A pivot table stores a cached copy of the source data inside the workbook, so that it can be manipulated without the source being present.
Delete the source sheet and the pivot table continues to work, because the data is still in the file. Double-clicking a value in it produces a new sheet containing the underlying rows, extracted from that cache.
So a workbook from which the detail was deleted before circulation can contain the detail, retrievable by anybody, through an ordinary double click. There is a setting controlling whether the cache is saved with the file, and it defaults to saving it.
The metadata
Author, last editor, company, the file paths of linked workbooks, the names of printers, and any remaining comments and tracked changes are all stored in the file's properties.
The linked paths are the interesting one: a reference to a file on somebody's machine records the folder structure of that machine, and a reference to a network location records the internal layout of the organisation's storage.
What removes it
Excel includes an inspector that finds and removes these categories, including hidden sheets, personal information and comments. Running it is a minute's work and is the closest thing to a safe-to-send button that exists.
It does not remove the pivot cache. That requires either clearing the setting and refreshing, or building the file from values as described in the previous entry.
The safest route, once more
Create a new workbook. Paste the values that should be shared. Send that.
Everything in this entry is a way of finding what a copy of the original still carries, and every one of them is a search that can miss something. Starting empty cannot.
Why this keeps happening
Because deleting is what the interface makes easy and because a file that looks right is a strong signal. The material described here is invisible by construction, and nothing in the ordinary act of preparing a file to send draws attention to any of it.
Incidents of this kind are reported regularly, and the pattern in the published accounts is consistent: a spreadsheet was prepared for publication by removing what should not be published, and the removal was of the visible layer.
A checklist before sending outside the organisation
Unhide everything and look. Check the pivot tables and whether their sources are present. Run the inspector. Open the links dialogue and see what the file points at. And if any of that takes more than five minutes, build the extract instead.
What we cannot verify
The pivot cache behaviour, its setting and the inspector are documented by the vendor and were confirmed on a current build. The second hidden state for sheets is documented in the object model reference. Google Sheets does not have an equivalent pivot cache in the same form, which was established by testing rather than from documentation stating it.
The other cached copy
Charts store the values they plot, so a chart in a workbook whose source data was removed continues to draw the same picture from data still inside the file.
The same applies to a chart pasted into a document or a presentation with a live link: the values travel with it. Pasting a chart as a picture is the only route that carries nothing but the appearance.
Checking a file somebody else prepared
Before publishing anything received from a colleague, unhide every sheet and every column, and look at the file size. A workbook of a few visible rows that occupies several megabytes is carrying something, and the pivot cache is the usual answer.
In short
- Hidden rows, columns and sheets are one menu item from visible, and one has a second state.
- A filter changes the display, and copying a filtered range brings back everything.
- A pivot table keeps its own copy of the source data inside the workbook.
- Deleting the source sheet leaves that copy, retrievable by double-clicking a value.
- Metadata records the author, and linked paths record the layout of somebody's machine.
- The inspector removes most of it and does not remove the pivot cache.