Process Models for the Validation of Excel Applications

Excerpt of the GMP Compliance Adviser, Chapers 9.H.4.3,  9.H.5.2 and 9.H.5.5

8 min. reading time | by Thomas Trantow, PhD
Published in LOGFILE 45/2021

Definition of the types of Excel applications

Figure 9.H-7 Types of Excel application

The types of Excel application acceptable for use in an organisation (in a department) should be clearly regulated. Suitable process models should be defined in the SOP for these types.

Process model for type 1: text input

Figure 9.H-9 Type 1: Text input

These types of Excel applications are rather unusual. They harbour three risks:

1. They are not what they are expected to be (i.e. they contain formulas/calculations/external references or something else that is not detectable in the printout and therefore cannot be checked adequately, and/or
2. They are too large and are not checked completely, or
3. They are regarded as part of a data system and are not checked in any way.

Sufficient instructions in the SOP help regulate the process and bring it under control. Compliance with the specified workflow has to be ensured in an appropriate manner.

Process model for type 4: Excel lists, paperalogue

Figure 9.H-12 Type 4: Excel lists

Type 4 is only introduced here in order to present and evaluate in more detail a common way of using Excel which often leads to problems.

“Excel lists” are lists or file systems. In this case, the term list refers to a document (hard copy or electronic version) WITHOUT a database-specific functionality.

Example: a list of GxP-related data used to search for GxP-related information (e. g. valid reference materials or other information required for carrying out work in a GMP laboratory).

These lists can be found almost everywhere, and they are usually uncontrolled copies. Although officially declared to be “search tools”, there content is frequently used (reading of GMP-related data (information) in connection with unprotected/non-traceable additions, changes and sorting, etc.)!

Sometimes, these lists are saved locally and referred to as “personal reference materials”. The respective user is supposed to control them and assume responsibility for them. When these kinds of lists – which take a lot of time to create and are often impossible to control – are passed on to a colleague, the approach loses any remaining credibility.

Written lists are useful, however, it takes a lot of time to create them and they are error-prone. For this reason, existing (and mostly unprotected) lists are often used.

These kinds of lists have to be updated and checked on a regular basis, and suitable measures have to be taken to protect the document and ensure traceability. In the case of lists that are used intensively during day-to-day operation, it is not possible to create a new version of the list which is authorised by the current user (➜ new version of document, e.g. printout on paper, if necessary also in PDF format) every time changes are made.

Type 4+: Electronic Excel lists without GMP compliance

As opposed to category 1 “recording system” (information is entered and printed, the printed document is checked and signed ➜ this ensures at least some GMP compliance), Excel lists are often used directly in Excel to search for information.

When typical Excel functions such as sorting, editing, deleting, filtering are used, a small “list” can be turned into a data system (data are stored, supplemented, changed, etc.). So the initially small list becomes longer and longer. This causes extended risks that require adequate protective measures, especially if a complete review is practically impossible because of the increasing amount of cells and columns.

For this reason, these “lists” – which really are collections of data – are a common GMP problem:

• No audit trail
• No authorisation concept
• No data security (no protection against changes/loss)

Suggestion by the authors:

Excel lists should be handled as type 1 documents or replaced by a data base or GMP-compliant Excel-based data system. As a temporary solution or in cases that are not critical (no GMP-related data), the situation can be improved per SOP (strict rules).

The focus should be on the following aspects:

• Size: it must be possible to carry out a complete review
• Version: the version should be updated when the list is edited
• Security: protection using authorisation (OS) or using a file password (to protect the file from being edited carelessly; this is not a reliable type of protection). Attention: incorrect use is not prevented and is often not detected (on time). This results in a high level of risk for the supported process.

To a limited extent, technical protection against accidental misuse and support for traceability by means of VBA¹ are possible and useful.

Procedural protection against accidental changes (e.g. use of Excel functions such as sorting, filtering, etc. exclusively on a fresh copy that is not subsequently saved) can also be helpful – but the fundamental deficiency that the data visible in the result is not traceable and depends on error-free use remains.

¹ VBA = Visual Basic for Applications, programming language for macros contained in Excel

Do you have any questions or suggestions? Please contact us at: redaktion@gmp-verlag.de