Replacing Spreadsheets in QC Without Starting a Two-Year Project

Why Both Of The Usual Answers Are Wrong

Ask a quality control director how many spreadsheets support GMP decisions in the laboratory and you will usually get a range rather than a number. The range is wide because nobody built the collection on purpose. A method transfer needed a conversion. A stability program needed a pull schedule. A regulator asked for trending on environmental data and the fastest way to produce a chart was a workbook. Each one was a good local decision. Together they form an infrastructure layer that no one owns, that changes without change control, and that produces numbers which end up in batch records and regulatory filings.

When the problem finally gets escalated, two answers get proposed in the same meeting.

Answer one: validate them all

This sounds rigorous and is usually a way of doing nothing slowly. A spreadsheet validation package that is worth having includes a requirements statement, a specification of every formula, testing against known inputs including boundary and error conditions, a protection scheme, a controlled location, a defined change procedure, and periodic review. That is real work. Multiply it by the number of files a mid-size site actually has and the program consumes a quality unit for a year while delivering nothing a patient or an inspector would notice for most of the files.

Worse, it produces a maintenance obligation in perpetuity. A validated spreadsheet is only validated while the controls hold. The moment an analyst copies it to a desktop to make a small adjustment, the package describes a file that no longer exists. Organizations that validate everything tend to end up with a shelf of documentation and a laboratory that operates on uncontrolled copies, which is a worse position than having neither, because the documentation now creates an expectation the practice does not meet.

Answer two: replace everything with a LIMS

The other proposal is to buy or extend a laboratory information management system and move all of it there. This is right for some of the content and wrong as a total answer, for three reasons.

First, timing. A LIMS implementation or major extension is measured in quarters at best and years at worst. During that entire period the spreadsheets keep running, uncontrolled, and the control problem is deferred rather than solved. Second, fit. A meaningful share of laboratory spreadsheets are not laboratory data at all. A reagent inventory, a training matrix, an instrument calibration due list, and an equipment log are administrative trackers. Forcing them into a LIMS is expensive configuration for low value, and the configuration itself becomes a validation burden. Third, arithmetic. Some calculations belong in the chromatography data system, not the LIMS, because that is where the raw data and the integration parameters already live and where recalculation is already covered by the system’s own controls.

Step One: Find Every Spreadsheet, Including The Ones You Are Not Supposed To Know About

You cannot tier what you have not found. The inventory is the part teams want to skip and the part that determines whether the rest of the work is credible.

Note the relationship to the wider computerized system inventory required under Annex 11 clause 4.3, which calls for an up-to-date listing of all relevant systems and their GMP functionality.⁶ The spreadsheet inventory is a feeder into that listing, not a replacement for it. Build it as a working document that resolves into inventory entries for the files that survive tiering, and into disposal records for the ones that do not. Treating it as a separate permanent register creates a second system of record, which is the failure mode this whole exercise is meant to avoid.

Where they actually live

Four locations, in ascending order of difficulty.

Managed shared drives and document systems. The easy population. A file crawl over the laboratory’s shared folders, filtered to spreadsheet extensions and sorted by last-modified date, produces a candidate list in an afternoon. Include macro-enabled formats, because those are the highest-risk files in the estate and the ones most likely to be treated as ordinary documents.

Attachments inside controlled systems. Spreadsheets attached to deviation records, change controls, protocols, and validation reports. These are often overlooked because the containing record is controlled and the assumption is that the attachment inherits that control. It does not inherit formula protection or a recalculation history. If an attached workbook computed a number that supported a conclusion in an investigation, that workbook is in scope.

Instrument workstations. Local drives on chromatography, spectroscopy, dissolution, and particle counting workstations. Analysts export to a local file, calculate, then print or transcribe. The local file is frequently not backed up, not retained, and not visible to anyone outside the bench. This population overlaps with the question of where raw data ends and processed data begins in chromatography systems, which is a separate discussion in its own right, but for inventory purposes the rule is simple: if a file on that workstation contributed to a reported result, it counts.

