Clinical audit deserves better than a spreadsheet

A colleague at another Trust asked me to help with them analyse some audit data. The mandated tool was an Excel spreadsheet, hosted on SharePoint on the Trust’s NHS.net Microsoft 365 tenancy. Everyone contributing to the audit downloads it, types in their bit of the data, and it’s saved into the cloud. No patient identifiers permitted. Analysis by whoever gets there first.

I understand exactly why it was built that way. But it’s the wrong answer to a problem that hasn’t really been thought through properly.

The category error

The humble spreadsheet is abused throughout the NHS. I’ve lost count of the number of times I’ve been asked to look at a database, only for it to turn out to be an Excel spreadsheet.

In this case, the poor spreadsheet is being asked to do multiple jobs at once:

Job	What it actually requires
Data collection instrument	Constrained fields, validation, provenance, an audit trail of who entered what and when
Data store	A single authoritative copy, immutability during analysis, versioning, access control, a defined retention and deletion policy
Analysis environment	Scriptable, reproducible, capable of the statistical methods the question demands
Output	Graphics, tables, and descriptive statistics

Each of these systems have genuinely different sets of requirements, and a spreadsheet satisfies none of them well precisely because it’s trying to satisfy all simultaneously. The cell you type into is the cell that’s stored is the cell that’s analysed. There’s no separation between input, state, and derivation. There’s no way to reconstruct how you got from the raw observation to the number in the report.

That’s the whole problem. Everything below is a consequence of it.

The case for the defence

To be honest, at first glance, I was impressed. The people who chose to implement this solution were not being lazy, and superficially using a cloud-based spreadsheet solves a lot of issues with clinicians doing audits:

It runs where the trust already has assurance: NHSmail and the associated Microsoft 365 estate are accredited to DCB1596, the NHS secure email standard, which explicitly covers the storage and transmission of patient-identifiable data. Every participating clinician already has an @nhs.net account. There is nothing to procure, no DPIA for a new platform, no information asset to register.
It is the lowest common denominator that actually works across organisations: Different Trusts run different EHRs, different identity providers, and different local data warehouses. A file in a shared tenancy is the only substrate everyone can reach on day one. That is not a trivial property.
It’s BYOD-safe by policy: Because it lives in the managed tenancy, participants can work on personal devices under conditional access without data landing on unmanaged storage (so long as nobody downloads it, which is a “trust me” control rather than a technical one). In these days of squeezed NHS budgets it’s a real cost saving versus issuing managed devices, or fighting over computers in clinical use to do audits.
Cost is approximately zero: The Trust is already paying for NHS.net.

Why it fails anyway

So the spreadsheet is not stupid. It is a rational local optimum given a set of constraints that nobody has tried to relax in twenty years of doing clinical audits. But it’s still a crazy solution.

It isn’t reproducible, and it can’t be

Data are edited in place. There is no diff, no commit, no record of the transformation that turned a date of admission and a date of death into a survival time. If the analysis is wrong - or indeed merely questioned - there’s no way to re-run it and see. Worse still, if corrected data is later entered into the audit, every downstream number has to be reconstructed by hand from memory.

This isn’t a stylistic objection. Goldacre’s Better, broader, safer review made reproducible analytical pipelines - i.e.e analysis written as documented, shareable code - one of its central recommendations across 185 proposals, alongside the shift to secure data environments (more on these later). We accepted that argument for research, but then carried on doing service improvement - which changes practice considerably faster than research does - on the exact infrastructure the review was written to replace.

The error rate is not hypothetical

Panko’s synthesis of field audits of real organisational spreadsheets found that roughly 94% of audited operational spreadsheets contained at least one error, with cell error rates in the low single digits. While those figures are contested on methodological grounds (Powell et al argue a defensible estimate is nearer 1.3% of cells) even if the exact number is arguable the direction is not. Human error rates on complex cognitive steps sit around 2–5%, and a spreadsheet preserves every one of them without complaint.

The canonical example remains Public Health England losing 15,841 positive COVID-19 results between 25 September and 2 October 2020, because results were being collated into .xls files with a 65,536-row ceiling. Since each result occupied several rows, each template silently truncated at around 1,400 cases. The failure mode was not a wrong formula. It was the wrong tool being used as a database and failing silently at the boundary.

The analytical ceiling is low and arrives early

I spent an embarrassing fraction of an afternoon producing one histogram. Reformat the column (insert classic Excel joke here), add a helper column to bin it, build a pivot table, plot this with a point and click, then fight the chart formatting to get it to do what I wanted.

In R that is three lines:

ggplot(audit, aes(x = as.numeric(admission_to_theatre_time, units = "hours")) +
  geom_histogram(binwidth = 1, boundary = 0, closed = "right") +
  labs(x = "Time from admission to theatre (hours)", y = "Patients")

And the histogram is the easy case. The questions worth asking in critical care audits are mostly time-to-event questions - time to antibiotics, time to organ support, competing risks of death and discharge - with clustering by site and confounding by case mix. A Kaplan–Meier estimate with site as a stratum is one line of survival::survfit(). A mixed-effects model adjusting for APACHE II with a random intercept per unit is one line of lme4. In Excel these range from “possible with heroic effort and no way to check your working” to “not going to happen”.

So the tool doesn’t just make the analysis slower. It shapes which questions get asked, and it selects systematically for the shallow ones - proportions meeting a threshold, bar charts by month. Then we wonder why audit rarely changes anything?

The data still has to be extracted by hand

This is the part that really bothers me. Whatever the collection tool, someone is still sitting in front of the EHR transcribing values into a form. That is where the time goes, where the errors enter, and where the sample size limit comes from. The spreadsheet isn’t the binding constraint - manual extraction is. Changing the file format without changing the extraction step is optimising the wrong stage of the pipeline.

The identifier question

The bit I found slightly confusing: if the platform is accredited to handle patient-identifiable data, why does are patient identifiers explicitly forbidden? And surely knowing their age, what procedure they’ve had, and when they had it remains relatively identifiable?

I can see both sides of this argument. Ultimately, platform accreditation is a statement about technical and organisational security, but not a legal basis for disclosure. These are independent questions and you need to satisfy both:

Under the common law duty of confidentiality, implied consent covers local clinical audit conducted by staff who were involved in providing care to that patient. That’s the mechanism by which normal in-house audit is lawful without asking anyone. And yes, there’s been some consternation about how this fits with the NHSE guidance on access to patient records, but the recent update to this has clarified that clinical audit is still ok.
The GMC frames it the same way: explicit consent is required if identifiable information is disclosed for purposes other than the patient’s own care or local clinical audit, absent a legal requirement or public interest justification.
The moment identifiable data crosses that boundary (for example in a multi-centre audit) implied consent stops doing the work, and you need explicit consent or support under Regulation 5 of the Health Service (Control of Patient Information) Regulations 2002 - “section 251”.
Independently, UK GDPR data minimisation says don’t process identifiers you don’t need. And pseudonymised data remains personal data in the hands of anyone who holds, or can obtain, the key.

So de-identification isn’t a workaround for a weak platform. It’s partially patient safety (just in case somebody does download that spreadsheet or leave their screen logged in), it’s partially GDPR compliant (do you actually need a lot of it for the analysis), and it removes the risk for a section 251 application, which would otherwise be a wildly disproportionate ask for a service improvement project. But it also exposes what’s being given up. Without identifiers you cannot link to anything. No ONS mortality, no readmissions from HED, no linkage to a regional shared care record. Every outcome must be transcribed by hand at source, which caps follow-up at whatever the local team can see and be bothered to chase. So althought the de-identification may be lawful and necessary, the loss of linkage is the price, and nobody costed it.

Would a SDE offer salvation?

A secure data environment - otherwise known as a trusted research environment, or TRE - inverts this model. Instead of extracting data and sending it somewhere, approved users are given controlled access to data that never leaves the environment. This became national policy through the Data Saves Lives strategy, with SDEs as the default route for accessing NHS data for research and other secondary uses.

They’re governed by the Five Safes framework:

Safe	What it means in practice
Safe people	Vetted, trained, organisationally sponsored users
Safe projects	Approved purpose, demonstrable public benefit
Safe settings	Analysis happens inside; raw data never egresses
Safe data	Pseudonymised and minimised to the project
Safe outputs	Statistical disclosure control on everything leaving

Using a SDE changes the picture significantly:

flowchart
  subgraph A["Spreadsheet model"]
    direction TB
    A1[EHR] -->|manual transcription| A2[Excel template]
    A2 -->|upload| A3[SharePoint]
    A3 -->|download, edit in place| A4[Analysis in the same file]
    A4 --> A5[Report]
  end

  subgraph B["SDE model"]
    direction TB
    B1[EHR extract] --> B2[(Curated, linked,<br/>pseudonymised data)]
    B6[Locally collected<br/>audit fields] -->|ingress| B2
    B2 --> B3[Analysis code<br/>R / Python, version controlled]
    B3 --> B4{Output checking<br/>SDC}
    B4 --> B5[Report + re-runnable pipeline]
  end

How would this help clinical audit?

Reproducibility by construction: Code lives in the environment and can be re-run. OpenSAFELY takes this seriously - all analysis code is published openly by default as a condition of running against real data, which turns “trust our numbers” into “here is our method, check it”.
Allows the use of real statistics: R, Python, and enough compute to fit models rather than count rows.
Automated outcomes via linkage: Readmission data, deaths, adverse outcomes, referrals, all anyone transcribing anything. This is the single largest efficiency available and the spreadsheet model structurally cannot deliver it.
BYOD is preserved, and arguably strengthened: nothing lands on the device at all.
Governance that’s proportionate rather than absent: The Five Safes framework was designed exactly for “sensitive data, legitimate purpose, no consent for identifiable disclosure” which is the position clinical audit is in.

Has anyone actually done this?

This may seem like an excessive and expensive way to run an audit. But it has been done, very successfully. PRANA, the Pre-hospital Research and Audit Network describes itself as a national clinical audit of pre-hospital critical care, and it is hosted within the Wessex SDE. Its Data Sustains Life project links ambulance, air ambulance, police, DfT and coroner data with hospital records, and was awarded £500,000 by the Department for Transport and the Road Safety Trust. That is a registry-plus-audit asset built on SDE infrastructure, not on a spreadsheet, and it does things no spreadsheet could.

NHS England’s own clinical audits and registries best practice guide already points in this direction, recommending that audits and registries leverage central data investments and route access through DARS and SDE programmes. The policy direction - and funding - exists. Sadly, local practice hasn’t followed.

Where the SDE argument gets weaker

However, I don’t want to argue this too hard, because there are real problems:

Small numbers and disclosure control: Statistical disclosure control blocks outputs with small cell counts, typically fewer than five or ten individuals per cell. A national audit of 40,000 patients is fine. A single-unit audit of 60 patients where the interesting finding is the three cases that went wrong is not. SDC is a poor fit for exactly the granularity at which local audit generates its insight, and I don’t think anyone has properly solved this.
Output checking is a human bottleneck: DARE UK has been explicit that staffing for statistical disclosure control is already limiting the ability to scale SDE use. Audit generates lots of small outputs on short cycles. That’s the worst possible load pattern for a manual review queue.
Access latency versus the audit cycle: The Sudlow Review found in 2024 that researchers routinely wait months or years for secure data access, and called for health data to be treated as critical national infrastructure. Meanwhile resident rotate every four to six months, which is already cited as a reason audit cycles don’t close. A governance process measured in months cannot serve an improvement cycle measured in weeks.
Data ingress is much harder than egress: The genuinely hard requirement for audit is getting locally observed fields - i.e. ones that aren’t in any structured dataset, like whether a decision was documented before or after a conversation, or data from paper notes and charts - into the environment and linked to the right patient. Several SDEs advertise “bring your own data” capability. How well it works in practice for a small collaborative with no data engineering support is the question I’d want answered before recommending this to anyone.
Cost: Eleven sub-national SDEs were seeded with £13.5m as part of around £200m of joint departmental funding. That is real money, and the marginal cost of onboarding a fifteen-site audit collaborative to an SDE is not zero. The spreadsheet’s marginal cost genuinely is close to zero. Any honest comparison has to acknowledge that the spreadsheet wins on cost and loses on almost everything else, and that the trade only makes sense above some threshold of scale, complexity, or repetition.

Is there an answer?

The framing of “spreadsheet versus SDE” is therefore a false binary. Building a SDE is a big undertaking for an individual Trust or even a collaboration, although there are many positives to their existence and once it’s in place using it for audit would be essentially free, with many of the positives described here (even with a few of the downsides). But there’s also potentially a middle tier that almost nobody uses:

Structured capture that isn’t the analysis file: Use a purpose-built form - REDCap is purposely designed for this, but any tool with validation, per-site access control, and an append-only audit trail will do, which may even include Microsoft Forms - separates collection from storage from analysis. This is cheap, mature, and solves most of the reproducibility problem on its own.
An analysis-ready data extract, versioned: Not a file people edit. A dated snapshot that code reads. This can sit within Sharepoint, complete with patient identifiers.
Analysis as code, in a repository: R or Python, in the open available for scruitiny, and unattached to the data. Re-runnable when data is changed or new data added. Many Trusts run their own GitLab instances, for example.
Use a SDE when, and only when, you need linkage or identifiers: such as automated outcomes, cross-organisational linkage, or a persistent registry rather than a one-off cycle.

Steps 1–3 cost almost nothing and capture most of the benefit. Step 4 is where the real prize is - automated mortality and readmission ascertainment would transform what local audit can measure - but it needs governance latency to come down by an order of magnitude before it’s usable on an improvement timescale.

The uncomfortable conclusion is that the spreadsheet isn’t the disease. It’s a symptom of a system that treats audit as a training requirement to be evidenced rather than as data infrastructure to be built and to enable the evidence for meaningful change to be generated. We mandated that every doctor do audit, but never mandated that anyone give them somewhere decent to do it. Twenty years on, we’re still asking senior clinicians to build pivot tables at midnight, and then expressing surprise that the cycle doesn’t close.