Top 10 problems with Excel in the laboratory
In recent years, I have come across dozens of homemade Excel and Access systems, and although the laboratories I visit are very different, these DIY ‘solutions’ all share the same shortcomings.
Here, in no particular order, is my list of the 10 most common issues with self-built LIMS.
1. Too little authorization, structure and data validation
Is everyone allowed to access all the data? Or do you want to break down permissions into reading and editing rights? Preferably at group level? Be my guest and try that in Excel!
A simple typo can cause havoc. We know that “Product A” and “Product A” are identical, but for an Excel file, the two have nothing to do with each other. So if you try to calculate an average of all the Product A data in Excel, you might just miss a few.
2. Management of (historical) specifications
Verifying measurement data against (product-specific) specifications is one of the most important functions of a QC system. But how do you deal with changes in the specs? Is the entire history checked against the new specs? Or do the specs have to be duplicated for all the measurements, and how do you see when (and why) the spec has changed? Not an easy thing to do in Excel.
3. Limited reports
The built-in reports may have been adequate at the time the system was made, but are they fit for purpose now? How often does it happen that you need to copy data to a new Excel file for further analysis? And how often are you recreating the same graphs?
4. Finding information
You could tell yourself that a collection of 100 Excel files is actually a QC system, but it won’t feel like that when you are going through those files manually to collect batch data, and that is what Excel makes you do!
5. No change history
Analysts or operators often have to fill in their initials manually to indicate who made a certain measurement. But in more strictly regulated environments, that does not go far enough. You need formal authorization so a change can always be traced back to the person who made it (while the system regulates who has the permission to change what). Try keeping track of the what, when and why in Excel.
6. Dead links
The more extensive homemade systems that I come across have links to regulations, procedures and forms from the handbooks. Fine principles, but you better keep that workable. What about changing the file names of the documents, or moving files to another folder?
7. Inventing the wheel yourself
The nice thing about a self-built system is that you can build it yourself. The disadvantage of a self-built system is that you have to build it yourself. And that means figuring out a lot of details.
What is the difference between Cp and Cpk? And what if you only have a subspec and no upper spec? Do you really want to bother with all that? Personally, I prefer to take advantage of the knowledge of others, so I can concentrate on my own work.
8. Data not accessible to the rest of the organization
In general, self-built systems are not particularly user-friendly. Although the files are technically accessible to colleagues outside the lab, it is not always easy to make head or tail of them.
As a result, the lab often spends (=wastes) a lot of time creating reports for one or other of their colleagues. You have better things to do, and wouldn’t your co-workers prefer to generate their own reports from the system?
9. Data management is labor-intensive
In practice, the management of self-built systems turns out to be rather labor-intensive. Creating new files every period, changing years, deleting results and updating links. And also cleaning up data, copying graphs for new products. These are all hidden costs.
10. Manual input due to lack of integration
Although it is not impossible, it is rather complex to make a robust and scalable link with, for example, an ERP or MES system. Especially when there is data conversion involved. Often the result is that all kinds of logistics data (product, batch number, tank, customer and so on) have to be retyped manually.