Home / News / Validation of excel spreadsheets

Validation of excel spreadsheets

It is recognised that excel spreadsheets have been widely used in all industries for many years. Also in pharmaceutical companies they are used in different departments. Spreadsheet examples for GxP related matters range from a simple template for calculations of impurity in a sample to a highly complex spreadsheet for statistical analysis of clinical studies including Visual Basic for Application (VBA) macros. Such spreadsheets may contain critical data and may be used for making critical decisions.

A major attraction of spreadsheets is their ease of use and flexibility. The downside of this versatility is the potential for developer and operator error, and the difficulty in verification of the spreadsheet and the data it produces. Additionally, there are inherent security and audit trail deficiencies in the standard Excel application, leading many experts to consider spreadsheets as ‘unvalidatable’. Records generated by the calculations are electronic records and must comply with EU Good manufacturing practice guidelines Annex 11 and US FDA 21 CFR Part 11. Spreadsheets need to comply with several requirements, however controls are quite often inadequate, and this may result in a compliance risk.

When it comes to spreadsheets, what should be validated? A Guidance on good manufacturing practice and good distribution practice: Questions and answers (Q&A) issued by EMA provides the following answer: “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 (EudraLex Volume 4 GMP Annex 11 paragraph 7.1) and should be stored in a manner which ensures appropriate version control (EudraLex Volume 4 GMP Chapter 4 paragraph 4.1).

When it comes to accuracy checks (EudraLex Volume 4 GMP Annex 11 paragraph 6), data integrity is an important issue. The Q&A document explains that both the files and the calculations should be secured in such a way that formulations are not accidentally overwritten. Accidental input of an inappropriate data type should be prevented or result in an error message.

According to the EDQM guidance Validation of Computerised Systems Annex 1: Validation of Excel Spreadsheets (revised August 2018) there are ways to validate your excel spreadsheet. One of the first things that needs to be determined is whether the spreadsheet needs to be validated. There are two related questions here:

  • does the data flow involve GxP critical data that needs to be validated?;
  • and which spreadsheets within that data flow should be validated?

A pragmatic approach to the validation of Excel spreadsheets is presented in the principals of GAMP® 5 (V model) guidelines. GAMP® 5 is Internal Society for Pharmaceutical Engineering’s (ISPE) guide for validation of computerized systems in the pharmaceutical industry; the Good Automated Manufacturing Practice A Risk-Based approach to compliant GxP computerized systems. The GAMP® 5 guides you through the process and even gives practical instructions on how to write the validation documentation.

Important steps that should not be forgotten during validation process:

  • uphold spreadsheet security and protocols

A spreadsheet should include all necessary requirements for proper spreadsheet validation:

  • when a user fills the validated spreadsheet template with data, the resulting spreadsheet file with data is an electronic record. Use of the spreadsheet must be restricted to the intended user, stored on a location with access limited to selected users. Protect the spreadsheet with a password that allows users to open the spreadsheet as read-only;
  • spreadsheet should be protected against any undesired changes, meaning all cells within the spreadsheet (except the cells in which the user needs to give input) should be locked. This way, the user cannot change cells with formulas, constants, labels etc;
  • it is recommended that the input cells are coloured to make the spreadsheet more clear and easier to understand for users;
  • use of drop-down menus or data entry limits is recommended to prevent entry mistakes;
  • a version number to the spreadsheet should be implemented.
  • prepare validation documentation and standard operating procedures

Extent of the validation documentation that needs to be compiled is determined based on the outcome of impact assessment on patient safety, product quality, and data integrity, and the complexity of the spreadsheet. GAMP® 5 provides simple guidelines on this topic; so called “GAMP software category”.

In addition to validation documentation, all necessary supporting Standard Operating Procedures need to be prepared as well; Change and Configuration Management, Backup and Restore, Archiving and Retrieval, Working Instructions for how to use the spreadsheet template for each type of user.

  • manual verification of the reliability of the spreadsheet

During validation manual verification of the reliability of the spreadsheet needs to be conducted. This is done by verification of the spreadsheet calculations by entering expected values and extreme values and verifying the behaviour of the spreadsheet or comparing the data with already known data.

  • formal release of the spreadsheet for use

After successful spreadsheet validation, the end step is to formally release the spreadsheet for use. Any further changes (optimization) of the validated spreadsheet should go through change control and the actions for revalidation can be determined based on a risk assessment.