Spreadsheet Validation

Spreadsheets have become commonly used within a wide range of applications within the pharmaceutical and biotechnology industries. These range from the management of documentation lists through to complex algorithms used to support batch release.

This post provides a description of a risk based approach to the validation of spreadsheets. As with all posts comments are welcome.
The approach to validating spreadsheets should be the same as all other software processes, requirements and planning, specification and design, verification and release. (see article software validation)

TO keep spreadsheet validation processes lean I recommend that templates are developed for each of the deliverables alongside a Procedure / Validation Plan and a well defined Standard.

Requirements and Planning

The level of requirements required for a spreadsheet will be different to that of an automation control system. However the user should define the basic requirements.
Company standards should be developed for formatting, storage and security, and therefore only the specifics requirements of the spreadsheet need to be documented. This includes:

• All calculations and level of accuracy
• Any logic based decisions (if statements)
• Trending functions (charts to be used)
• Special functions

Generally a spreadsheet will be used to replace a manual process. The user requirements should reflect this process.

An individual validation plan should not be required for a spreadsheet (unless extremely complex). An SOP or VP for spreadsheet should describe the process fully.

Risk Assessment

The planning phase should also include a risk assessment of the spreadsheet, considering impact of use and complexity. The following provides guidance


GMP Impact

High Impact –used to generate GMP release or study data or make GMP processing decisions

Medium Impact –used to generate data supporting GMP release data (eg assay trending)

Low Impact – used to generate other GMP data (e.g. Schedules and lists).

Complexity

High Complexity – uses calculation macros, look-up functions, pivot tables, conditional formatting.

Medium Complexity –uses nested formulae, logic, sumif functions

Low Complexity –uses standard simple functions, basic mathematical functions simple workbook navigation macros.

The risk matrix provides a priority to support the approach to validation.

High Priority Records (Red) full lifecycle validation should be performed (treat as software in accordance with site CSV procedures.

Medium Priority (Yellow) Apply specification and design and verification phases detailed below..

Low Priority (Green) Limited validation required simple user requirements and functional testing (Operational Qualification).

Specification and Design

The specification can consist of the site standard for spreadsheet design with a marked up copy of the completed spreadsheet.

The design should be in accordance with the standards. This reduces the amount of effort during the verification phase.

The spreadsheet design standard is a subject that I will cover in more detail in a future post. It should however consider the approach for

• formatting
• calculations
• spreadsheet storage and distribution
• application of electronic records; electronic signatures
• version control

A design review should not be required.

Spreadsheet Verification

Formal verification should be performed. This can either be in a combined document or separate Installation Qualification (IQ) and Operational Qualification (OQ).

Spreadsheet Installation Qualification (IQ)
Installation Qualification is generally limited to recording the version of the spreadsheet package (e.g. MS Excel version), the version of the spreadsheet and the location of the spreadsheet.
Spreadsheet Operational Qualification (OQ)
The OQ should be a number of functional tests against a standard input range of data to verify that the results are accurate and the required precision is met.

“IF” logic statements used to make Pass / Fail decisions then testing around the decision point should be made, to ensure and to document that the decision is made accurately.

Acceptance and Release

Following the successful completion of the testing the spreadsheet should be transferred to the user. Operational compliance should be maintained and consideration should be made to software patches and application upgrades.

Periodic Review

FDS 21 CFR § 211.68(b) states:
Input to and output from the computer or related system of formulas or other records or data shall be checked for accuracy. The degree and frequency of input/output verification shall be based on the complexity and reliability of the computer or related system.

A method of periodic evaluation is required. This can be run concurrent with normal use of the spreadsheet that at predefined intervals the calculations are independently verified.