Excel spreadsheets in GMP laboratories: validation and controls

A spreadsheet returns the expected value even though a mandatory measurement is missing. The arithmetic works, but the application does not prevent a decision based on incomplete data. Controlling an Excel spreadsheet in a GMP laboratory means defining its use, demonstrating relevant behaviour and retaining reconstructable records. No generic template is automatically validated for every laboratory.

1. Separate intended use, template and record

Describe the activity the spreadsheet supports, the decisions it affects, who enters data and who checks them. A staff scheduling table does not have the same impact as a calculation contributing to a QC result. Criticality depends on the process and consequences of error as well as file complexity.

The controlled template contains structure, formulas, rules and the approved version. A completed file records one execution: it must connect inputs, result, author, context and the template used. Releasing a template does not establish the correctness of every subsequent file; reviewing an individual record does not replace template testing.

Define boundaries and dependencies: application and version, numerical settings, calculation mode, add-ins, macros, external links and storage location. Identify hidden sheets, named ranges and cells feeding other formulas. An undeclared dependency can change the result without changing the visible final cell.

2. Turn calculation logic into testable requirements

For each input specify meaning, unit, format, relevant range and whether it is mandatory. Distinguish zero, missing data, values below a limit and text: do not make them equivalent to simplify a formula. Document conversion factors, operation order, retained digits and the applicable rounding rule.

Prepare expected results using a calculation independent of the formula under test. Copying that formula into another cell can reproduce the defect. Break complex logic into steps and also check relative/absolute references, added rows, sorting and imports relevant to use.

For example, the arithmetic mean of 8, 10 and 12 mg/L is 10 mg/L. The illustrative formula =AVERAGE(B2:B4) uses the English function name; local interfaces and syntax may differ. Within a range reference, empty cells are excluded and zeros included. Thus 8, blank, 12 still gives 10: the numerical calculation is consistent, but the test is incomplete if three measurements are required.

3. Test matrix: challenge plausible errors too

This original matrix describes tests to execute in an authorised environment, with acceptance criteria defined beforehand. The example’s arithmetic has been independently verified; it is not execution or validation of an actual Excel file.

Test the actual input channels: typing, pasting and importing can behave differently. Cell colour or a warning is insufficient if the user can still finalise an incomplete result. Retain inputs, actual outcome, differences from expectations, file version, environment, executor and deviation assessment.

4. Govern distribution, access and changes

Distribute the approved template from a controlled source and make its version identifiable. Define who may modify, approve and use it; prevent obsolete copies from being mistaken for the current one. Cell protection helps prevent accidental changes but does not by itself demonstrate security, event attribution or traceability.

Establish how changes to data and logic are detected and reconstructed, how completed files are reviewed, and how approval remains associated with the content examined. A shared password or typed name does not resolve these needs. The digital evidence review checklist can help organise result checking.

Retain input data, result, template version and the context needed to interpret and reconstruct the calculation. Assess external dependencies and functionality absent from a printout. After changes to formulas, macros, application or settings, assess impact and repeat relevant tests before authorised release; merely opening the file successfully is insufficient.

5. Simulated case: the average passes, the record does not

A fictitious template requires three readings in mg/L. In the normal test, 8, 10 and 12 produce 10. In the negative test, the second value is removed: the spreadsheet still displays 10 and permits report completion. The defect concerns the completeness requirement, not the averaging function.

Before use, the responsible owner records the deviation and changes the control so a missing mandatory input prevents finalisation. They repeat the normal and incomplete cases and relevant tests involving zero, text and units, then document the release decision. Entering zero for the missing value would change the mean to approximately 6.67 mg/L and invent an observation.

If distributed versions, access, changes and retention cannot be governed reliably, assess an application with controls suited to the process. The LIMS requirements pathway helps start from the need; changing platforms does not remove the need to demonstrate intended use.

6. Sources and applicability

Sources checked on 2 October 2026. Human medicinal product GMP context; matrix and case are original examples, not a validated spreadsheet or instructions to alter live systems.

• European Commission — GMP Annex 11, revision 1, January 2011, effective 30 June 2011, §§1, 4, 6–7, 9–12: risk, testing and lifecycle controls.
• EMA — GMP/GDP Questions and Answers, Annex 11 section, Q2–Q3, February 2011, consulted online version: spreadsheet controls, formulas and custom code; application clarifications.
• FDA — Data Integrity and Compliance With Drug CGMP, final December 2018, Q1–Q3: relevant data, metadata and records; nonbinding guidance within its scope.
• Microsoft Support, “AVERAGE function” and “Protection and security in Excel”, online documentation consulted for Excel Microsoft 365 and the versions listed by the manufacturer: function behaviour and worksheet protection limits. Technical reference, not GMP certification.