Personal drives and mailboxes. The population everyone knows about and nobody lists. This is where the honest part of the exercise happens.

How to get the personal-drive files without starting a witch hunt

The instinct is to run a discovery sweep across user home directories. It works technically and fails organizationally. People hide what they think will be taken away, and a laboratory that hides files from quality is in a much worse position than one that keeps files in the open.

The approach that works is an amnesty framed around usefulness rather than compliance. Announce a defined window. State plainly that the purpose is to find out which calculations people are doing by hand because no system supports them, that nothing will be deleted without the owner being consulted, and that the outcome for most files will be either a controlled version of the same thing or a small application that does the job better. Then ask each analyst and supervisor a short set of questions.

• What do you calculate outside a validated system, and why?
• Which file do you open at the start of a shift that is not in a system?
• What would you have to do by hand if your laptop failed today?
• Which number that leaves this laboratory passed through a spreadsheet on the way out?

The last question is the one that finds the material risk. It reframes the request from an audit of behavior into a question about data flow, and people answer it accurately because it does not sound accusatory.

Pair the amnesty with a technical sweep of managed locations so that the interview data can be cross-checked. Where the sweep finds a file that nobody declared, treat it as an inventory gap to close rather than a disciplinary matter, at least on the first pass. The first inventory is for learning what is there. Enforcement belongs to the operating model that follows it.

What to record about each file

Resist the urge to build a rich metadata model. Most of the fields will be wrong within a month and the extra effort delays the classification step, which is where the value is. Ten fields are enough.

Collect these in a structured form rather than free text, and have the owner complete it rather than a central team guessing. The exercise of writing the business purpose and the output destination is itself the beginning of the classification, and owners are the only people who reliably know where the numbers go.

Step Two: Classify By What The File Actually Does

Classification schemes fail when they ask about importance, because everyone’s file is important. They work when they ask a factual question with a checkable answer. Use three questions in order, and stop at the first yes.

Getting the tier decision right

Two rules keep the classification honest.

Follow the output, not the intent. A file described as “just for trending” that produces the trend chart pasted into an annual product quality review is influencing a GMP conclusion. It may still be Tier 3 if the underlying data and the conclusion are both documented elsewhere and the chart is illustrative. It moves to Tier 2 if the chart is the only place the trend was ever evaluated. Ask where the evaluation is recorded, not what the file is called.

Split files that span tiers. Many workbooks contain a calculation tab, a log tab, and a chart tab. Classifying the whole file by its highest tier means applying Tier 1 controls to a chart, which is wasteful. Classifying it by its lowest tier is a compliance failure. The right answer is usually to split the file, which is also the first step toward moving the calculation into a system and leaving the tracker as a tracker.

Layering risk on top of tier

Tier says what the file does. Risk says how much it matters if it is wrong. The combination determines effort, and it is a straightforward application of the quality risk management principle that runs through Annex 11 clause 1 and is carried forward in the draft revision of the annex.⁶⁷ Score two dimensions coarsely, on three points each.

A Tier 2 calculation with high consequence and high likelihood of an error going undetected is the priority set. In most laboratories that set is between five and twenty files. That is a tractable number, and identifying it in the first six weeks is worth more than perfecting the classification of the remaining hundreds.

What Validating A Spreadsheet Actually Means, And Where It Stops

Some spreadsheets legitimately stay. Before deciding which, leaders need an accurate picture of what a validated spreadsheet does and does not give them, because the gap between the two is where inspection findings live.

The regulatory frame

A spreadsheet used in a GMP activity is a computerized system. Annex 11 opens with the statement that it applies to all forms of computerized systems used as part of GMP regulated activities, and that the application should be validated while the IT infrastructure should be qualified.⁶ There is no size threshold and no exemption for desktop tools. In the United States, 21 CFR 211.68(b) requires appropriate controls over computer or related systems to assure that changes in master production and control records or other records are instituted only by authorized personnel, along with backup of records.⁹

