IT systems · Practical guide

Validating Excel for GxP: formulas, templates and evidence

An Excel workbook may be a convenient aid or a decisive part of a GMP process. Define its use, test the calculations and distinguish the controlled template from the completed records. The examples concern GMP. Other GxP uses require a separate assessment.

Table of contents

At a glance

What does it mean to validate Excel for GMP use?

Excel validation documents that a specific spreadsheet application is fit for its intended GMP use. Assess the approved template, calculations, inputs, protections and associated workflow in light of the data and decisions involved. A workbook can be critical even without macros. Worksheet protection alone does not demonstrate fitness for use.

  • Assess the intended use and potential consequences of errors, not merely file format or macro issues.
  • Separate released template, completed record and technical environment.
  • Also verify blank values, incorrect units, input limits, rounding, and copied data.
  • Link accurate calculations with version control, traceability, and controlled operation.

When is an Excel spreadsheet considered GMP-related?

Begin with the decision that supports a table: does it calculate a single batch weight, evaluate a test result, or document a quality-related activity? Then assess its impact on the process. An internal travel expense summary serves a different purpose. The fact that both files are opened in Excel does not answer the technical question.

EU-GMP Annex 11 applies to computerised systems used in GMP-compliant activities and establishes the validation scope based on risk assessment. The EMA further specifies for spreadsheets that individually programmed code, such as VBA, requires at least one validation under Annex 11 in the relevant GMP context; formulas and algorithms must be verified for accuracy.

For macro-free templates, the focus lies on the verified accuracy of formulas, appropriate input controls, and template navigation; the specific application determines the extent of additional validation evidence.

First, define the purpose, responsible party, users, relevant data, and consequences of a failure. This prevents two critical errors: overlooking a critical calculation due to missing macros, or treating every office spreadsheet with the same documentation package. A one-size-fits-all GAMP category for all Excel files is not a reliable basis for decision-making.

Sources: [1] [2]

Review, documentation, and Excel environment to be considered separately

The validated template contains the intended calculation logic. The generated record further includes data from a specific process instance. Approval of the template does not automatically validate subsequent entered values. Conversely, a correct individual result does not confirm that the template properly processes all intended scenarios.

Define where the released master template is stored, how users obtain a working copy, and how the version of the working copy remains identifiable. Reused result files may inadvertently inherit outdated inputs. Therefore, a controlled starting state is advisable for initiating a new process.

Three Levels for Planning
LevelQuestion to test
Template Are formulas, input fields, and permitted changes clearly defined?
Record Are entries, results, procedural context, and the version of the template used traceable?
Environment Which actual version of Excel, operating mode, add-ins, connections, and storage locations are in use?

Sources: [2] [3]

Formulate requirements that can actually be verified

"The statement 'the table calculates correctly' is too vague. Describe inputs with unit and value range, calculation method, output unit, rounding rule, and handling of incomplete data. Specify permitted user actions. The department determines the technical rules; QA and IT support with evidence, control, and operational oversight.

An example requirement is: “The template calculates a concentration from the stock solution, aliquot and final volume. A missing or non-positive final volume must not produce a usable result.” This gives you specific tests. Decide from the intended process whether other inputs may be zero.

Prioritize errors based on their impact: A unit error can alter results by a factor. A misaligned cell reference may still produce plausible-looking numbers. An unobserved formula swap could affect numerous subsequent entries. Specifically evaluate such mechanisms; a successful normal case does not cover them.

Sources: [1]

Test formulas: A transparent calculation example

Example: no manufacturing instruction present – B2 contains the concentration of the master solution in mg/mL, C2 the volume of aliquot taken in mL, and D2 the final volume in mL. The calculation formula is =B2*C2/D2. With 20 mg/mL, 2.5 mL, and 50 mL, the result is 1 mg/mL. The final volume refers to the total volume, not the additional volume of solvent added.

Determine expected values independently of the file being reviewed, for example through transparent manual calculation. Do not simply accept the output as the expected result. Document inputs, formula version, expected value, actual value, and evaluation. The calculation formula alone does not constitute input validation.

Example: Test cases for concentration calculation
Inputs B2 / C2 / D2ExpectationPurpose of inspection
20 / 2.5 / 50 1 mg/mL Normal case
20 / 1 / 100 0.2 mg/mL Another relationship
20 / 2.5 / 0 No usable result; error clearly identifiable Invalid denominator
20 / 2.5 / empty Incomplete entry will be detected Required field
20 / 2500 / 50, aliquot actually in μL Unit errors prevented or detected Plausible value with incorrect unit

Sources: [1]

Check input limits also when pasting and copying

For an allowed range of 1 to 100, for example, 0.99, 1, 100 and 100.01 are included in the test. Complete text, empty cells and non-allowed negative values. This sample limit must come from the business requirement; it is not a general GMP standard.

