Data integrity challenges in using spreadsheets for your environmental monitoring

20 October 2020

Whether you love spreadsheets or loathe them, we’ve seen plenty of examples of organisations using them for their environmental monitoring data. With an infinite number of cells, you can store a great deal of data and since Excel is so widely used, your team are likely to be familiar with the system to some extent. However, there are also some major drawbacks to the use of spreadsheets for your environmental monitoring, which could lead to a lack of trust in your data.

In the pharmaceutical and healthcare industries, a lack of data integrity could lead to consequences from external scrutiny to patient harm, as it is vital to trust your environmental monitoring data to ensure products are safe. In this blog, we will explore how using spreadsheets for environmental monitoring could make meeting data integrity guidelines a challenge.

The ALCOA+ Principles

If you have read any of our other blogs, you should by now be familiar with the ALCOA+ principles; introduced by the FDA in the 1990s and since adopted by regulatory bodies worldwide, these principles underpin data integrity guidelines for bodies such as the MHRA. The principles state that for facilities to have trust in their data, records must be: attributable, legible, contemporaneous, original, accurate, complete, consistent, enduring and available. A breach of even one of these principles could mean your data integrity is at risk, and if you use Excel for your environmental monitoring, even the best-organised spreadsheets and biggest Excel whizzes may unknowingly be doing just that.

Let’s go through each of the principles to demonstrate how you could be breaching ALCOA+, and therefore putting your environmental monitoring data at risk.

Attributable

This principle states that all data must be identifiable to the person or system from which it was first generated, as well as the time of data collection. Whilst this sounds straightforward, if you use Excel in your environmental monitoring, it could present some challenges, as you are likely to also use paper at some stage in the process before transcribing your data to your spreadsheet for trending. These multiple transcription stages increase the chance of human error, but also, whose name appears on the final record? Is it the person who originally collected the data on the paper record and the corresponding time, or the name and time for the person who is transcribing? In either case, you could be missing a vital piece of information as if an error is found, you may be unable to pinpoint when this happened. With Excel, there is also no easy way to create an audit trail so original data could be overwritten with changes; therefore, the record would not show the original time or person who recorded the data, or who made the change.

Legible

Legible states it should not be possible to alter data without an audit trail preserving the original record, which is challenging to do in Excel. This principle also requires data to be easy to read, understandable and permanent; a well-organised spreadsheet may meet these first two points, though with the potentially huge amount of data collected for environmental monitoring, making data easy to understand could be tricky for someone with average Excel skills. Likewise, with large spreadsheets, the possibility for data loss is high should the spreadsheet crash. A more robust system would be required to handle this.

Contemporaneous

To meet this principle, the time of data collection should accurately correspond with the time of recording, whether this is for an original record or later correction. This can be breached for spreadsheet users as, similarly to the point made under attributable, a record entered in the spreadsheet is not original so the time of recording would be different to that at the point of original collection.

Original

Original records should be preserved rather than relying on copies. This is important as each time the original copy is transcribed, there is a higher likelihood of human error, potentially impacting on the accuracy of your data. If you originally record onto paper and then re-transcribe on to Excel, it is this re-transcription you will use should you need to retrieve information and for your trending, whilst your original paper record is filed away. If an error occurs when re-transcribing, it will be this which shows up in your trends – can you therefore trust what you see?

Accurate

Data should be free from errors, truthful, complete and reflect reality. As discussed above, unless you enter your data directly into Excel then there is an increased chance of human error through multiple transcription stages, meaning your data may not be accurate. In addition, you could have an incomplete data set from crashes of the file and your trends may not reflect reality if the data is not free from errors.

Complete

A robust audit trail is required to show no information has been lost or deleted, and any amendments to the original set of data should be visible. To create a complete audit trail on Excel as we have already mentioned is a challenge; additionally, original records are often overwritten with any amendments meaning original data is lost, breaching this principle in addition to others.

Consistent

For data to be consistent, it should be chronological with timestamps for amendments to the original record. On Excel, data may not be chronological if a pile of the original paper records to be transcribed is muddled – especially if there is a delay between data collection and transcription. Of course, you can filter on Excel to display records chronologically, however, given the nature of the data sets being so large, this could lead to crashes. Additionally, time stamps are not visible for amendments.

Enduring

For this principle to be met, data should be safely stored in a manner which will be long available after recording. We have already mentioned the tendency of large Excel files to be slow and crash, which could lead to data loss; however, considering the principle that original records should be used and copies not relied upon, it is arguable more important to look at the paper records for this principle. Of course, paper is easily damaged, misplaced and stored in files which can be muddled. If you use Excel for your environmental monitoring but paper to originally take the record, breaching this principle is challenging to avoid.

Available

The final principle states that data must be accessible when needed. You may think this is easy on Excel, as you can simply filter or search the spreadsheet for the data you need; however, when we consider again that the files are usually large datasets, filtering or searching could be slow and lead to crashes. This would make any records you need difficult to retrieve. The nature of Excel is also such that it is often only possible for one person to be using a spreadsheet at a time; this itself can lead to a lack of availability when required.

A better way of environmental monitoring

By now, you should be able to see how using spreadsheets in your environmental monitoring could have consequences for your data integrity, with challenges around crashing and data loss as well as a dependence on paper to keep the original record. Luckily, there are solutions available to help provide an alternative, and we think better, way of environmental monitoring. One of these solutions is Microgenetics SmartControl.

SmartControl has been developed to help you to better meet MHRA data integrity guidelines, so you can have trust in your data. With our paperless environmental monitoring software, it takes just 5 seconds to enter a sample directly into the user-friendly interface, meaning no more re-transcribing of data. This not only saves you time but increases accuracy by minimising the risk of human error, means the original record is always used and the data correctly corresponds with the time and name of the individual who first recorded it.

SmartControl also provides you with a complete audit trail, showing any amendments made to the original record. Since the software comes with a high-resolution photobooth, you can also capture an image of every plate to form part of this record. Finally, as SmartControl is a cloud-based environmental monitoring system, all records are easily accessible for an unlimited number of users, from any location, making your data available.

The process of environmental monitoring is open to a number of risks in addition to the ones set out in this blog, which could threaten your data integrity; click here now to read our whitepaper to find out more, and what you can do to help.