1. Using Macros
We recommend to not use macros if not absolutely necessary. . If you need to use macros or program code to realize your custom solution each possible step of the program code must be tested. To visualize the program it is recommended to design a flowchart of the process. This flowchart should be document in the URS / FS. In the OQ / PQ test it must be verified that the program is following the process flowchart as described in the URS / FS. Depending on the program that has been written this leads to a large amount of testing. As you can see in figure 5: flowchart of a customized program every decision that is made during the process leads to a new path that must be tested. The test of this program would require at least 20 tests.
In case you find any errors or mistakes during the test phase it may impact the whole program and complete re-testing may be required. The source code nevertheless should also be part of the URS / FS document to have documented what the code looks like.
The design expectations and also a template could be part of the SOP describing how to validate a spreadsheet. The design requirements should mention that each spreadsheet must be (Year, Month, Day, Hours, Minutes, Seconds and Time zone). If you want to have electronic signatures instead of paper records, you must find a solution to convert the outcome of the spreadsheet into an electronic report so it can be signed. One solution would be working with PDFs. Converting the spreadsheet into a PDF enables you to sign an protected, electronic version of the spreadsheet electronically. PDF files can also be protected from uncontrolled changes. From design perspective the usability of a spreadsheet is also a very important topic. If you want that the user uses the spreadsheet correct, its design should be as intuitive as possible and it should align with the approved SOP. Therefore the SOP describing how to use the spreadsheet should be in place and approved before the validation testing gets started. Based on the risk and complexity of the spreadsheet it could also be tested if the SOP describes the usage of the spreadsheet correctly. The spreadsheet should be designed in a manner that values which are exceeding limitations will be rejected by the spreadsheet . For example if there is fields which are supposed to be used for the entry of numbers, the spreadsheet must reject the entry of symbols and letters. Also if there is a certain range of numbers expected to be entered, the spreadsheet must reject numbers that are below or exceeding the range.
If the spreadsheet shall be used by multiple people it is recommended to ensure that the spreadsheet cannot be copied onto a local computer. This would prevent people from working with an old copy of the spreadsheet. The spreadsheet must be protected from unwanted and uncontrolled change. Lock all cells of the spreadsheet, except those needed by the user to input data .
The report or the validation documents must contain a copy of the spreadsheet with all formulas and the complete program code in it. This copy must reference to the respective spreadsheet. Therefore it is highly recommended to have a unique ID number for each spreadsheet.
The SOP must describe where the original data and the printouts shall be archived during and after the use of the spreadsheet. The procedure for the storage of the data must also be tested during the validation of the spreadsheet. It must be made sure that the generated data is protected against any kind of damage. Accessibility, readability and accuracy must be guaranteed for the respective lifetime [10; 8]. 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.
Regular backups of the spreadsheet and its environment should be performed. If the spreadsheet gets changed it must be made sure that the actual spreadsheet is archived and not destroyed. It must be treated like a document and in respective SOP it shall be mentioned that every spreadsheet must be archived according to applicable regulation . The functionality of the backup strategy must be checked and tested during the validation.
Each spreadsheet shall be re-validated periodically. There should be a process in place which organizes and plans the periodic re-validation . The period for the re-validation shall be based on a risk assessment and described in local procedures.
Respective personnel must be trained on how to use the system and how to validate it. A SOP must be in place to describe how the spreadsheet shall be used.
Spreadsheets can be the evil tool that will ruin your process and turn your world upside down, or it can be the tool that will solve all your problems. It can be both and it is up to you to decide what it is going to be. Your decision if a validation or re-validation is required should be based on the risk the spreadsheet is having on patient safety, product quality and data integrity. Review the latest regulatory requirements to define what is expected and what is recommended. Plan the validation accordingly and make sure that deviations from the plan, or changes are managed in a controlled manner. If the spreadsheet fulfills your requirements that have been developed in a team of responsible people and the remaining risk is either covered by extensive tests or covered by functionality of the spreadsheet, the spreadsheet will do you a great service.
Never miss an update by following us and subscribing to our monthly newsletter!
 [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