Need extra support quickly? We are working with clients around the world right now to help them tackle new challenges from COVID-19. How can we help you? Learn More >

Back to Posts

Back to Posts

July 20, 2020 Life Science Consulting

6 Quick Tips for Excel Spreadsheet Validation

Nynke Noort Consultant

Are you using Microsoft Excel spreadsheets for GxP activities? Are these Excel sheets used again and again, as a template? Ever thought about validating such templates? Is the person who built your spreadsheet a genius with Excel, but doesn’t know how to validate? Want to use spreadsheet templates but validation issues are holding you back?

It is fairly common that a spreadsheet template is created to do calculations which otherwise should be done by hand or calculator. 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.

Some people may think a 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 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 extend of activities required to validate your spreadsheet.)

But, no need to be afraid. A good road map and some tips and tricks can help you on your way through the rough landscape of spreadsheet validation. Here we go!

  1. 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. With all your experience, regulations, and guidelines in mind, develop a spreadsheet that includes all necessary requirements. 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 even can 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 the GAMP guide can help you to find out what you need to do. Here you get the so called ‘GAMP software category’.
  4. 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. 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. After successful 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.

So, please don’t be afraid of spreadsheet validation anymore. It is actually very doable!

However, don’t forget: an Excel 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 accessed with each update. For example, Office 365 is a platform that is continuously improving and implements updates each month. Knowing this, also consider using dedicated software for your calculations, such as laboratory data systems.

We invite companies who are looking for an efficient way to validate their Microsoft Excel sheets in an optimal way to contact us. You provide the necessary information and we’ll provide you with a compliant sheet.

Your Business has Complex Challenges. ProPharma Group has Exceptional Solutions.

We partner with pharmaceutical, biotechnology, and medical device clients to tackle complex challenges. Contact us to learn how our experienced team can help ensure regulatory and development success throughout the product lifecycle.

Interested in gaining an industry edge? Let us help you stay up to date.

All fields are required.