Spreadsheet Validation

Avoid or Validate

Spreadsheets are deployed across the pharmaceutical industry. Spreadsheets are intuitive, flexible, and transportable making them the medium of choice to perform tasks such as:

Routine analytical or formulation calculations
Instrument and equipment asset management – logs, calibrations and check events
Unique ID generation for documents such as SOPs, methods, investigations…
Training records
Tabular and graphical reporting

The very usefulness of spreadsheets can produce a compliance nightmare when un-validated spreadsheets are utilized to generate regulated reports or make scientific decisions.

Growing companies often struggle with two difficult options

OPTION 01

Remove spreadsheets from the compliance sphere. This is more difficult than it sounds since, the use of spreadsheets must be replaced by some validated process, such as Excel Spreadsheet Validation. The replacement process can be a manual operating procedure which is often cumbersome and less efficient. It is also possible to replace spreadsheets with targeted IT solutions. The IT solutions however are never as flexible as a spreadsheet and must be validated (no small task).

Option 1 above requires a significant budget to purchase one or more IT solutions (often LIMS). In addition, the company processes must be modified to fit the purchased solution, and employees must be trained to operate in the new environment. A truism regarding IT solutions like LIMS is worth repeating: “IT solutions are a lot like concrete, they can be configured in many shapes but once deployed are almost impossible to change.” The point is that unlike spreadsheets which can be updated as your company grows and changes, IT solutions are often antiquated even before they are deployed.

OPTION 02

Validate all spreadsheets which may be used for regulated reports or decisions. Companies often find upwards of 200 spreadsheets which need to be validated and maintained. A daunting endeavor.

Option 2 requires validating a large suite of spreadsheets. In addition, the spreadsheets must be maintained in a validated state and must be capable of rapid updates to support change within your company. Attacking this problem one spreadsheet at a time is cost and time prohibitive. To be successful, a company wide spreadsheet validation solution is required.

Solution Overview

The diagram below illustrates a typical deployment for compliant spreadsheets. Templates are maintained in a controlled state. To run the spreadsheet process, a copy of the template is made which we will refer to as the “Document”

Templates

Templates are the basic building blocks of a managed spreadsheet system. Templates are managed within a controlled, secure environment which provides lifecycle/status control and supports spreadsheet versioning. The diagram indicates that templates progress through a defined lifecycle. Templates are available for use in either “Active” or “Verified” status and are placed in “Inactive” status when superseded or when the work process is no longer required. Template verification is an optional step where template calculations and automation are documented and tested to produce a “Verified” template. By electing to verify template, a company avoids lengthy review of individual documents. The decision on whether to verify a template is mainly determined by the frequency of template use and spreadsheet complexity. In a compliant environment, verification of spreadsheet content is not optional and must be done at either up front on the template or at the review stage of each document created from the template.

Documents

Documents are copies of templates created to execute a process such as to test a group of samples. Like templates, documents are managed within a controlled, secure environment which provides lifecycle/status control. In addition, changes to documents must be recorded in an audit trail and author/reviewer/approver signatures must be applied.

Selecting a Solution

There are many potential solutions which can be harnessed to provide the controlled environments required for templates and documents. Three general categories are provided here:

From Scratch – combination of operating procedures and access controlled file shares
Tool Kit – file share software i.e.: SharePoint, Dropbox…
Vendor Solution – targeted spreadsheet compliance solution

The “Vendor Solution” is highly recommended unless your company has a wealth of experience in this area and the time/ energy to invest. The problem with in-house development, even with an advanced tool kit such as SharePoint is the flexibility inherent in spreadsheets. The principles of file security, control, and 21 CFR Part 11 are well known and straight forward to implement, but taming and bringing into compliance the wide-ranging functionality of spreadsheets takes experience. The goal is to bring your spreadsheets into compliance, not to start an open-ended research project.

Validation

Your spreadsheet validation should be separated into two components. The first validation component is the environment in which Templates and Documents are secured and maintained. The second component is individual validation packages for each spreadsheet. It is important to separate these components rather than execute a single validation exercise because new spreadsheets and revisions to existing spreadsheets must be expected as your business grows. Handling spreadsheets as modular add-ins will make maintenance of your solution easier.

Validating the Spreadsheet Environment

The required industry standard validation documentation will be the same regardless of the solution you picked for your spreadsheet environment. If you picked the Vendor Solution, your task is simplified by the documentation set provided by the vendor and denoted by an asterisk.

Item	Description
Validation Master Plan
User Requirements	High Level Business Requirements
System Requirements Specification*	High Level Solution Requirements
Design Qualification	Comparison of the User Requirements with the System Requirements
Functional Requirements Specification *	How the solution implements the system requirements
Design Specification *	Technical system definition
Installation Qualification *	Documentation of successful installation
Operational Qualification *	Report summarizing the testing executed to prove the solution functionality works as expected.
Risk Assessment	Consider how the solution fits into the business environment and potential risks which may arise.
Performance Qualification	Report summarizing testing performed which proves the solution operates as expected in the target environment.
Summary Report and Release Statement	Report summarizing the validation process and a statement of release for production use

Validating Individual Spreadsheets

Spreadsheet validation requirements are defined by GAMP 5 “A Risk-Based Approach to Compliant GxP Computerized Systems”. GAMP categorizes spreadsheets into 5 categories depending on impact and complexity. The documentation required for each category increases as the impact and/or complexity increases. To avoid the complexity of managing different documentation sets across your portfolio, it is possible to assign the highest category of GAMP risk across all templates. Categorizing individual templates can avoid full documentations sets for simpler templates BUT since simple templates are easy to document the savings are not large and compensated by a single well-defined process.

GMP Category 5 documentation includes:

Item	Description
Validation Plan	A single plan should be created to cover all spreadsheets both new and revised. The plan should also define the template lifecycle.
User/Functional/Design Requirements	Combined document delineating high level requirements, functionality employed to implement the requirements and design considerations. The document should include calculations, alert criteria, number of decimals displayed, automation deployed, data validation options, and significant format.
Traceability	Between requirements and testing
Test Plan	Defined testing and predetermined acceptance criteria
Test Report	Documentation of testing performed
Validation Report	Summarize the validation exercise. Include a summarization of previous template versions and testing performed on each version.

Spreadsheet Revisions

The template revision process should be defined in the “Validation Plan” including a risk based strategy for regression testing.

A typical documentation set is defined below:

Item	Description
Required Changes and Risk Assessment	Define the required changes and assess against criteria defined in the “Validation Plan”
Test Plan	Defined testing and predetermined acceptance criteria
Test Report	Documentation of testing performed
Traceability	Between requirements and testing
Validation Report	Summarize the validation exercise. Include a summarization of previous template versions and testing performed on each version.

Contact
Us

Reach out to Schedule a Meeting and get more information about how SciCord can fit into your lab

What Our Users Say

Don’t take our word for it.
We exceed our client’s demands everyday to make their research and discovery process simpler and more efficient.

“

This is by far the best value in science software (or anything else in science, really) that we’ve ever experienced. Other solutions in this price range had a fraction of the features, and those with the features cost 3x – 10x more. We’re very happy customers.

”

Josh Guyer,
Senior Pharmaceutical Scientist

Platform and Features

Main Features

More

Solutions

See our Customer’s Success

Resources

Case Studies

Key Items

See our Articles

Spreadsheet Validation

Avoid or Validate

Growing companies often struggle with two difficult options