Validation of Excel Calculation Sheets in Pharmaceuticals | Complete Step by Step GMP Guide

The pharmaceutical industry relies on Microsoft Excel very much for several GMP related activities such as calculating assay results, generating calibration curves, storing stability data and performing various other types of GMP related calculations. Although Excel provides flexibility and speed but it can also introduce compliance risks, if not appropriately validated.

Regulatory authorities including the USFDA, MHRA and World Health Organization (WHO) expect that organizations using Microsoft Excel to create spreadsheets in GMP facilities classify these spreadsheets as a GxP relevant computerized system and subsequently validate these spreadsheets for accuracy, dependability and data integrity.

This article provides an overview of what it means to validate a spreadsheet using Excel, why it is important for pharmaceutical companies and how to validate an excel spreadsheet in a manner that is effective for pharmaceutical operations.

What Is Excel Sheet Validation?

Validation of Excel sheets is the procedure used to confirm the functionality of an Excel sheet meets the expectations for accurate calculations without errors and comply with regulatory requirements for quality system.

Validation provides documented proof for an Excel sheet that it correctly completes calculations, protected from unauthorized changes to its functions, provides accurate data input and traceability and holds to the requirements set forth in 21 CFR Part 11 and Annex 11 regarding electronic data and data integrity.

Simply stated, Excel sheet validation indicates that the numbers you calculate are correct, reproducible and secured from being manipulated.

The Importance of Validating Excel

Excel is a powerful tool, however Excel can be a source of error if not properly used. For example, a small formula error In Excel could produce incorrect results for the pharmaceutical industry, this could lead to several issues with a product release, a validated essay or compliance with regulatory guidelines.

Regulatory Expectations

USFDA 21 CFR Part 11 - This regulatory requirement states that any computer system developed to generate, maintain or utilize GMP data must be validated to ensure it produces accurate, consistent and reliable information.
EU-GMP Annex 11 - This regulatory guidance states that businesses must validate any spreadsheet that generates and stores GxP data.
WHO TRS 1019 Annex 4 – This regulatory guidance states that you must validate any software being used for recording, calculating or controlling your data.

In summary, any Excel sheet being used to create, calculate or report GxP data must be validated.

When to Validate an Excel Spreadsheet

Whenever an Excel spreadsheet is used in one of these ways, validation should be done.

1. Analytical Assay, Dissolution, Content uniformity results
2. Calibration and developing standard curves
3. Stability trending and expiration projections
4. Equipment qualification data
5. Cleaning validation data calculations
6. Batch manufacturing, packaging calculations for product records
7. Records impacting the decision to release products or quality of the product

Spreadsheets used for non GxP administrative purposes like scheduling, HR tracking etc. do not require validation.

Validation Approach

Excel validation follows the same process used for all other computerized system validations that include
Plan → Build → Test → Authorize → Maintain.

Step 1: Validation Planning

In first step of validation, we have to create a validation plan or protocol that defines:
1. Purpose and Scope of the Excel Spreadsheet
2. Description of the Intended Use of the Excel Spreadsheet
3. Roles and Responsibilities Associated with Excel Spreadsheet Validation
4. List of Deliverables Related to Validation of Excel Spreadsheet
5. Acceptance Criteria for Validation of Excel Spreadsheet
6. Change Control and Periodic Review Requirements for Excel Spreadsheet

The validation protocol is a document that communicate to all stakeholder holders how the process of developing, testing and maintaining the Excel spreadsheets will be carried out.

Step 2: User Requirement Specification (URS)

The URS provides descriptions of what the spreadsheet needs to achieve. The URS should include:

• Data input, calculation and output
• Reports the expected accuracy and range.
• Describes data protection and access control requirements.
• Describes audit trails or version control.
• Describes format and layout expectations.

For example:
The excel sheet will calculate an assay percentage automatically based on the weight of sample, its dilution, its potency and its area ratio.

Step 3: Design and Development

Based on the user requirement specification (URS), the first step is to create an Excel spreadsheet. The following are examples of good design principles for the spreadsheet:

• Using cell references rather than entering hardcoded numeric values.
• Implementing data validation to prevent invalid data entry.
• Locking and securing the cells that have formulas in them.
• Labeling inputs, calculations and output areas separately.
• Using the same units of measure and decimal format throughout the spreadsheet.
• Incorporating the spreadsheet version number and date in the sheet header.

A well-designed Excel spreadsheet will limit the user’s potential for error and make it easier to validate data.

Create the Calculation Sheet as follows.

1. Prepare the Excel calculation sheet in “Microsoft Excel”
2. Prepare separate calculation sheet for individual tests, which can be common for all products.
3. The typical calculation sheet should include name of the organization, name of the test, name of product, batch No, AR No, and wherever applicable, standard preparation (Weight of the standard, dilutions, potency of standard) Average standard area / absorbance, labelled amount, Average weight, conversion factor (if applicable) Sample preparation (Weight of sample, dilutions) and sample area / absorbance. [In case of Related Substances, include retention time, relative retention time, name of the impurity etc]
4. For dilutions of the standard solution, use prefix “S” and serial numbers.
5. For dilutions of the sample solution, use prefix “T” and serial numbers.
6. Consider the maximum possible dilutions of the solutions and give provision for it.
7. Type the formula correctly in the cell where the result will appear.
8. Fill the grey color in the cells that need to be filled while calculating the results.
9. Protect the excel sheet except for the cells that need to be filled while calculating the results.
10. Protection of excel calculation sheet shall be done by Assistant Manager QA.

Step 4: Verification of Formulas and Logic

Each formula in the spreadsheet must be verified manually to check for correct logic and mathematical calculations. For each formula:

• Check the function syntax and logic (for example, SUM, AVERAGE, IF, ROUND).
• Check the relative and absolute references used correctly.
• Verify results from the spreadsheet using a calculator.
• Check that there are no rounding issues or errors and confirm the number of significant digits.

All formulas and calculations must provide expectations for results within the range of the input.

Step 5: Conduct Functional Testing

Functional testing tests the functional operation of the worksheet. This includes:

• Testing of results on extreme value inputs
• Testing of error messages if invalid values are entered into a worksheet.
• Testing of automated results based upon entered data into the worksheet.
• Testing of protected and locked cells (usually used for formula/accounting based testing) in the workbook.
• Testing using typical copying and moving of cells and ensuring that no formula is affected; i.e., copying of formulas should create duplicate formulas.
• Testing of print layout and report formatting of worksheet.

Records of all functional testing activities should be documented in a testing protocol. The test protocol will provide documentation of all functional test procedures, which include input data, expected results, and actual outcomes, for the testing performed.

Step 6: Security and Access Control

To maintain data integrity, the Excel spreadsheet must be protected against unauthorized modifications. Recommended access and security controls are:

• All calculation cells must be locked, the worksheet must be password protected.
• Valid Excel workbooks must be stored on a network or dedicated server, not on an individual local hard drive.
• Only those users with authorized user rights will have editing access to the workbook.
• Version Control - Each new version of the Excel workbook must be validated.
• File audits - If the Excel workbook files are stored in a controlled environment such as SharePoint or LIMS, audit trail logging capability should be enabled.
• If the spreadsheet is used for GMP-critical data, it is highly recommended that the worksheets be maintained in a read-only and/or controlled Document Management System (DMS).

Procedure for Protecting an Excel Calculation Sheet 1. Select the cells to be filled in for calculation. Right click on the selected cell and click on Format Cells and select the Protection tab in the window opened. Uncheck the box next to Locked.
Important: After protecting the calculation sheet, only the cells that were left unlocked during this procedure can be edited.
2. From the Review menu click on Protect Sheet option. Enter your password and press OK. You will then be prompted to confirm your password by re-entering it in the Confirm Password box; make sure to enter the same case-sensitive password here.
3. Do the same process for all cells that will need to be filled in during calculations and fill these with a grey background color.

Step 7: Documentation

Validation must be fully documented and should include:

• Validation Protocol/Plan
• User Requirement Specifications (URS)
• Risk Assessment (if applicable)
• Verification & Testing Results
• Validation Summary Report (VSR)
• Controlled Access to Approved Master Copy (Protected/Version-Controlled)
• Standard Operating Procedure (SOP) for Spreadsheet Creation, Validation and Change Control

The Validation Summary Report should provide a summary of all test results, any deviations and final approval for the validated item(s).

Risk Assessment for Spreadsheets

The following guidelines outline how to perform risk assessments on Excel spreadsheets.
All Excel spreadsheets do not need an equal level of validation and a risk-based strategy should be used to know the requirement of spreadsheet validation.
High Risk - Assay calculation sheet for product release - Full validation (URS, test scripts, security, audit trail)
Medium Risk - Calibration record tracking sheet - Partial validation
Low Risk - Administrative or training tracker - No validation required

The higher the risk of affecting product quality or compliance, the more extensive the validation will be required for the spreadsheet.

Change Control and Periodic Review

Any changes (e.g. modifications to formulas, layouts, or the addition of other fields) identified with respect to an excel spreadsheet need to result in the assessment of impact and partially or completely re-validated through the use of a controlled change process.

Periodic Review

Excel spreadsheets need to be reviewed periodically (typically 1 – 2 years) to ensure that:

• The Logic of the Excel spreadsheet still meets the current requirements;
• There are no unauthorized changes to the Excel spreadsheet; and
• Current formulas, references, and protections are still intact/maintained.

Common Deficiencies Observed by Auditors

Auditors have noted the following deficiencies as issues that are common for inspectors (GMP).
1. GMP calculations are completed on unverified excel spreadsheets.
2. There is no version control or change tracking.
3. Formula mistakes are made, and the lack of proper validation resulted in the formula mistake not being detected.
4. The calculation spreadsheets have unlocked cells which allow for the unauthorized changing and manipulation of data.
5. The validation process has not been documented and not reviewed.
6. Spreadsheets are saved on employee computers without any back-up or control.

All of the above points create potential data integrity problems and can potentially generate regulatory citations.

Example Structure of a Validated Excel Sheet

• Well-designed validated Excel spreadsheets include a header (name, version, date, approval, and purpose).
• An input section (user entry cells are clearly marked white/yellow).
• A calculation area (formulas are protected/hidden with a gray background).
• An output section (results), and a footer (signature, date, remarks).

These elements help reduce errors and improve the user experience, as well as facilitate validation of an Excel spreadsheet.

Validation of Excel Calculation Sheets is important to ensure accuracy, integrity and compliance in pharmaceutical Manufacturing and Testing. GMP critical Excel calculation sheets should be considered like any other computer-based system. Validation, documentation and control is critical to ensure that all Excel calculation sheets are verified and tracked throughout the life cycle.

Pharmaceutical Companies may use a structured process to validate Excel calculation sheets, including; User Requirement Specification (or URS) preparation, verify all formulas, functional testing, documentation, change control, etc. By using this structured approach, pharmaceutical companies can rely on the use of Excel for accurate calculations without losing their ability to maintain compliance to the regulations.

Validated Excel calculation sheets are not only a method of preventing costly mistakes in today's data-driven GMP environment but validated Excel calculation sheets will also provide an additional layer of regulatory confidence and increased operational efficiency for all users.

Get ready to use editable Validation Protocols in MS-Word FormatView List

Share

Tweet

Share

Pin it

Share