6 Quick Tips for Excel Spreadsheet Validation

Are you using Microsoft Excel spreadsheets for GxP activities? Are these Excel sheets used again and again, as a template? Ever thought about validating your spreadsheet templates? Is the person who built your spreadsheet a genius with Excel, but doesn’t know how to validate? Do you want to use spreadsheet templates but validation issues are holding you back? Our 6 quick tips for excel spreadsheet validation can help to guide you through this process.

It is fairly common that a spreadsheet template is created to do calculations that otherwise should be done by hand or calculator. Spreadsheet examples for GxP related matters ranging 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.

Some people may think a medical spreadsheet template is just a calculator and don’t realize that it actually should be regarded as an application. In fact, records generated by the calculations are electronic records and must therefore comply with EU’s Annex 11 and FDA’s 21 CFR Part 11.

Did you know that by creating a medical spreadsheet template you are now considered to be a software developer? Your in-house developed software might even be the highest GAMP software category 5 application. (The GAMP software category defines the extent of activities required to validate your spreadsheet.)

But, no need to worry. A well-planned road map combined with our expert tips and tricks can help you on your way through the complex landscape of spreadsheet validation. Here we go!

1) GAMP Guidelines

First of all, relax, you don’t need to show all your calculations are accurate and validate Excel itself. What you do need to show, is that the calculations in the spreadsheet are the right calculations. Now, how do we do that? Well, you’re halfway if you already know about validation and the ‘V-model’. Also embrace the GAMP guide. That’s ISPE's guide for Validation of Computerized Systems in the Pharmaceutical industry; the Good Automated Manufacturing Practice (GAMP). The GAMP guides you through the process and even gives you practical instructions on how to write the validation documentation.

2) Uphold Spreadsheet Security and Protocols

With all your experience, regulations, and guidelines in mind, develop a spreadsheet that includes all necessary requirements for proper spreadsheet validation. Think of the following aspects:

• Use of the spreadsheet must be restricted to the intended user. Place the template on a (server) location with access limited to selected users. Protect the spreadsheet with a password that allows users to open the spreadsheet as read-only.
• You want to protect the spreadsheet against any undesired changes, so lock all cells within the spreadsheet (except the cells in which the user needs to give input). This way, the user cannot change cells with formulas, constants, labels etc.
• Give the input cells a color to make the spreadsheet more clear and easier to understand for users.
• Make use of drop-down menus or data entry limits to prevent entry mistakes.
• Is the calculated result presented in the right way? Does it have the correct number of decimal places; is the unit of measure displayed?
• You can even create an audit trail to log what has changed when using the spreadsheet to make it more compliant to the EU’s Annex 11 or FDA's 21 CFR Part 11.
• Like any application, implement a version number to the spreadsheet, which also comes in handy for change control.
• When a user fills the validated spreadsheet template with data, the resulting spreadsheet file with data is an electronic record. Just like the template, make sure these files are stored on a (server) location with access and user rights limited to selected users.

3) Prepare All Validation Documentation

Depending on the impact on Patient Safety, Product Quality, and Data Integrity, and the complexity of the spreadsheet, GAMP guidelines can help you to find out what you need to do. Here you get the so-called ‘GAMP software category

4) Manually Verify the Reliability of the Spreadsheet

During validation, manually verify the reliability of the spreadsheet. Verify the spreadsheet calculations by entering expected values and extreme values and verifying the behavior of the spreadsheet or comparing the data with already known data.

5) Include Standard Operating Procedures

Make sure all necessary supporting Standard Operating Procedures are present, such as procedures for Change and Configuration Management, Backup and Restore, and Archiving and Retrieval. Also provide Working Instructions for how to use the spreadsheet template for each type of user.

6) Release the Spreadsheet for Use

After successful spreadsheet validation, celebrate your success and release the spreadsheet for use. Any changes should go via Change Control and the actions for revalidation can be determined based on a Risk Assessment.

Maintaining Excel Spreadsheet Validation After Updates

An Excel medical spreadsheet template is built upon the spreadsheet application Microsoft Excel, which is principally not designed for regulated environments. If the spreadsheet software changes, the spreadsheet template might be subject to validation again and should therefore be reviewed with each update. For example, Office 365 is a platform that is continuously improving and implements updates each month. Knowing this, we must also consider using dedicated software for your calculations, such as laboratory data systems.

Expert Resources for Excel Spreadsheet Validation

Excel spreadsheet validation is a complex subject that requires in-depth knowledge of both the Microsoft Excel application and medical regulatory best practices. With significant time, money, and resources on the line, it’s crucial to manage every medical spreadsheet project with consistency, attention to detail, and diligent follow-up.

If you would like to learn more about spreadsheet validation and how to optimize and safeguard your validation projects, please reach out to our knowledgeable team at the ProPharma Group. Our experts can walk you through the process and help to ensure that your spreadsheets and templates are air-tight and ready for validation.

Contact Our Spreadsheet Validation Experts