The FDA’s data integrity guidance makes the connection to spreadsheets explicit in its discussion of static and dynamic records. Describing what makes a record dynamic, the guidance notes that the format may allow the user to modify formulas or entries in a spreadsheet used to compute test results or other information such as calculated yield.⁵ That single sentence carries the operative point: a spreadsheet is a dynamic record, and dynamic records carry the full set of expectations around retention, review, and the ability to see what changed.

Where 21 CFR Part 11 applies, the electronic records and electronic signatures requirements apply to the spreadsheet as they would to any other electronic record system, including the controls in subpart B for closed systems.⁸ The draft revision of Annex 11, published for consultation jointly by the European Commission and the Pharmaceutical Inspection Co-operation Scheme, has no final text and no implementation date, so nothing in it is in force. It is still worth reading for direction of travel. Its qualification and validation section states that increased focus should be on testing a system’s handling of key functional requirements and functionality designed to ensure data integrity, and it lists calculations explicitly among the areas named.⁷ For a spreadsheet estate, that is a direct signal about where inspector attention is heading.

What a spreadsheet validation package contains

Under GAMP 5 second edition, software categorization is a way of scaling effort to risk and complexity rather than a fixed label.¹⁰ A spreadsheet is a useful illustration of why the categories are a continuum rather than boxes. The underlying application is commercial software in wide use. What the laboratory built on top of it, formulas, links, lookups, and in some cases macros, is the part that carries the GMP function and the part that needs the evidence. The practical consequence is that validation effort should scale with the complexity of what was built, not with the name of the product it was built in.

A defensible package for a Tier 2 calculation workbook contains, at minimum:

1. A requirements statement. What the workbook must calculate, from which inputs, to what precision, with which rounding conventions, and what it must reject.
2. A formula specification. Every calculation written out in a form a reviewer can check without opening the file. This is the document that survives the file.
3. Test evidence against known inputs. Normal cases, boundary cases, and error cases. Test what happens with a blank cell, a text entry in a numeric field, a value outside the calibration range, and a division by zero. Annex 11 clause 4.7 calls for evidence of appropriate test methods and test scenarios covering parameter limits, data limits, and error handling.⁶
4. A protection scheme. Which cells accept input, which are locked, which sheets are hidden, and who holds the password. The password is a controlled item, not a shared convenience.
5. A controlled location and version identity. A single master copy in a location where users cannot overwrite it, with a version number visible on the printed output.
6. A change procedure. How a formula change is requested, assessed, tested, approved, and released, and how the previous version is retired. This is change control, not a note in a file name.
7. A periodic review. Confirmation that the file in use is still the validated file, that the protection is intact, and that the calculation still matches the method it supports. Annex 11 clause 11 requires periodic evaluation to confirm systems remain in a valid state.⁶

The limits, stated plainly

This is the part that gets glossed over in vendor material and it is the part senior leaders need to understand before they authorize keeping a population of validated spreadsheets.

There is no native audit trail of the kind Annex 11 clause 9 contemplates. That clause asks for a system-generated record of GMP-relevant changes and deletions, with the reason documented for changes and deletions of GMP-relevant data, available in an intelligible form and regularly reviewed.⁶ A spreadsheet application does not generate that record. Change tracking features and version history in cloud document platforms provide something related but not equivalent: they typically capture that a file changed and who saved it, not that a specific formula was altered, why, and with what effect on a previously reported value. Where a platform’s version history is used as the compensating control, that use has to be specified, tested, and reviewed like any other control, and its gaps have to be documented rather than assumed away.

Attribution is weak. Annex 11 clause 12.4 expects management systems for data and documents to record the identity of operators entering, changing, confirming, or deleting data, including date and time.⁶ A shared network file opened under a shared session does not do this. Where the file is the record (Tier 1), this limitation is usually decisive: the file cannot meet the requirement and the data needs to move.