Excel provides data validation for value ranges and data types. Microsoft distinguishes between a stop and warnings, where a stop prevents input while warnings allow entry to continue. Additionally, invalid values may be accepted during copying or filling without the usual notification. Therefore, verify the actual input workflow, including imports and copying from other files.

Test blank values separately. In English-language Excel, =AVERAGE(A1:A3) returns 15 for 10, 20 and a genuinely empty third cell. If the third cell contains 0, the average is 10. If three measurements are required, a calculated average alone does not show that all three were entered. The template needs a completeness check.

Sources: [6] [7] [8]

Explicitly define rounding and limit value comparison

Displayed and calculated values can differ. Excel displays 1.0049 as 1.00 in a two-decimal number format unless precision as displayed is enabled. The unrounded value still exceeds an upper limit of 1.00. If the workbook applies =ROUND(B2,2) before comparing the values, the value used in the comparison is 1.00.

Which variant is correct depends on the professionally approved evaluation rule. Define it prior to testing. Additionally, investigate values immediately below, at, and above the limit. Rounding intermediate results may lead to a different conclusion than rounding the final result once.

Microsoft also outlines limitations of floating-point arithmetic. The option "Accuracy as displayed" alters stored values and may result in permanent loss of decimal places. Therefore, this option should not be casually enabled to correct an ambiguous limit logic. Relevant calculation settings should be documented jointly with the template.

Sources: [9] [10]

Combine leaflet protection, access rights, and change control

A logical structure separates approved input cells from formula cells. Sheet protection helps prevent unintended modifications to locked cells. Microsoft explicitly does not classify it as a security feature. Therefore, a password on a sheet does not replace a concept for access control to templates and records.

Verify separately who is authorized to modify the master copy, create work copies, correct records, or delete files. A meaningful test uses the actual permissions of a typical user. It also assesses whether the controlled version can be replaced by an arbitrary local copy.

Plan the traceability of required changes. A version history is not automatically an appropriate audit trail. The key factors are the available information and its reliability. If relevant changes within the intended process cannot be adequately controlled or reconstructed, an alternative technical solution should be evaluated.

What technical and organizational controls are required and sufficient for a specific documentation process is justified on a case-by-case basis. The use of Excel alone does not imply a general audit trail obligation nor does it constitute a release from applicable requirements.

Sources: [5] [1] [4]

Which records remain after the calculation?

Define the complete output of a process: the inputs used, associated units, identifiable template, result, and required review or verification information. For a concentration example, an isolated value "1" would be largely incomprehensible. Process context and calculation basis ensure professional and technical traceability.

The FDA distinguishes in its Guidance on Drug CGMP between static and dynamic records. For copies, the content, meaning, and required context must be preserved. Therefore, a PDF containing visible results does not automatically demonstrate that all necessary electronic information has been retained. Similarly, PDFs are not inherently unsuitable: the specific record-keeping process is critical.

Review, at a closed example dataset, whether an authorized reviewer can later reconstruct the process. Also test the ability to locate and retrieve an archived record. Retention periods are derived from the currently applicable regulations and document type; a single, general retention period for all Excel files would be misleading.

Sources: [4] [3]

Further reading:

Evaluation and implementation of template changes and Excel updates

A new formula, additional rows, modified units, or another add-in may impact the validated process. Describe the change, the affected functions, and the resulting assessments. A cosmetic title change and a modified cell reference require distinct evaluations.

Example: An evaluation is extended from ten to twelve input lines. Verify that sums, averages, completeness checks, and output actually include both new lines. A suitable test coverage intentionally introduces differing values in the additional lines. Thus, a too-short formula range is not concealed behind similar results.

Switching between desktop and browser, an office update or a new import path is also part of the impact assessment. This does not result in an automatic complete repeat test after each change. A well-founded selection of relevant regression tests and a clear release of the new template make the decision comprehensible.

Sources: [1]

Further reading:

Build evidence and address existing gaps

A clear and concise validation record links intended use, requirements, risk assessment, documentation description, testing, deviations, and release. It references the identifiable version of the file. Screenshots complement this chain; a collection of unlabelled images neither demonstrates the tested version nor confirms acceptance criteria.

With tables already used, the work begins with an inventory. Secure the current status, evaluate existing evidence and prioritize gaps. If errors are found, the possible influence on already generated records is to be investigated in addition to the correction. A today's signature does not replace any previously unexecuted examination.

The following checklist supports the initiation phase. It is a planning proposal and not a universal release form.

  • Purpose, responsible party, and professional boundaries are defined.
  • Master templates and records possess unique identities.
  • Formulas, units, and rounding rules are clearly described.
  • In normal conditions, input limits and relevant error scenarios are verified.
  • Copies, permissions, and required change justifications have been considered.
  • Open findings, usage prerequisites, and release are documented.
  • Changes, storage, retention, and responsibilities within operations are defined.

Sources: [1] [3]

Project experience and collaboration

