Excel spreadsheets, we all know them, most of us use them, and some of us develop and validate them. And sometimes, they can be the bane to your existence. Don't worry though. In this blog, we will enlighten you with best practices for validating these wondrous spreadsheets.
First of all, an answer to the question: why do we validate Excel spreadsheets? There are multiple reasons:
Excel spreadsheets may be used to automate calculations, which in turn have to be validated because you need to be sure that your calculations are correct.
They may contain critical data like test results.
A completed spreadsheet is considered an electronic record and must as such be Annex 11 and CFR 21 Part 11 compliant.
Excel AND Excel spreadsheets are considered to be software according to GAMP5, which can immediately be used to assist during validation.
1. Keep it secure
A spreadsheet is a file, both as an empty template or as a completed electronic record. And files have to be kept somewhere, and if possible at all, somewhere secure. Let me introduce you to the first one in our bounty of best practices:
Store your validated spreadsheets on a network share with read-only access right for the end-users. This means that end-users should not be able to save or add files in the network share and cannot modify files.
Similarly, assign write rights for the network share for the assigned responsible person.
End-users should only be able to fill in permitted cells, on which I will tell you a bit more later on.
End users can only print and save to a protected data repository (which could be a different network share).
Assuming our files are now stored in a safe place, let’s take a look at which protective measure can be taken for the spreadsheet itself. Yup, you guessed it, more best practices coming up:
Lock all cells containing calculations, which means adding a password to unlock these fields. This code should only be available to the responsible person.
Protect your workbook structure by adding a password as well (this can be the same if you want to have an all-round Excel spreadsheet password).
And voila, tamperproof!
2. Configuring your first spreadsheet
Let’s delve a bit deeper into the configuration of a (soon-to-be-)validated spreadsheet. First things first, which general information should or could be available in the file? Let’s create a list:
Provide dedicated cells for operator name and date
Display file path and filename spreadsheet
Display the Excel version number
Display the Spreadsheet version number, be it in the file or as the filename
Add an audit trail log
To get output, there first needs to be some input. These practices will make your and your end-users’ lives a lot easier:
As I mentioned earlier, make sure all cells are locked, except those used for data input.
Identify input cells by giving them a specific color.
Apply data validation rules to prevent aberrant input. Optionally you can even add input or error alert messages.
Where possible use dropdown lists.
And now that we have our input, let’s create some output. Have some additional guidelines, on the house:
Lock these cells! We promise it’s the last time we will mention this one.
As with input cells, identify output cells with a specific color.
Use conditional formatting when testing against acceptance criteria.
Take care of the values that need to be displayed and take into account the number of decimals to be shown, the units, and the presentation (such as exponential values).
3. Validating your first spreadsheet
We’ve got a fully configured spreadsheet stored in a safe location. Time to get some validation going. As I mentioned at the start of this blog, you can use GAMP5 as a guideline for the validation of your spreadsheets.
As with any validation cycle, create your user requirement specifications. These will list:
The purpose of the spreadsheet (be it calculations or otherwise)
The general layout of the sheet(s) (calculations, curves, audit trail log…)
Used data types (text, numerical, …)
Applied data validation rules and/or conditional formatting
Localization of the file and requirements attached to this (such as the protected network share)
Name and/or unique identification of the spreadsheet
Access rights and password protection information
After which you can safely and responsibly move on to validation. Put your user requirement specifications to practice and start running those test scripts. Additionally, add a full print-out of the worksheet with formulas shown to your validation file and any VBA code that might be used in macros. Don’t forget to document the Excel version used to create and validate your spreadsheet.
If there are any calculations in the spreadsheet, these will need to be verified with an independent system. There are two methods you could use to go about this:
Compare to results obtained by manual calculation.
Compare to published reference data or results obtained with commercial software.
Also, take the following advice into account:
Calculations must be validated for use with all different Excel versions that are in use.
Calculations should also be validated for datasets with exceptional data (OOS, missing data, nonsense input, limit testing, …).
Finally, consider the option of an SOP or other documentation containing:
Validation method for spreadsheets within your company
The responsible person
How to handle updates and issues with the spreadsheet
Requirements for periodic verification
And now that I’ve mentioned these, I’d like to add some last words on unavoidable changes, updates and/or issues:
Initiate changes with a Change Control
Verify after each change in software and hardware to ensure the validated state is maintained
Use a known data set and compare results to prove the equivalence between versions
Document date, type of intervention, results, performing operator and any comments
Too long; didn’t read
Quite a bit of information, isn’t it? Here's a summary of the main points:
Excel spreadsheets used in a quality environment must be Annex 11 and CFR 21 Part 11 compliant as they are considered electronic records.
Use GAMP5 as a validation guideline
As for your configurations, adhere to the following:
Lock and differentiate cells (oops, I did it again)
Provide the required general information
Apply sufficient protection to your spreadsheet and storage location
As for validation, follow the standard validation cycle (URS, IOPQ, …) and provide evidence of the working state of any calculation or formula. And with this, we wish you a happy spreadsheet validation!
Blog by Kenneth Poupaert