TL;DR
- Incident: Royal Cornwall Hospitals NHS Trust inadvertently published sensitive sickness absence data belonging to 8,003 individuals via an active Excel pivot table cache in an FOI disclosure response.
- Root Cause: The request was misrouted to an operational team, Excel Document Inspector was bypassed or ignored, and visual table formatting was wrongly assumed to delete underlying data streams.
- Exposure: The file remained publicly accessible on the trust’s web portal for 33 months (February 2023 to November 2025), accumulating 340 logged accesses in its final 12 months online.
- Operational Fallout: An emergency breach helpline planned for two weeks was forced to run for 11 weeks due to high call volume, leaving underprepared staff to handle distressed calls while discovering their own health data was exposed.
- Remediation: The Information Commissioner’s Office (ICO) closed its investigation after the trust mandated CSV file conversions, senior Information Governance (IG) sign-off, and password protection protocols.
In February 2023, the Royal Cornwall Hospitals NHS Trust responded to a Freedom of Information Act (FOIA) request regarding workplace stress by publishing a spreadsheet on its public disclosure log. Behind the aggregated tables lay an excel pivot table data leak that exposed sensitive sickness absence records belonging to 8,003 individuals—comprising 7,715 trust staff, 288 non-trust workers, and a small number of unidentified records. The underlying dataset remained fully intact within the document structure, allowing anyone who downloaded the file to extract personal health details by double-clicking summary cells.
The spreadsheet remained publicly accessible for 33 months before an unnamed member of the public discovered the vulnerability and notified the trust in November 2025. Web server logs for the 12 months preceding the notification revealed 340 access events, though available logging could not differentiate between human users performing interactive data extraction and automated search engine crawlers indexing public pages.
This post-mortem analyses the technical mechanics of Excel container caches, the workflow failures that bypassed standard redaction controls, and the severe operational strain placed on incident response teams. It provides engineering teams and data governance officers with concrete technical remedies, including automated egress checks and programmatic file sanitisation pipelines to eliminate hidden metadata risks permanently.
The Incident: 33 Months of Hidden Sickness Records
The exposure originated from an FOIA request submitted in early 2023 seeking detailed statistics on staff stress and sickness absences across the organisation. Rather than routing the request through specialized data release channels, internal intake processes assigned the task to the trust’s e-Rostering team. Staff within the e-Rostering unit compiled the requested figures using internal operational databases, generating an Excel workbook that summarised absence days and categories into pivot tables.
When published to the trust’s public disclosure portal in February 2023, the workbook appeared to contain only high-level summary tables. However, the authoring team failed to decouple the visible pivot tables from the underlying source data range. The published file contained detailed line-item sickness absence records for 8,003 individuals. The exposed dataset included full names, job roles, specific dates of absence, and granular medical reasons for leave, including mental health conditions and stress-related diagnoses.
Timeline of Exposure
The file remained hosted on the public FOI disclosure log from February 2023 until November 2025—a window of 33 months. Access logs covering the 12 months prior to removal showed 340 access events. Because log retention was limited to 12 months, total accesses across the full 33-month window remain unknown.
The identity of the member of the public who discovered the cached pivot table data in November 2025 has not been disclosed. Upon receiving notification of the flaw, the trust revoked public access to the file and initiated an internal investigation alongside regulatory reporting to the Information Commissioner’s Office (ICO). The breach response required establishing a dedicated helpline in December 2025 to support affected staff and contractors. However, operational oversights during helpline setup severely compounded the organizational strain, extending the helpline’s operation from a planned 2-week window to an 11-week deployment that ran until February 2026.
Technical Deep Dive: How Pivot Table Caches Expose Underlying Data
Understanding why visual redaction fails in Microsoft Excel requires examining the internal construction of modern Office OpenXML (.xlsx) and legacy binary (.xls) spreadsheet containers. An .xlsx file is not a flat document; it is a compressed PKZIP archive containing structured XML files, relationships, and embedded media streams defined by the OpenXML specification (ISO/IEC 29500).
When a user creates a pivot table in Excel, the application does not query the source worksheet dynamically every time a user views the table. For performance optimization, Excel constructs an internal database object known as the PivotCache. This cache holds a complete, indexed copy of the source data range in memory and serialises it directly into the file container when saved.
# Unpacking an OpenXML (.xlsx) structure reveals embedded cache files
$ unzip Spreadsheet_Release.xlsx -d unpacked_excel/
$ tree unpacked_excel/xl/
unpacked_excel/xl/
├── workbook.xml
├── worksheets/
│ └── sheet1.xml # Visible summary view
├── pivotTables/
│ └── pivotTable1.xml # Table layout definition
└── pivotCache/
├── pivotCacheDefinition1.xml # Cache schema and source range metadata
└── pivotCacheRecords1.xml # FULL UNDERYLING DATASET (XML format)
Inside the unpacked archive, the xl/pivotCache/pivotCacheRecords1.xml file contains every row and column attribute present in the original dataset at the moment the pivot table was refreshed. Even if an analyst deletes the original source worksheet (e.g., sheet2.xml containing the raw data) or hides specific rows and columns within the visible grid, the PivotCacheRecords stream remains intact inside the ZIP archive.
The Mechanics of Double-Click Drill-Down
Excel features a built-in interactive functionality known as DrillDown or Show Details. When a user double-clicks any aggregated cell inside a pivot table grid, Excel executes a query against the embedded PivotCacheRecords stream rather than the visible worksheet grid.
<!-- Excerpt from pivotCacheDefinition1.xml -->
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
r:id="rId1"
saveData="1"
refreshOnLoad="0">
<cacheSource type="worksheet">
<worksheetSource name="Raw_Staff_Sickness_Data" />
</cacheSource>
</pivotCacheDefinition>
The key attribute in the cache definition file is saveData="1". When set to 1 (true), Excel explicitly saves the underlying record cache within the document container. When a user double-clicks a summary count (for instance, a cell displaying “42 staff absences”), Excel interprets this user interaction as a command to filter pivotCacheRecords1.xml for all items matching that cell’s intersection criteria. It then instantiates a brand new worksheet (e.g., Sheet2) and writes every matching raw row directly into the grid.
| User Action | Visible Interface State | Underlying OpenXML Container State | Data Security Boundary Impact |
|---|---|---|---|
| Delete Source Data Sheet | Raw data tab is removed from tab bar. | xl/pivotCache/pivotCacheRecords.xml remains fully intact. |
Zero Protection: Data remains fully extractable via double-click. |
| Hide Columns/Rows | Sensitive attributes hidden from view grid. | XML tags inside pivotCacheRecords.xml retain all hidden attribute strings. |
Zero Protection: Hidden attributes populate on drill-down sheet. |
| Apply Filter to Pivot View | Grid shows subset of aggregate totals. | Cache retains full, unfiltered source dataset. | Zero Protection: Unfiltered records remain in cache stream. |
| Disable Cache (“Save Data” unchecked) | Pivot table renders, but drill-down fails off-line. | pivotCacheRecords.xml is purged from the container archive. |
Partial Protection: Prevents simple extraction, but layout metadata remains. |
Because visual redaction techniques—such as hiding rows, applying visual filters, or deleting source tabs—only modify the display rules in worksheets/sheet1.xml, they leave the underlying XML data streams completely untouched. Publishing a file with an active PivotCache is equivalent to distributing a database dump alongside a pre-written SQL reporting query.
Workflow & Governance Breakdown: Where the Redaction Controls Failed
The independent review into the Royal Cornwall Hospitals NHS Trust incident highlighted systemic failures across routing, technical verification, and executive approval chains. The technical control failures stemmed directly from operational breakdowns in standard operating procedures.
1. Operational Misrouting
Standard data governance protocols mandate that Freedom of Information Act requests involving complex, multi-variable personnel datasets must be processed by a specialized data unit—in this case, the trust’s People Information Unit. Instead, the request was routed directly to the e-Rostering operational team. While the e-Rostering team possessed functional access to scheduling data, they lacked formal training in FOIA data sanitisation standards and data protection risk management.
2. Omission and Limitations of Excel Document Inspector
Internal operating guidelines instructed staff handling public disclosures to run Microsoft Excel’s native Document Inspector (File -> Info -> Check for Issues -> Inspect Document) prior to distribution. In this instance, the assigned handler either omitted running the tool or failed to interpret its output warnings.
However, relying solely on Document Inspector exposes an architectural vulnerability in data governance workflows. Document Inspector can detect the presence of pivot table caches and hidden streams, but it operates with significant limitations:
- Lack of Contextual Intelligence: Document Inspector flags the existence of a
PivotCache, but it cannot evaluate whether the cached dataset contains public summary data or sensitive, personally identifiable health records. - Non-Mandatory Action: The tool presents a warning prompt requiring manual user intervention to clear the cache. If an operator assumes the underlying data is benign, they can bypass the warning without altering the file.
- No Automated Blocking: Standard desktop configurations do not prevent users from saving or emailing inspected files even if unresolved warnings persist.
3. Failure to Enforce Transformation Standards
The trust’s procedural guidelines recommended converting spreadsheet responses into PDF format prior to release. During the compilation of the February 2023 response, staff skipped this conversion step entirely. (The specific version of Microsoft Excel used by the e-Rostering team when compiling the initial response has not been disclosed in public findings).
Furthermore, internal guidance at the time did not mandate converting data responses into Comma-Separated Values (CSV) format. While PDF conversion prevents the native double-click drill-down behavior in desktop spreadsheet viewers, it remains an imperfect security boundary for structured data releases. Only flat formats such as CSV guarantee the complete destruction of embedded application logic, macros, and container caches.
4. Defective Sign-Off Priorities
The final approval process focused heavily on public relations management, media messaging, and verifying that textual answers directly satisfied the wording of the FOI request. Approvers operated under the unverified assumption that technical data sanitisation had already been performed by the operational team prior to submission for executive sign-off. No technical verification step existed to audit the file’s binary container structure before publication.
Human and Operational Blast Radius: The Incident Response Fallouts
When the exposure was identified in November 2025, the trust initiated its emergency incident response framework. A core component of the mitigation plan involved deploying a dedicated phone helpline in December 2025 to handle inquiries from current and former employees whose health data had been compromised.
The operational execution of the helpline suffered from severe planning deficiencies, as detailed in reporting by The Register. Staff assigned to staff the response call centre were drawn from the trust’s clinical governance team. Despite their experience in clinical governance, these workers were provided with minimal operational preparation for managing crisis communications:
- Notice Period: Staff received approximately 24 hours’ notice prior to being assigned to the breach helpline.
- Script Preparation: The standardized response script was delivered to call handlers just 30 minutes before the phone lines went live, leaving no time for scenario testing or roleplay exercises.
- Lack of Safeguarding Training: Handlers faced hostile, angry, and deeply distressed callers. Some calls involved complex safeguarding scenarios where exposed sickness records presented risks related to domestic abuse or personal safety. Handlers were forced to make real-time safeguarding decisions without established escalation frameworks or specialized training.
Operational Conflict of Interest
Many clinical governance staff assigned to the helpline only discovered that their own personal sickness absence records and sensitive health details were included in the leaked dataset while actively handling incoming calls from affected colleagues.
The helpline was initially scheduled to operate for two weeks in December 2025. Due to continuous call volume and the complex emotional support required by callers, the trust was forced to extend operations for 11 weeks, finally standing the team down in February 2026. Staff members were expected to maintain their routine clinical governance responsibilities alongside call-handling duties, leading to significant overtime and severe operational burnout.
Technical Remediation: Automating Excel Sanitization and CSV Export
To eliminate hidden metadata risks, organisations must decouple data presentation from underlying storage formats. Human verification of complex binary or OpenXML files is inherently error-prone. Technical controls must enforce structural transformations that make data retention physically impossible.
Why CSV Strips Container Risks
Converting an Excel workbook to a raw CSV (Comma-Separated Values) format acts as a hard security boundary. CSV is a plain-text format containing only character strings, delimiters, and line breaks. Converting a file to CSV enforces the following security properties:
- Purges OpenXML Streams: Completely removes the ZIP container layout, destroying `xl/pivotCache/` streams, XML relationship maps, and custom document properties.
- Eliminates Interactive Logic: Disables all formulas, VBA macros, dynamic data exchanges (DDE), and event handlers.
- Destroys Hidden Worksheets: Exposes only the active visible grid layer chosen during export, leaving hidden tabs unexported.
While PDF conversion flattens the visual display and prevents native double-clicking, PDFs can still inadvertently preserve hidden text layers, embedded attachments, or object metadata if generated incorrectly. CSV remains the mandatory baseline format for raw tabular disclosures under FOIA.
Programmatic Cache Purging with Python
Data pipelines releasing spreadsheet files publicly must automate the destruction of pivot caches. The following Python script uses the openpyxl library to scan workbooks for active PivotCache definitions, strip the cache objects, and convert visible sheets into safe CSV files. Test this script in a staging environment before incorporating it into publishing workflows.
#!/usr/bin/env python3
"""
FOIA Data Sanitiser - OpenXML Cache Purger & CSV Exporter
Requires: openpyxl, pandas
Usage: python sanitise_release.py <input_file.xlsx> <output_directory>
"""
import sys
import os
import zipfile
import shutil
import pandas as pd
import openpyxl
def inspect_and_purge_pivot_cache(input_path):
"""Checks for pivot caches inside the OpenXML ZIP container."""
has_cache = False
with zipfile.ZipFile(input_path, 'r') as zip_ref:
file_list = zip_ref.namelist()
cache_files = [f for f in file_list if 'xl/pivotCache/' in f]
if cache_files:
has_cache = True
print(f"[WARNING] Detected {len(cache_files)} PivotCache stream(s) in {input_path}")
for cf in cache_files:
print(f" - Embedded Stream: {cf}")
return has_cache
def export_to_sanitised_csv(input_path, output_dir):
"""Loads workbook grid data and exports visible worksheets as flat CSV files."""
if not os.path.exists(output_dir):
os.makedirs(output_dir)
# Load workbook evaluating formulas to static values
wb = openpyxl.load_workbook(input_path, data_only=True)
base_name = os.path.splitext(os.path.basename(input_path))[0]
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
# Ignore hidden worksheets in export pipeline
if sheet.sheet_state != 'visible':
print(f"[INFO] Skipping hidden sheet: {sheet_name}")
continue
data = sheet.values
try:
cols = next(data)
except StopIteration:
continue
data_list = list(data)
df = pd.DataFrame(data_list, columns=cols)
# Drop completely empty rows and columns
df.dropna(how='all', inplace=True)
output_csv_path = os.path.join(output_dir, f"{base_name}_{sheet_name}.csv")
df.to_csv(output_csv_path, index=False, encoding='utf-8')
print(f"[SUCCESS] Exported sanitised sheet: {output_csv_path}")
if __name__ == "__main__":
if len(sys.argv) < 3:
print("Usage: python sanitise_release.py <input_file.xlsx> <output_dir>")
sys.exit(1)
target_file = sys.argv[1]
destination = sys.argv[2]
if inspect_and_purge_pivot_cache(target_file):
print("[ACTION REQUIRED] Direct XLSX publishing blocked. Converting to CSV...")
export_to_sanitised_csv(target_file, destination)
Automating Egress Scanning via PowerShell
For Windows-centric environments, sysadmins can deploy pre-publication scanning scripts via PowerShell. The script below inspects outbound files targeted for web hosting, searching specifically for binary stream indicators of embedded data caches.
# Audit-FOIAFile.ps1
# Scans targeted .xlsx files for embedded PivotCache streams prior to public release
[CmdletBinding()]
param (
[Parameter(Mandatory=$true)]
[string]$FilePath
)
Add-Type -AssemblyName System.IO.Compression.FileSystem
if (-not (Test-Path $FilePath)) {
Write-Error "File path not found: $FilePath"
exit 1
}
$zip = [System.IO.Compression.ZipFile]::OpenRead($FilePath)
$pivotEntries = $zip.Entries | Where-Object { $_.FullName -like "xl/pivotCache/*" }
if ($pivotEntries.Count -gt 0) {
Write-Host "[SECURITY ALERT] File '$FilePath' contains $($pivotEntries.Count) embedded PivotCache object(s)!" -ForegroundColor Red
foreach ($entry in $pivotEntries) {
Write-Host " - Container Path: $($entry.FullName)" -ForegroundColor Yellow
}
$zip.Dispose()
exit 2 # Non-zero exit code to halt automated publishing pipeline
} else {
Write-Host "[PASS] No PivotCache streams detected in '$FilePath'." -ForegroundColor Green
$zip.Dispose()
exit 0
}
Policy & Process Engineering for Public Data Disclosures
Hardening data release pipelines requires structural changes to organisational policy. Technical controls fail when operational workflows allow untrained units to bypass central oversight gates.
Role-Based Routing and intake Enforcement
All incoming requests under Freedom of Information or Environmental Information Regulations must be enforced at the gateway layer. Automated intake rules must route any query requesting data tables directly to specialized data teams (such as a People Information Unit or Data Governance Office). Operational teams—such as e-Rostering, Facilities, or Finance—must be restricted from executing direct external file releases.
Segregation of Sign-Off Duties
Organizational sign-off policies must separate administrative approval from technical data sanitisation verification. Executive approval certifies that the response policy aligns with public disclosure obligations and PR strategy. Technical approval certifies that the binary container structure has passed automated and manual sanitisation checks.
[ Incoming FOI Request ]
│
▼
[ Specialized Data Unit ] ─── (Extracts & Aggregates Raw Data)
│
▼
[ Data Sanitisation Pipeline ] ── (Runs Audit Scripts, Purges Cache, Converts to CSV)
│
▼
[ Technical IG Approval Gate ] ── (Verifies OpenXML Streams & Metadata via Scanner)
│
▼
[ Executive / PR Sign-Off ] ── (Approves Narrative Context & Q&A Accuracy)
│
▼
[ Public Release Log ]
ICO Regulatory Case Resolution
Following its internal post-mortem and independent investigation, the Royal Cornwall Hospitals NHS Trust updated its internal operating framework. The Information Commissioner’s Office (ICO) formally closed its regulatory case after the trust instituted three mandatory compliance controls:
- Mandatory Workbook Password Protection: Requiring encryption on operational spreadsheets distributed internally to prevent unauthorized structure modification.
- Senior Information Governance Approval: Mandating that a senior Information Governance (IG) officer perform technical verification and sign-off on all spreadsheet-based responses prior to release.
- CSV Standardisation Guidelines: Formally revising standard operating procedures to mandate CSV conversion for all public data releases containing tabular data.
Practitioner Action Plan: Preventing Hidden Metadata Disclosures
Security teams, system administrators, and data governance officers should execute the following phased implementation plan to identify and mitigate metadata exposure risks across public file repositories.
Phase 1: Audit Historical Public Repositories
Inventory all legacy files currently hosted on public disclosure portals, FOI logs, and web servers. Search specifically for native spreadsheet extensions (.xlsx, .xlsm, .xls, .xlsb).
# Example command to inventory hosted spreadsheet files on Linux web servers
$ find /var/www/html/disclosures/ -type f \( -name "*.xlsx" -o -name "*.xls" \) -exec ls -lh {} \;
Pass identified spreadsheets through the PowerShell or Python audit scripts detailed above to locate embedded PivotCache streams, hidden worksheets, or active macros. Remove non-compliant legacy files immediately and re-publish them as sanitised CSV files.
Phase 2: Implement CI/CD & Security Gateway Rules
Configure content inspection rules on web application firewalls (WAFs), reverse proxies, or CMS publishing workflows. Direct upload pipelines should block public releases containing binary spreadsheet extensions by default.
Gateway Blocking Strategy
Configure file-upload validation rules on public CMS platforms to allow only flat text formats (.csv, .txt) and fully flattened vector documents (.pdf). Require administrative override approval for any raw spreadsheet upload attempt.
Phase 3: Formalise Incident Response for Internal Breach Targets
Update incident response playbooks to address scenario-specific risks when internal staff personal data is exposed. Ensure the following mechanisms are established prior to launching public breach helplines:
- Conflict Checking: Cross-reference the list of impacted data subjects against designated incident response personnel to prevent affected staff from handling their own exposure incidents without support.
- Pre-Deployment Training: Enforce mandatory training on managing hostile communications, managing caller distress, and evaluating safeguarding threats prior to opening call lines.
- Safeguarding Escalation Paths: Establish direct escalation pathways to on-call senior safeguarding officers for calls involving personal safety or domestic abuse concerns.
Common Data Sanitisation Mistakes
Avoid these frequent operational and technical missteps during dataset release processing:
- Mistake: Setting row height or column width to zero. Changing visual dimensions in Excel leaves data fully visible in the formula bar and fully accessible via copy-paste actions.
- Mistake: Applying white text fills over sensitive cell values. Changing font colour affects only rendering rules; raw values remain in clear text inside the XML worksheet stream.
- Mistake: Converting to PDF using print drivers without checking hidden layers. Certain virtual PDF printers embed source document properties and hidden text layers. Always inspect exported PDFs with text extraction tools.
- Mistake: Trusting visual inspection alone. Never approve complex documents for public distribution based on visual verification in desktop viewing applications.
Key Takeaways: Hardening Public File Release Pipelines
The 33-month exposure at Royal Cornwall Hospitals NHS Trust demonstrates that visual redaction inside complex binary formats is fundamentally flawed. When working with OpenXML containers, visual presentation rules in the worksheet grid operate independently from embedded cache databases stored in underlying XML streams.
Organizations publishing data to public disclosure logs must treat file conversion as an absolute security boundary. Converting tabular releases to raw CSV format strips container logic, eliminates macros, and purges embedded pivot caches entirely. By pairing automated egress verification tools with strict intake routing and technical sign-off gates, data governance teams can eliminate hidden metadata exposures and build resilient public release pipelines.
Frequently asked questions
Why does saving an Excel file with a pivot table retain hidden underlying raw data?
Excel creates an internal database object called a PivotCache to optimize performance when rendering pivot tables. By default, Excel saves this complete cache directly inside the OpenXML document container (xl/pivotCache/pivotCacheRecords.xml). Even if an author deletes the original source data tab or hides rows, the cached records remain stored in the file container.
Does converting an Excel spreadsheet to PDF completely eliminate underlying data exposure risks?
Converting to PDF flattens the visible grid display and prevents interactive double-clicking actions that extract pivot cache records. However, PDF conversion is not a guaranteed security boundary. Improperly configured PDF exporters can preserve embedded objects, hidden text layers, or document metadata. Converting tabular data directly to CSV format remains the safest method for public releases.
Why is converting spreadsheet disclosures to raw CSV format recommended for FOIA compliance?
CSV files contain plain text strings separated by delimiters. Converting a workbook to CSV completely destroys the OpenXML container structure, stripping embedded pivot caches, hidden worksheets, macros, formula logic, and XML relationship streams. This structural conversion makes it technically impossible for end users to reveal hidden underlying datasets through document manipulation.
How can organizations automate the detection of cached data in outbound files before publication?
Security teams can deploy pre-publication inspection scripts using Python or PowerShell that inspect .xlsx containers directly. Because OpenXML files are ZIP archives, automated scanners can check for the existence of `xl/pivotCache/` paths or parse the `pivotCacheDefinition.xml` stream for attributes like `saveData=”1″`. Gateway rules can automatically block non-compliant files from being published to web servers.
Sources and further reading
- NHS breach helpline left staff carrying a (The Register · Security)
This article was drafted with AI assistance from the publicly cited sources and passed automated checks for structure and sourcing. Verify critical details against the vendor advisories linked above before acting.