Spreadsheet validation in GxP: inventory first, template second

Start with an inventory, not a template

Most spreadsheet validation programmes start by writing a template and then wonder why nobody uses it. Start with a list instead. Which spreadsheets are used to make or support a GxP decision, who owns each one, and where does it live?

The list is usually longer than expected, and that is useful in itself. It tells you the size of the problem and gives you something an inspector can see you are managing.

Sort them by what they do

Not every spreadsheet needs the same treatment. A useful split:

• Used like a typewriter. A table or a form, no calculation that matters. The printed or signed output is the record. Control the template and you are mostly done.
• Simple calculations. Standard functions on entered data. Verify the formulas once, lock the cells, and control changes.
• Complex logic. Macros, lookups across sheets, nested conditions. This is effectively bespoke software and should be treated with similar care, or replaced.

The third group is where real risk sits. If a spreadsheet has grown macros, ask honestly whether it should still be a spreadsheet.

What the controls look like

For anything beyond the first group, the basics are the same each time. Formulas verified against independent calculation, including edge cases like blanks, zeros and out-of-range values. Input cells separated from calculation cells, with the calculations locked. A controlled master copy in a known location, with version and change history. Access limited to the people who should edit it.

None of this is complicated. It just has to be true, which is harder with files that can be copied to a desktop in two clicks.

The data integrity problem

Here is the part template-led programmes miss. A standard spreadsheet does not keep a reliable audit trail. It cannot show who changed a value, when, or what it was before. Someone can edit an entry, recalculate and save, and the file looks exactly as if nothing happened.

That is why regulators are sceptical of spreadsheets as the record for important data. If the result drives a release decision, the safer pattern is to treat the spreadsheet as a calculation tool and keep the record somewhere with a real audit trail, or print, sign and retain the output with the inputs visible. Our data integrity page covers the wider expectations.

Keeping them validated

A spreadsheet validated in 2023 and edited eleven times since is not validated. Changes need the same discipline as any other system: a change request, a check of what the change could affect, and re-verification of those formulas. Periodic review should confirm the inventory is still accurate and that people are using the controlled copy, not a local one. Everything in our piece on periodic review applies here too.

When to stop using the spreadsheet

When it needs a macro to work, when several people edit it at once, or when the same calculation is maintained in more than one copy. At that point the validation effort starts to exceed the cost of a proper tool. GxP software vs. spreadsheets goes into that trade-off. If you want help sizing your inventory, get in touch.