30.11.2021 | LOGFILE Feature 45/2021

Process Models for the Validation of Excel Applications

Process Models for the Validation of Excel Applications

8 min. reading time | by Thomas Trantow, PhD

 

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

Features/examples Handling/process notes GAMP® 5 category
Tabular data processing, without formulas or calculations Creation of documents (with review) 1

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

Features/examples Handling/procxess notes GAMP® 5-category
List of GxP-related data, e. g. the valid reference materials or other (GMP-related) information required for carrying out work in a laboratory.

Uncontrolled use in many GMP laboratories – often declared as "not to be used for GMP purposes" because they are unprotected and uncontrolled, but used on a daily basis.

Based on the actual use of such lists (repeated updating and subsequent printing), the requirement for "complete review of the printed document" or "version control and appropriate protective measures" follows (Excel as a recording system, type 1).

Unprotected Excel files are often used for reference during day-to-day operations. This approach does not comply with GMP standards (type 6).

1

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)

Saving data is a critical process which is covered in many regulations (from Annex 11 of the EU GMP Guidelines to FDA 21 CFR Part 11).

A violation of the requirements for stored data can quickly become the cause of action during an inspection!


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


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

 
Thomas Trantow

Author

Thomas Trantow, PhD
Pharmaceutical quality control Consultant
E-Mail: trantow@analytik-service.de
 
GMP Compliance Adviser

GMP Compliance Adviser


Simplify your GMP business!
  • Save time when you are looking for regulations and interpretations
  • Enjoy the safety of the global leading GMP knowledge database
  • Current GMP information & regulations at your fingertips
  • Stay informed with our latest news:
    We check and observe 50+ websites of regulatory bodies, associations, etc. on a weekly basis

Updates: All regulatory changes are covered, minor or big. With a high frequency update rate of 6x a year, you are always up to date.


> More information and order
 
 

Comments