Copies defeat everything. The validated master exists in one place. The moment a user saves a copy to work on, the controls do not travel with it. Every control in the list above depends on a behavioral assumption that people will use the master. That assumption is the weakest link in the entire approach, and it is the reason spreadsheet validation should be reserved for the files that genuinely warrant it rather than applied broadly.

What the error research actually supports

The literature on spreadsheet errors is often cited loosely in vendor material, usually as a single dramatic percentage. The underlying picture is more useful and more defensible than the headline.

Panko’s synthesis of field audits and laboratory experiments concluded that spreadsheet errors are both common and non-trivial, that the field audits conducted from 1997 onward found errors in at least 86% of the spreadsheets examined, and that across roughly a thousand experimental subjects building spreadsheets from word problems, 51% of the resulting files contained errors even though most were only 25 to 50 cells.¹¹

Powell, Baker and Lawson reviewed the same literature critically and were more cautious. They noted that the widely repeated figure of a 5% cell error rate rests on five studies covering only 43 spreadsheets in total, some of them informally conducted.¹² When they built an explicit auditing protocol and applied it to 50 diverse operational spreadsheets, they found errors in 0.9% to 1.8% of formula cells depending on how an error was defined, and they found that the rate varied widely from one spreadsheet to another.¹³

Use the conservative number, because the conservative number is more than sufficient. At roughly one error per hundred formula cells, a workbook with 300 formulas is likely to contain several. A laboratory calculation that produces a single reportable result from a chain of twenty formulas has a meaningful probability of being wrong somewhere, and the wrongness will not announce itself. This is the actual argument for controlling calculation spreadsheets, and it does not require the dramatic version.

The life-sciences example that makes the point concretely is gene name corruption. A 2016 study found that a fifth of papers with supplementary gene lists in spreadsheet format contained gene names that had been converted to dates or floating point numbers without anyone noticing by default application settings.¹⁴ A follow-up covering 2014 to 2020 found the problem had not improved, identifying errors in 30.9% of 11,117 articles examined.¹⁵ Nobody chose to corrupt that data. The application did it on open, and the researchers did not notice. A quality control laboratory pasting lot numbers, batch identifiers, or sample codes into a workbook is exposed to the same class of automatic transformation.

For the wider pattern, the European Spreadsheet Risks Interest Group has collated public reports of spreadsheet failures for many years, and the collection continues to grow at a steady rate across sectors.¹⁶ The value of that record is not any single incident. It is the demonstration that these failures are ordinary rather than exceptional.

Step Three: Decide Per Tier, Not Per File

Once the inventory is classified, the decisions become policy rather than a hundred separate arguments. Four dispositions cover the estate.

A default disposition by tier and risk

The light standard for Tier 3

Most of the estate ends up here, so the standard applied to it determines whether the program is sustainable. Four requirements, no validation package.

• Controlled location. The file lives on a managed drive or in a document platform with backup. Not on a desktop, not in a mailbox.
• Named owner. One person, recorded, who is responsible for the file’s continued fitness.
• An explicit statement of what it is not. A visible note on the file stating that it is a working tool and not a GMP record, and naming where the authoritative record is held. This one line prevents the most common drift, which is a tracker gradually becoming the thing people rely on because it is easier to read than the system.
• Annual confirmation. At periodic review of the laboratory’s system inventory, the owner confirms the file still serves its purpose or it is deleted. This is what stops the estate regrowing.

The Migration Test: Which Spreadsheets Are Worth Moving At All

A migration decision made file by file becomes a negotiation. A migration decision made against stated criteria becomes a review. Five questions, answered before any configuration work is scoped.

1. Does a system you already own do this?

If the LIMS or the chromatography data system already performs the calculation and the laboratory does not use that function, the problem is adoption, not capability. This is common and it is usually the cheapest win available, because the functionality is validated already and the work is training, configuration review, and a procedure change. Before scoping any development, ask the system owner to demonstrate the function. Teams are frequently surprised.

2. Will the calculation still exist in two years?

