A Guide for Excel Sheet Validation needs

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.

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).

Configuring your 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.
Apply data validation rules to prevent aberrant input. Optionally you can even add input or error alert messages.
Where possible use dropdown lists.

Validating your spreadsheet

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
Applicable SOPs
Access rights and password protection information