Example SOP for QC Spreadsheet Validation

In short: This illustrative SOP template describes a risk-based lifecycle for QC spreadsheets used in GMP work. Define the intended use, protect data and formulas, test calculations and error handling, and control approved versions and changes. Adapt the controls to the system, records and risks at the site before approval.

1. Purpose

To describe how a site can specify, develop or configure, verify, approve, use, maintain and retire spreadsheets used for GMP calculations. This is an example SOP, not a validated workbook or a substitute for the site’s approved computerised-system and data-integrity procedures.

2. Scope

This template applies to spreadsheets used to enter, calculate, review, store or print QC data where errors could affect a GMP decision or record. First determine the spreadsheet’s intended use and whether the electronic workbook, its printout, or both are the official record. Regulatory applicability depends on the intended use and records maintained; do not assume every spreadsheet has the same controls. FDA’s Part 11 guidance explains the scope in relation to electronic records required by predicate rules.

For broader system controls, see SOP for computerised system introduction and validation and SOP on data integrity. For general data-integrity principles, see What is data integrity in the pharmaceutical industry.

3. Responsibilities

• Process owner / QC: defines intended use, required calculations, data inputs, outputs, review and retention; ensures users are trained.
• System owner / IT: supports approved configuration, access, backup, recovery and technical maintenance. Assign responsibilities to qualified personnel and document them.
• QA: reviews risk-based control decisions, validation evidence, deviations, changes and approval for routine use under the site’s quality system.
• Users and reviewers: use the current approved version, enter and review data as instructed, protect records, and report errors or unexpected behaviour.

4. Procedure

4.1 Inventory, intended use and risk assessment

1. Assign an identifier and record the spreadsheet’s owner, location, version, status and GMP function in a controlled inventory.
2. Document its intended use, users, data inputs and sources, formulas, outputs, interfaces, reports, record-retention needs and the decision it supports. Identify whether it is a standalone workbook or part of a wider computerised system.
3. Assess risks to product quality, patient safety and data integrity. Consider calculation complexity, input criticality, error detectability, access and change risks, reliance on the output, and whether the electronic record or a printout is relied upon. Use the assessment to justify the extent of verification, controls and documentation.
4. Define user requirements and acceptance criteria before testing. Requirements should be clear enough to trace to design choices and test evidence.

EudraLex Annex 11 says risk management should be applied through the system lifecycle and that the extent of validation and data-integrity controls should be based on a justified, documented risk assessment. It also calls for validation evidence, appropriate test scenarios, and controlled access. These principles do not prescribe a single Excel layout or colour scheme.

4.2 Preparation and configuration

1. Build or configure the workbook from approved procedures, specifications and calculation requirements. Record the software/platform and relevant configuration needed to identify the workbook’s operating environment.
2. Use clear labels and controlled units. Identify required product, batch, sample, method, analyst, date, source-data references, reviewer, version and status fields according to intended use.
3. Document formulas, decision logic, rounding, significant figures, limits and conditional formatting. Protect formula cells and distinguish input cells in a way that is usable and accessible, but do not treat cell locking or colour alone as security or validation.
4. Provide appropriate error handling and prevent or flag invalid, missing, out-of-range or inconsistent inputs where practical. Assess copy/paste, blank cells, boundary values, hidden rows/columns, linked files, macros, external connections and spreadsheet settings where relevant.
5. Store the working version in a controlled location with access, backup and recovery arrangements appropriate to the risk. Keep development and routine-use copies clearly identified; prevent an uncontrolled draft from being mistaken for the approved version.

4.3 Verification and approval

1. Execute and document tests against approved requirements and acceptance criteria. Test each relevant formula and decision rule using representative values, boundary conditions, invalid or missing inputs, and expected error cases. Use an independently prepared expected result or other justified method for comparison.
2. Verify that input and output fields, units, rounding, specifications, warnings, printed reports and record identifiers function as intended. Confirm that data remain attributable and legible and that relevant source data and calculation results can be reconstructed.
3. Record the tester, date, workbook identifier and version, test case, input, expected result, actual result, pass/fail, evidence and any deviation. Investigate failures, correct the workbook under change control, and repeat affected tests.
4. Have an independent, qualified reviewer assess the requirements, risk assessment, workbook configuration, test evidence and unresolved deviations. QA and the process owner approve routine use according to the site’s procedures.
5. Keep validation records with the controlled system documentation. See Validation master plan for site-level validation context.

4.4 Access, use and record review

