This is a ready-to-use validation protocol for a GxP spreadsheet, the kind of Excel workbook that converts inputs into a result, trend, or decision that affects product quality, patient safety, or a submission. Replace every <<FILL: ...>> placeholder with your own specifics, set your document numbers and dates, and route it through your normal review and approval. A worked filled specimen follows. Confirm each cited regulation against the current source before you rely on it. This content is general educational reference, not legal or regulatory advice.
Approval page
| Field | Entry |
|---|---|
| Protocol title | GxP Spreadsheet Validation, <<FILL: workbook name>> |
| Protocol number | <<FILL: e.g. VAL-SS-009>> |
| Version | <<FILL: e.g. 1.0>> |
| Effective date | <<FILL: date>> |
| Spreadsheet file / version ID | <<FILL: file name + internal version cell value>> |
| GxP risk classification | <<FILL: Low / Medium / High>> |
| Role | Name | Signature | Date |
|---|---|---|---|
| Author (builder) | <<FILL>> | ||
| Independent reviewer (not the builder) | <<FILL>> | ||
| Quality Assurance | <<FILL>> |
1. Objective
Confirm that the spreadsheet <<FILL: NAME>> performs its specified calculations correctly, handles boundary, blank, and error inputs safely, is protected so formulas and constants cannot be altered by users, is under version and access control, and is fit for its intended GxP use.
2. Scope
This protocol covers the released master workbook <<FILL: file name>>, version <<FILL: version>>, used for <<FILL: purpose, e.g. assay % label claim calculation>>. It covers the calculations, the input controls, the protection, and the version/access controls. It does not cover the instrument or system that generates the raw inputs, which is validated separately under <<FILL: reference>>.
3. Spreadsheet description and specification
Record what the spreadsheet does before testing it. Without a specification there is nothing to test against.
| Item | Specification |
|---|---|
| Inputs (cell, name, type, allowed range) | <<FILL: e.g. B4 Sample_Response, positive numeric; B5 Standard_Response, positive numeric>> |
| Constants (cell, value, source) | <<FILL: e.g. Standard_Conc = 10 mg/mL, from CoA>> |
| Calculations (output, equation) | <<FILL: Result_% = (Sample_Response / Standard_Response) x (Standard_Conc / Sample_Conc) x Dilution x 100>> |
| Outputs (cell, format, rounding, expected range) | <<FILL: e.g. D10, numeric, one decimal, typically 90.0 to 110.0>> |
| Error handling required | <<FILL: reject blank/non-numeric; divide-by-zero must error visibly>> |
| Version indicator location | <<FILL: e.g. header cell A1 displays version>> |
4. Prerequisites
- The specification (section 3) is approved.
- The workbook under test is the controlled master at the stated version.
- The independent reviewer is named and is not the builder.
- Test data, including deliberate boundary, blank, and error cases, is prepared and its expected results computed independently (by hand, calculator, or a separate validated tool).
5. Roles
| Role | Responsibility |
|---|---|
| Builder / author | Drafts the specification, builds the workbook, executes author checks. |
| Independent reviewer | Executes formula inspection and protection tests; cannot be the builder. |
| Quality Assurance | Reviews and approves the protocol and report, owns the controlled inventory entry. |
| System / process owner | Holds the protection password under procedure, authorizes changes. |
6. Acceptance criteria
- Every specified formula is confirmed by both independent recalculation and direct formula inspection.
- All boundary, blank, and error cases behave exactly as specified (visible error, not a quietly wrong number).
- Rounding occurs at the specified step and the output matches to the specified precision.
- Every formula and constant cell is locked; only designated input cells are editable; sheet and workbook structure are protected.
- The master is stored read-only under managed access, carries a version identifier visible inside the file, and is under change control.
- Any test failure holds release until the workbook is corrected and the affected cases are re-run.
7. Test cases
7.1 Formula verification by recalculation
Choose cases deliberately: nominal, at each boundary, blank input, divide-by-zero, and the maximum expected dataset size.
| Case ID | Inputs | Independently calculated expected | Spreadsheet result | Pass/Fail | Tester / date |
|---|---|---|---|---|---|
| FV-01 nominal | <<FILL>> | <<FILL>> | |||
| FV-02 boundary low | <<FILL>> | <<FILL>> | |||
| FV-03 boundary high | <<FILL>> | <<FILL>> | |||
| FV-04 blank input | <<FILL: leave a required cell empty>> | Visible error, no number | |||
| FV-05 divide by zero | <<FILL: denominator = 0>> | Visible error | |||
| FV-06 max dataset | <<FILL: full expected row count>> | <<FILL: from separate tool>> |
7.2 Formula verification by inspection
Display the actual cell formulas (formula view or FORMULATEXT) and confirm each matches the specified equation, including cell references, ranges, and operator precedence.
| Case ID | Output cell | Specified equation | Actual formula read from cell | Match? | Tester / date |
|---|---|---|---|---|---|
| FI-01 | <<FILL>> | <<FILL>> | Yes / No | ||
| FI-02 | <<FILL>> | <<FILL>> | Yes / No |
7.3 Blank-cell behavior (called out because it is the silent one)
Confirm how each formula treats an empty cell. Averaging functions ignore a truly empty cell (shrinking the denominator), a cell holding zero is counted, and direct arithmetic treats an empty cell as zero. Test each formula against a blank input and confirm it either handles it correctly or refuses it visibly.
| Case ID | Formula | Blank-input behavior specified | Observed | Pass/Fail |
|---|---|---|---|---|
| BC-01 | <<FILL: e.g. AVERAGE of readings>> | <<FILL: blank must be flagged, not silently dropped>> |
7.4 Locking and protection
| Case ID | Test | Expected | Result | Pass/Fail |
|---|---|---|---|---|
| LP-01 | Attempt to edit a formula cell | Blocked by protection | ||
| LP-02 | Attempt to edit a constant cell | Blocked | ||
| LP-03 | Attempt to insert / delete / reorder / rename a sheet | Blocked by workbook protection | ||
| LP-04 | Enter out-of-range value in an input cell | Rejected by data validation | ||
| LP-05 | Confirm test is run on the working copy, not only the master | Working copy is protected |
7.5 Access and version control
| Case ID | Test | Expected | Result | Pass/Fail |
|---|---|---|---|---|
| AV-01 | Master stored read-only, managed access | Only authorized can modify | ||
| AV-02 | Version identifier visible inside the file | Displays current version | ||
| AV-03 | Change control record exists for this version | Referenced and approved |
8. Deviation handling
Record any test failure or unexpected result as a deviation on the execution record, assess impact, correct the workbook, and re-execute every affected case. Do not adjust an acceptance criterion after seeing a result. See validation test failure management.
| Deviation # | Case affected | Description | Disposition | Re-test result |
|---|---|---|---|---|
<<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
9. Summary and conclusion
Summarize results against acceptance criteria, list deviations and their resolution, and state the conclusion.
Conclusion: <<FILL: The spreadsheet is / is not validated for its intended use at version X>>
| Role | Name | Signature | Date |
|---|---|---|---|
| Author | <<FILL>> | ||
| Independent reviewer | <<FILL>> | ||
| Quality Assurance | <<FILL>> |
10. References
21 CFR Part 11 (electronic records and signatures) and the predicate rules under 21 CFR 210/211. EU GMP Annex 11, Computerised Systems (a calculating spreadsheet is a computerized system). FDA Guidance, Data Integrity and Compliance With Drug CGMP (2018). MHRA GxP Data Integrity Guidance and Definitions (2018), which names spreadsheets explicitly. ISPE GAMP 5 (second edition) for the risk-based category approach.
Confirm the current version of each reference before issue.
11. Revision history
| Version | Date | Author | Summary of change |
|---|---|---|---|
<<FILL: 1.0>> | <<FILL: date>> | <<FILL: author>> | Initial issue. |
Filled specimen
The following shows key results completed for an example % label claim workbook. Numbers are illustrative.
Workbook: ASSAY-LC-CALC v2.0, Medium risk. Purpose: compute % label claim from paired sample/standard responses.
Recalculation:
| Case ID | Inputs | Expected | Spreadsheet | Pass/Fail |
|---|---|---|---|---|
| FV-01 nominal | Resp 0.512/0.500, conc 10/10, dil 1 | 102.4 | 102.4 | Pass |
| FV-02 boundary low | Resp 0.450/0.500, conc 10/10, dil 1 | 90.0 | 90.0 | Pass |
| FV-04 blank input | Sample_Response empty | Visible error | #VALUE! shown, no number | Pass |
| FV-05 divide by zero | Sample_Conc = 0 | Visible error | #DIV/0! shown | Pass |
| FV-06 max dataset | 500 paired rows | 98.7 (separate tool) | 98.7 | Pass |
Inspection FI-01 confirmed cell D10 read =(B4/B5)*(F2/F3)*F4*100, matching the specified equation. Protection LP-01 and LP-03 confirmed formula cells and sheet structure were locked on the working copy, not just the master. One deviation was raised: BC-01 initially averaged a column with occasional blanks using AVERAGE, which silently dropped missing readings; the formula was changed to flag a missing reading, and BC-01 re-ran Pass. Conclusion: validated for intended use at v2.0.
Common inspection findings this protocol prevents
- A spreadsheet in GxP use with no specification, so “it looks right” is the only evidence.
- Formulas verified only for nominal inputs, so a boundary or blank-cell error ships and produces plausible wrong numbers for months.
- The master locked and approved while the working copy analysts fill in has protection off and editable formulas.
- The builder validating their own workbook, with no independent review of the formulas.
- The “validated” spreadsheet existing in a dozen emailed copies with no version anyone can trace to a result.
How to adapt this protocol
- Write the specification (section 3) first; the tests are meaningless without it.
- Choose test cases that break things: zero, blank, negative where it should not occur, boundaries, and the maximum dataset.
- Run the protection tests on the copy people actually use, not the pristine template.
- If the workbook retains records, address Part 11 audit-trail and signature expectations for the saved files, or make the spreadsheet transient with the record of record in a validated system.
- If it is High risk (macros, retained records), reconsider whether it should be a validated application instead. See infrastructure qualification and spreadsheet validation.