Validating Excel Workbooks for Regulatory Compliance

Objective

Outline the requirements and strategies for creating a compliant Excel workbook.

Why Validate?

Spreadsheet software is a powerful tool for processing data. This allows it to be an extremely useful asset for the pharmaceutical industry, however, it can also be a huge risk. Each year citations from regulatory bodies inspecting pharmaceutical labs are given out to companies for their use of un-validated spreadsheets. While almost all professionals in the industry have some degree of experience with spreadsheet software, the level of technical ability varies greatly from user to user. Excel spreadsheet validation creates a standard that is acceptable for all associates involved in the project.

When to Validate?

In a Q&A issued by European Medicines Agency: “Validation according to paragraph 4 of annex 11 is required at least for spreadsheets that contain custom code (e.g. Visual Basic for applications). Formulas or other types of algorithm should be verified for correctness. Therefore, appropriate controls for templates of spreadsheets must be in place. Erroneous calculations due to data remaining from previous calculations must be avoided. The templates should also be suitably checked for accuracy and reliability (annex 11 p7.1) and should be stored in a manner which ensures appropriate version control (chapter 4 p4.1).”

Common Issues with Spreadsheets

There are many hurdles to making spreadsheets compliant. The following are just a few of the common issues that must be addressed.

  • Easy access to programs
  • Everybody (not trained on GxP compliance validation and computer science) can write programs
  • Everybody can change without control
  • No validation, no documentation
  • Many different environments (operating systems, PC hardware)
  • Many versions in use (local PC, server, inbox, delete folders)
  • No or insufficient documentation

Procedures

Successful validation is dependent on proper planning and procedures being in place. It is likely that a large number of spreadsheets will be involved and therefore efficiency and repeatability are critical factors in the validation process; the key to efficiency and minimizing the validation costs is to keep the process simple. This is achieved by introducing streamlined processes throughout the validation lifecycle and the use of a generic, yet adaptable, set of documentation. This includes validation SOPs, usage SOPs, and Validation Plans.

Design

The spreadsheet should be designed with compliance in mind following established documented procedures. However, it is common that a spreadsheet has already been created and is in use before the decision to validate is made. The FDA has issued the following requirements as a guide to validating spreadsheets:

  • Lock all cells of a spreadsheet, except those needed by the user to input data.
  • Make spreadsheets read-only, with password protection, so that only authorized users can alter the spreadsheet.
  • Design the spreadsheet so that data outside acceptable conditions is rejected (for example, reject non-numerical inputs).

Validating

Each company has different approaches to Excel spreadsheet validation. Regardless the method (Validation Plan vs. SOP), the following points must be addressed at minimum:

  • Manually verify spreadsheet calculations by entering data at extreme values, as well as at expected values, to assess the ruggedness of the spreadsheet.
  • Test the spreadsheet by entering non-sensical data (for example alphabetical inputs, <CTRL> sequences, etc.).
  • Keep a permanent record of all cell formulas when the spreadsheet has been developed. Document all changes made to the spreadsheet and control using a system of version numbers with documentation.
  • Periodically re-validate spreadsheets. This should include verification of cell formulas and a manual reverification of spreadsheet calculations.

Documentation

The following information is key to document as proof of the validation effort:

  • Description of what the spreadsheet does
  • Description of formulas used
  • Explanation of the relationships of formulas used
  • User manual (including keys for any color/formats used)
  • Listing of any code (VBA Macros, etc.)
  • Test Sheets with expected results, acceptance criteria, and actual results
  • Security measures, password maintenance, and user lists
  • System requirements such as operating system, spreadsheet software version, and workbook version.

Conclusion

The validation of spreadsheets often gets overlooked on the priority list but is crucial to avoid regulatory issues. There are many resources available online to guide first time validators through the process. SciCord provides services and products that can fulfill these validation needs. Please contact us with any questions you may have regarding spreadsheet validation.