6 Quick Tips About Excel Sheet Validation (GAMP)

#6 Quick Tips About Excel Sheet Validation (GAMP)

Are you using Excel spreadsheets? And are these used 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?

And what does an umbrella have to do with it?

Caricature by George Cruikshank (1792-1878) entitled ‘The Umbrella’ and dating from 1820.

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 macros. Such spreadsheets may contain critical data, such as laboratory information, and may be used for making critical decisions.

Many companies think a spreadsheet template is just a calculator and don’t realize that you should actually think of it 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 a GAMP software category 5 application. The GAMP software category defines the extend of activities required to validate your spreadsheet.

But enough to scare you off, 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 the spreadsheet that includes all necessary requirements. Think of the following aspects:
    • Use of the spreadsheet must be restricted to the intended user. So place the template on a (server) location with access limited to selected users. And 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). So the user cannot change cells with formulas, constants, labels etc.
    • And 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 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. So, just like the template, make sure these files are stored on a (server) location with access 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 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 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 much doable.

However, we do have to make a remark: an Excel spreadsheet template is built upon the spreadsheet application Microsoft Excel. Which is principally not designed for regulated environments. So 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 expanding and implements updates each month. Knowing this, also consider using dedicated software, such as laboratory data systems, for your calculations.

What did the umbrella have to do with it? Here's your answer!

Definition of ‘gamp’, found on:

If you have any questions don't hesitate to contact us!

Blog by: Nynke Noort - Consultant

Did you like this blog? You might also be interested in our sequel: 7 Quick tips about Excel sheet operation (GxP / GAMP)

NEW: We invite companies who are looking for an efficient way to validate their Excel sheets in an optimal way. You provide the necessary information and we'll provide you with a compliant sheet. Check it out!

Subscribe to newsletter