Some workbooks support methods that are being replaced, products that are being transferred, or processes that are being redesigned. Migrating a calculation into a system three months before the method retires is effort spent on something that will be decommissioned. Check the method lifecycle and the product roadmap before committing. A spreadsheet with a short remaining life is a candidate for interim controls and a documented end date, not migration.

3. Is the logic stable enough to specify?

A workbook that changes every month because the underlying approach is still being worked out is not ready to be configured into a validated system. Moving unstable logic into a change-controlled environment converts a monthly edit into a monthly change control, and the laboratory will work around it. Stabilize the logic first, in a controlled spreadsheet with a defined review cycle, and migrate when it stops moving.

4. Does moving it remove a transcription step?

The strongest migration cases are the ones that eliminate a manual handoff rather than just relocating a calculation. If the current process is export from the instrument, calculate in a workbook, then type the result into the LIMS, moving the calculation removes two error opportunities and a manual accuracy check obligation under Annex 11 clause 6.⁶ If the current process is a self-contained calculation from manually entered values that will still be manually entered afterward, the gain is control rather than error reduction, which is a weaker but still legitimate case.

5. What happens to the historical data?

This question is routinely deferred and routinely becomes the reason a migration stalls. Historical results in a Tier 1 workbook are GMP records subject to the retention schedule. Options are to migrate the history into the target system, to archive the workbook as a static record with a documented readability check, or to retain it in place under maintained controls until the retention period expires. All three are defensible. Choosing none of them and hoping the question does not come up is not. Annex 11 clause 17 expects archived data to be checked for accessibility, readability, and integrity, and expects the ability to retrieve it to be tested when relevant system changes are made.⁶

The Uncontrolled Spreadsheet In An Inspection

The regulatory argument for this work does not rest on interpretation. Inspectors have written about spreadsheets in warning letters, in specific language, for years. The pattern in those letters is worth understanding, because it shows what triggers a finding and what the finding then does to the rest of the inspection.

The calculation nobody verified

In a September 2021 warning letter to Missouri Analytical Laboratories, the FDA wrote that the firm’s analysts used individualized non-validated spreadsheets to calculate assay, impurity, content uniformity, and dissolution test results for a variety of drug products.¹ The word doing the work in that sentence is “individualized.” Each analyst had a version. There was no master, so there was no way to demonstrate that any two results had been produced the same way.

An August 2021 letter to Adamson Analytical Laboratories cited a failure to validate electronic worksheets used by laboratory personnel for microbial challenge efficacy testing, and stated that microbial worksheets reviewed were found to use unvalidated cell formulas resulting in erroneous data generation such as negative log reductions and percent reductions.² This is the outcome the error literature predicts. The formulas were wrong, the outputs were physically impossible, and nobody caught it, because there was no test evidence and no independent recalculation. The letter also noted the firm had been cited for a similar violation in a previous warning letter, which is the pattern that turns a laboratory control finding into a quality system finding.

The workbook that was the record

A February 2020 letter to Chemland Co., Ltd. stated that the firm stored its master batch records as unlocked Excel files which were open to alteration, duplication, and deletion by unauthorized personnel, and that analysts used open Excel files for documenting sample preparation information and final calculations.³ This is Tier 1 exposure in its clearest form. The file was the record, the file had no protection, and the file’s contents could not be shown to be what they had been.

The files that disappeared overnight

A January 2025 letter to Global Calcium Pvt. Limited described investigators observing Microsoft Excel documents on a desktop computer in a production office on the first morning of the inspection, including files relating to cleaning validation samples and production details, and then returning on the second day to review those files and finding that all of them had been deleted and could not be recovered.⁴ Whatever the intent, the position is unrecoverable. Files that exist outside any controlled location can be removed without trace, and once an investigator has seen them the absence of a trace is itself the finding.

What this means for how you present the estate

A laboratory that can produce a current spreadsheet inventory, a stated classification, a validation package for the files it kept, and disposal records for the files it removed is in a fundamentally different position from one that cannot, even if both have spreadsheets in operation on the day of the inspection. The first is managing a known population under a documented risk-based approach. The second is discovering its own estate in front of an investigator.

