Spreadsheets are all around the professional business environment. Spreadsheet software which is more than just a giant calculator is completely integrated in our everyday business and used by most companies worldwide . It can perform complex calculations, draw various kind of diagrams and it supports the usage of software code to run programs that have been self-developed. The biggest advantage of spreadsheets is that anything can be configured, customized and a huge library of formulas is included to make the development process even easier for anyone. The downside could be that using spreadsheets might lead to horrible errors and outcome .
To use spreadsheets in the life sciences environment namely for GMP purposes, a bit of effort should be made to ensure that the generated spreadsheet fulfills regulatory requirements which are applicable. It is important to recall that a spreadsheet with GMP impact must be compliant with all regulations like any other system used for GMP purposes. A not validated spreadsheet or a spreadsheet that is not compliant with respective regulations must not be used for GMP purpose. There is no shortcut on the way to compliance in the validation process of a spreadsheet but we will give you some advises that may ease your work and lower your effort. At the end every spreadsheet is a unique system and the work effort is based on its risk and complexity.
We will give you an idea of how to approach the validation of a spreadsheet assuming that you already have knowledge in computer system validation.
Life cycle of a spreadsheet
The classic life cycle of a spreadsheet may look like the one described in figure 1: possible life cycle of a Traceability Matrix. It always should start with the development of requirements or a change of existing requirements. A Risk Management process must be in place and it shall be used to assess the risks of every process step during the validation . To plan the validation and give the whole validation project a structure, a validation plan should be written. Ideally the validation plan also contains an inventory of all spreadsheets that shall be validated. The testing phase of a spreadsheet consists of the IQ/OQ/PQ phases as known from classic computer system validation. To conclude the validation, a validation report shall be written, summarizing the outcome of the validation process. The EU Annex 11 expects that the owner of the system can trace the requirements through the life cycle of each spreadsheet. To provide respective evidence it is recommended to create a “Traceability Matrix”. It can be signed at the end of a project to provide information of the “as-built” status of the spreadsheet. The URS, FS and the Traceability Matrix must always be updated when the spreadsheet is being changed.
2 Development Phase
The development of a spreadsheet should always be based on the URS and the respective Risk Assessment. Beware that this could end in an endless circle of making the spreadsheet perfect and wanting to reduce the risk until it is as low as possible. That is not necessary. The target should be to reduce the risk to an acceptable limit  Appendix M3.
To make this process as fast and easy as possible it would be best to have a Standard Operating Procedure (SOP) in place which describes the process of the development of a spreadsheet. If a logbook in the spreadsheet may not give you enough control over the history of the spreadsheet, a solution of a specialized provider for spreadsheet Add-ins may help you out to install an Audit Trail on the spreadsheet , . To ensure data integrity it is highly recommended that the spreadsheet is blocked to be protected from willing and unwilling change. The access to the spreadsheet shall be restricted to authorized and trained personnel only. A simple solution to this challenge is to place the spreadsheet on a server area with restricted access and the restriction that it cannot be copied onto other computers  chapter 12. For the signature process a SOP must be in place to describe how the PDF files are managed, signed and archived  chapter 14. Trying to be compliant with FDA’s 21 CFR Part 11 may be very challenging with a spreadsheet. We recommend to use the spreadsheet as a “Hybrid System” as explained by the FDA . Therefore, the easiest way to be compliant with 21 CFR Part 11 is, to not use the spreadsheet to retain data. Instead enter the data in the spreadsheet, perform the calculations and printout the results which shall be signed on paper. This would be compliant to what FDA defines as a “Hybrid System” and will be widely accepted by authorities. If the data is generated by a system which is not generating printouts (e.g. pH meter; balances), the measuring result shall be entered in the spreadsheet, and printed on a paper based report. The report shall be reviewed and signed. If the original data is available on paper because the system is printing it out, this original data shall be retained together with the reports of the spreadsheet. Consider the MHRA regulations for further requirements regarding data integrity .
2.1 Data Storage and security
Do not retain data with a spreadsheet. Retaining data with a spreadsheet and being compliant with the 21 CFR Part 11 is a big challenge. The original purpose of a spreadsheet is not to retain data. Nevertheless it must be made sure that a backup of the spreadsheet exists and the integrity of the backup must be checked during the validation. This means that the backup functionality must be tested during the validation. The best way to test this during the validation is to install the spreadsheet, create a backup, delete the spreadsheet and restore it from the backup. Ideally the spreadsheet and the data printed out from it is archived in a secure place / environment, protected from uncontrolled change, destruction and usage. Any changes to the spreadsheet must follow a controlled change process  chapter 12.
3 Planning and Testing Phase
To plan the validation of one or more spreadsheets it is required to have an inventory of all spreadsheets  chapter 4.3. The inventory shall be a controlled document listing all spreadsheets for GMP purpose. It is recommended that spreadsheets have an ID number and follow a versioning concept which makes it possible to identify each version of each spreadsheet. The order of the validations shall be risk based. If an external supplier is involved in the development of the spreadsheet it should be made sure that the supplier is qualified for the service it is providing before it starts working on the project  chapter 3.
3.1 Validation and Tests
The documentation of tests must be performed according to Good Documentation Practice . The test design for spreadsheet is similar to other validation/qualification tests. The validation documents also must be written based on an approved URS and a respective Risk Assessment. Testing a spreadsheet means that the requirements are challenged. The different tests can be categorized as functional testing, design testing, security testing.
3.2. Functional Testing
We recommend that all tests are performed in the same or a similar environment as the final version of the spreadsheet will be located at. Otherwise it could be doubted that the functionality of the spreadsheet remains the same when it is moved to the productive environment. The test environment shall be documented in the test plans.
Usually the main functional requirements for a spreadsheets are the formulas and/or macros. Those are usually also the most critical requirements as they have the most critical impact on the patient health and product quality. In the URS/FS the concrete formulas must be written. In the development phase this formula is being transferred into the spreadsheet. During the testing phase it must be checked that the transfer from the URS/FS into the spreadsheet has been performed properly (figure 4: example of how a test script may look like). The target of this tests is to discover human errors made during the development phase. This must be done for every formula used in the spreadsheet. Based on the risk assessment it should be verified that the necessary rounding and rounding rules are respected by the settings of the spreadsheet.
To be continued…
In the following blog we will present an overview of the planning, testing and using phase. We will also talk about the usage of Macros in spreadsheet and why Macros are challenging for a spreadsheet validation.
Keep reading the Spreadsheet Validation in GMP environment with Part 2
 [Online]. Available: http://venasolutions.com/infographic-many-uses-ms-excel-going-away-time-soon/
 E. S. R. I. Group. [Online]. Available: http://www.eusprig.org/horror-stories.htm
 [Online]. Available: http://www.ispe.org/gamp-5
 O. Systems. [Online]. Available: http://www.ofnisystems.com/products/excelsafe/.
 SpreadSheetValidation. [Online]. Available: http://www.spreadsheetvalidation.com/pdf/DaCS_software.pdf.
 [Online]. Available: https://www.gov.uk/.
 F. -. S. Validation. [Online]. Available: http://www.fda.gov/scienceresearch/fieldscience/laboratorymanual/ucm174286.htm#4_5_2.
 [Online]. Available: http://ec.europa.eu/health/documents/eudralex/vol-4/index_en.htm.
 EudraLex, “Eudralex,” [Online]. Available: http://ec.europa.eu/health/files/eudralex/vol-4/chapter4_01-2011_en.pdf.
 F. S. T. o. G. Records. [Online]. Available: http://www.fda.gov/Food/GuidanceRegulation/GuidanceDocumentsRegulatoryInformation/DietarySupplements/ucm238182.htm.
 [Online]. Available: http://www.fda.gov/RegulatoryInformation/Guidances/ucm125067.htm.
 [Online]. Available: http://www.spreadsheetvalidation.com/pdf/Excel_Spreadsheet_Validation_Overview.pdf.
Latest posts by Daniel Broszeit (see all)
- Spreadsheet Validation in GMP environment (2 of 2) - February 29, 2016
- Spreadsheet Validation in the GMP environment (1 of 2) - February 23, 2016