1. Restrict access according to assigned roles and responsibilities. Use individual accounts or other controls that can identify users where appropriate to the system and intended record. Control administrative permissions separately from routine use.
2. Use only the approved version from its controlled location. Before entering data, confirm the workbook identity and version. Record source-data references and required sample, product and batch details.
3. Review inputs, formulas and outputs according to the approved procedure and documented risk. A second-person check or a validated electronic check may be appropriate for critical manually entered data; justify the control in the risk assessment.
4. Preserve original data, changes and review evidence as required for the official record. If the workbook is the electronic record, protect it against unauthorised changes and ensure retention, retrieval, backup and recovery are addressed. If a printout is the official record, define how it is verified, signed or otherwise reviewed, linked to source data and retained; a printout does not automatically make the electronic workbook irrelevant.
5. Report calculation errors, unexpected results, access issues or suspected data-integrity events promptly under the site’s deviation process. See SOP on handling incidents and deviations.

4.5 Change control, periodic review and retirement

1. Control changes to formulas, limits, layout, links, macros, software environment, access, storage or intended use. Assess impact and risk before implementation; document approval, testing, version update and release of the changed workbook.
2. Archive superseded versions and associated records so that they cannot be used accidentally and remain retrievable for the applicable retention period. Maintain an accurate inventory and current approved copy.
3. Review the spreadsheet periodically, and after relevant incidents or system changes, to confirm its continued suitability, performance, security and validated state. Define the review approach and interval using the site’s risk assessment.
4. At retirement, withdraw routine access, preserve required records and document final status in the inventory.

5. Source-template practices for Admin verification

These are example in-house criteria copied from the imported template for review. They are not regulatory requirements and should only be adopted if justified and approved for the site’s systems and records.

• The source says QC spreadsheets should be prepared on a QC laboratory computer from the current specification, use an A4 page layout, and include a company logo, centred title, file version and saved path in headers/footers. Example in-house criterion (not a regulatory requirement): the layout and traceability fields required depend on the intended use. A4 layout, a logo or a displayed file path are presentation and traceability aids only; they are not validation or access control.
• The source uses yellow fill for variable inputs, green for results, black font, locked calculation cells, worksheet protection and a read-only open password. Example in-house criterion: define the visual conventions (for example, a colour for input cells) in the controlled procedure, keep them usable for colour-blind users (add labels or borders, not colour alone) and verify them during testing. Cell locking and worksheet or open passwords only reduce accidental changes; they are weak security. Do not rely on them as the sole access or data-integrity control. Assess the protection against the site’s security requirements (EU GMP Annex 11, 2011, clause 12 on security) and combine it with controlled storage, access rights and review.
• The source assigns IT custody of protection passwords, allows analysts an open password, limits routine use to a single approved version, and uses separate validation and routine folders. Correction: shared open passwords do not identify individuals. Where the workbook is a GMP record, restrict access by individual accounts and defined roles on the controlled location (Annex 11, 2011, clause 12), keep protection-password custody documented with a controlled hand-over (not held by a single person), control the approved version and separate development/validation copies from the routine-use copy (clause 10, change and configuration management), and provide regular backup and tested recovery (clause 7, data storage).
• The source calls for an analyst to repeat calculations manually with a handheld calculator and for QC to compare the result with the workbook. Example in-house criterion: a manual recalculation can be used as an independent check of critical results, but it is not assumed to detect all errors. Justify it in the risk assessment, define it in a controlled test during validation and a routine review approach, and use an independent reviewer (Annex 11, 2011, clause 6 calls for an additional accuracy check on critical data entered manually).
• The source specifies an internal file naming scheme, product-specific shared workbooks, a central network location, and Annexures I and II for verification and inventory. Example in-house criterion: use the site’s own controlled identifiers, storage location and workbook reuse strategy, and controlled forms or inventory records that are currently approved. Remove obsolete paths and annexure references from this template and replace them with the site’s approved current records.
• The source says changes require QA approval and change control, and obsolete copies should be archived and restricted from use. Align this with the site quality system: assess, test and approve changes under change control before release (Annex 11, 2011, clause 10); archive superseded versions and records so they stay accessible, readable and intact for the applicable retention period (clause 17, archiving); withdraw obsolete versions from routine use while keeping them retrievable. Retention periods follow the site’s record-retention procedure and applicable regulations.

6. Abbreviations

• GMP: Good Manufacturing Practice
• IT: Information Technology
• QC: Quality Control
• QA: Quality Assurance
• URS: User Requirements Specification

References

• European Commission, EudraLex Volume 4, EU Guidelines for Good Manufacturing Practice for Medicinal Products for Human and Veterinary Use, Annex 11: Computerised Systems, revision 1 (January 2011; effective 30 June 2011). Official Annex 11 PDF
• US Food and Drug Administration (FDA), Part 11, Electronic Records; Electronic Signatures — Scope and Application, Guidance for Industry (August 2003). Official FDA guidance