This is why the quarter-length version of the work has value even though it does not finish the migration. Completing the inventory and the classification, and being able to show the disposition decisions with dates against them, converts an open-ended exposure into a managed program with a plan. That is a defensible answer. “We are replacing all of this with a new LIMS in 2028” is not, because it says nothing about the eighteen months in between.

Practitioner commentary in the analytical literature has made the same point for years. McDowall’s treatment of spreadsheet use alongside chromatography data systems examines, through case studies, how post-run calculation in spreadsheets creates regulatory exposure and when spreadsheets should and should not be used in a regulated laboratory.¹⁷ The consistent conclusion is that the calculation should happen where the data already is.

What One Quarter Actually Buys You

Here is what is genuinely achievable in thirteen weeks with a part-time working group, roughly one full-time equivalent split across a quality unit representative, a laboratory supervisor, and someone from the computerized systems or validation function. This is not a migration plan. It is the plan that makes a migration scopeable and defensible in the meantime.

What will not be finished, and why that is acceptable

At the end of the quarter, the migrations have not happened. Tier 1 rebuilds and Tier 2 moves into the LIMS or the chromatography data system take longer than thirteen weeks and depend on system roadmaps outside the laboratory’s control. Configuration work in an existing validated system carries its own change control and testing obligations, and it competes with everything else in the system owner’s queue.

What has happened is more valuable than a partial migration would have been. The estate is known. The highest-risk calculations are under control with evidence. The unnecessary files are gone. Everything else has a named owner, a stated disposition, and a date. The laboratory can answer the inspector’s question, and the organization can now scope the migration against a real list instead of an estimate.

What sustains it afterward

An estate that was cleaned once and then left alone regrows in about eighteen months, because the conditions that created it are still there. Three controls keep it from happening.

A stated rule about new spreadsheets. Not a ban, which will be ignored, but a rule with a low threshold: any new spreadsheet whose output will reach a GMP record must be registered before first use, and registration triggers the tier question. A five-minute form is enough. The point is that the file becomes visible on creation rather than at the next inventory.

A named home for the demand. Most Tier 1 and Tier 2 spreadsheets exist because someone needed something a system did not provide and had no route to request it. If that route still does not exist after the cleanup, the next workbook is already being built. Giving the laboratory a real intake path into the systems team, with a service level that beats building a workbook, addresses the cause rather than the symptom.

A place in periodic review. The annual confirmation for Tier 3 and the periodic review for validated Tier 2 files both belong in an existing review cycle rather than in a standalone process. A control that has its own separate schedule is a control that gets skipped in a busy quarter.

Conclusion

The spreadsheet population in a quality control laboratory is not a discipline problem and it is not a technology gap. It is the visible record of every occasion when the laboratory needed something the systems did not provide and solved it locally. Treating it as misconduct produces hidden files. Treating it as a system selection problem produces a multi-year program that leaves the exposure open the whole way through. Treating it as an estate to be inventoried, classified, and dispositioned produces a defensible position within one quarter and a real migration plan at the end of it.

The judgment that matters is proportion. A small number of calculation workbooks genuinely determine reported results and deserve full validation, full change control, and eventual migration into a system that can attribute changes properly. A larger number are records that a spreadsheet cannot adequately hold and need to move. The largest group by count are trackers that need a controlled location, a named owner, and an honest label, and nothing more. And a substantial share should simply be deleted, which is the disposition with the best ratio of risk reduction to effort and the one most consistently avoided.

Sakara Digital works with pharma and biotech organizations on exactly this kind of scoping problem: separating the part of a legacy estate that needs formal validation from the part that needs a smaller answer, and sequencing the work so that the highest-risk items are controlled first rather than last. If you are looking at a laboratory spreadsheet population and trying to decide whether it is a validation program, a systems program, or a cleanup, we are happy to have that conversation.

For Further Reading