Test the workbook against its intended task

With the people who use a workbook, we trace inputs, formulas, error messages and the decision made from the result. We decide together whether targeted controls on the existing file suffice or whether to build a controlled template. Tests should show what happens with invalid inputs, altered formulas or an incomplete export. At handover, we establish who owns the workbook and how later changes will be managed.

Frequent Questions About Excel Templates and Their Validation

Must every Excel file in a pharmaceutical company be validated?

No. Assess the intended use of the file and its impact on GxP-related data or decisions. A quality-relevant calculation requires a different evaluation than a purely commercial planning approach. Ensure that the justified demarcation of a non-relevant file is clearly documented and traceable.

Sources: [1] [2]

Are spreadsheets without VBA exempt from validation?

No. Even a single formula can alter a quality-related outcome. The EMA requires correctness validation for formulas and algorithms and explicitly identifies individual code as a validation case within the GMP context. This does not imply a blanket exemption for macro-free files nor a GxP obligation for every VBA file, regardless of its usage.

Sources: [2]

Must Microsoft Excel be fully revalidated as a standalone product?

No. Base the validation on the intended table application and its relevant dependencies. This may include Excel version, required functions, add-ins, and storage requirements. Manufacturer documentation does not substitute for verification of your own calculation logic.

Sources: [1]

Must each completed work copy be revalidated?

No, as long as approved logic and defined usage conditions remain unchanged. Each individual record will be reviewed according to the intended work and testing procedures. If a user modifies formulas, structure, or specified usage, such changes must be evaluated; prior template approval does not automatically cover such modifications.

Sources: [1] [3]

Is a password sufficient for sheet protection?

No. Sheet protection prevents certain operations but is not a security feature according to Microsoft. Additionally, assess file access, template distribution, modifications, and audit trails. Check with standard user rights to determine which actions are actually feasible. The measures must align with the identified risk and the intended workflow.

Sources: [5]

Does the Excel data verification prevent any erroneous entry?

No. Depending on the configured alert, users may proceed despite a warning. Microsoft also identifies copying and filling as methods in which the standard alert may be suppressed. Test the specifically permitted workarounds and, if necessary, implement an appropriate completeness or plausibility check prior to results usage.

Sources: [6] [7]

Why must rounding be defined prior to a limit comparison?

Since a display with a limited number of decimal places may convey a different impression than the actual stored computed value, the comparison must be determined professionally—either before or after a predefined rounding. Values at the threshold must be evaluated directly; visual representation alone does not determine assessment.

Sources: [9] [10]

Can the Excel file be deleted after the PDF export?

A PDF export does not constitute approval for the deletion of the Excel file. Clarify which original information and contextual data must be retained and whether the selected copy inherits this retention. A PDF containing final values may be incomplete for this purpose. Deletions should be processed through an approved retention procedure, not included in individual cleanup routines.

Sources: [4] [3]

What to do if a table that has been used for years has no proof of testing?

Capture the current status and assess the significance and extent of the gap. Evaluate the relevant logic and existing records according to a justified approach. When findings are identified, investigate the potential impact on prior results. Document today's assessments with their actual date and scope.

Sources: [1] [4]

When is another application more appropriate than Excel?

When required access controls, change tracking, or complex workflows cannot be reliably managed with the selected spreadsheet solution, a more detailed evaluation is warranted. High concurrent user activity or a large number of linked files serves as a trigger for such assessment. Crucially, the specific solution approach must demonstrably meet its requirements—not the number of worksheets it contains.

Sources: [1]

Sources and review date

Primary sources tested on October 6, 2026. Regulatory focus: GMP and drug-CGMP. Microsoft documents explain individual functions, no regulatory suitability of a specific file. The test cases and the checklist are their own planning aids. Functional behavior is to be confirmed in the used version and operating mode.

  1. European Commission: EU GMP Annex 11, Computerised Systems, January 2011
  2. EMA · GMP/GDP Questions and Answers, Annex 11 questions on spreadsheets
  3. European Commission: EU GMP Chapter 4, Documentation, January 2011
  4. FDA: Data Integrity and Compliance With Drug CGMP, finale Guidance, December 2018
  5. Microsoft: Protect a worksheet
  6. Microsoft: More on data validation
  7. Microsoft: Display or hide circles around invalid data
  8. Microsoft: AVERAGE function
  9. Microsoft: ROUND
  10. Microsoft: Floating-point arithmetic may give inaccurate result in Excel

Sources checked: .

Daniel Herrmann, Daniel Herrmann Consulting

Daniel Herrmann Consulting

Support for your GMP-related Excel applications

Daniel Herrmann Consulting supports with inventory assessment, prioritization of evidence gaps, and agreed documentation and testing activities. Technical evaluation rules and release decisions are aligned with your responsible parties within the existing quality process.

  • Computer system validation →

    Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

Send a project enquiry Customer testimonials →