Skip to content
Accueil » [Case study] Which Excel system to manage quality control?

[Case study] Which Excel system to manage quality control?

The implementation of quality control processes is essential from the start of a production or distribution activity of food products. However, at first, some Excel files as quality control software can be used to plan and follow the analyses.

Until when should I use Excel for my CA?

In this article, we offer you some strategies that we discovered while talking with quality control managers in the food industry.


According to the EC 178/2002 regulation, « un exploitant du secteur alimentaire est le mieux à même d’élaborer un système sûr de fourniture de denrées alimentaires et de faire en sorte que les denrées alimentaires qu’il fournit sont sûres. Il y a lieu par conséquent que la responsabilité juridique primaire de veiller à la sécurité des denrées alimentaires lui incombe ».

It means that “a food business operator is in the best position to develop a safe system for supplying food and to ensure that the food it supplies is safe. It should therefore have primary legal responsibility for ensuring the safety of food.

To ensure the sanitary control of their productions, food companies have the obligation to set up a Quality Management System and to control their products. If it is logical to rely on certain analyses performed by trusted and validated suppliers, the contaminations of the last weeks confirm that it is fundamental to control the products…

After having discussed with dozens of quality managers, here are some advices that we can share with you for the implementation of a laboratory management software in Excel:

KEEP IT SIMPLE !

Excel is a fantastic and extremely powerful software.

You can do a lot of things, from the simplest to the most complicated. The problem is that you need to know Excel well to develop and update a system and that on the other hand, many users are allergic to columns, rows and other tabs.

If you develop a system too much in Excel, you are confronted with a double problem: Excel is no longer used for what it is supposed to be(data processing) and we freeze the system because it starts to be difficult to modify it.

If the person who developed the tool leaves the company, it is very complicated to take over.

For this reason, you should keep your Excel system as smple as possible. Avoid having many files communicate with each other, avoid manipulating files that are too large, avoid trying to automate everything at the risk of creating a giant with feet of clay.

Different strategies

The quality managers have understood this and they develop Excel systems according to their needs. Here is the presentation of 4 Excel strategies that we see most often to create its own quality control management solution.

Product strategy

It is the simplest strategy.

It is “simply” necessary to create an Excel file for each product.

A first tab allows you to create a specification sheet with the analyses to be performed and the expected results. A second tab allows you to record results by batch. It is quite easy to automate the edition of purchase orders and certificates of analysis.

The obvious advantage is to centralize all the information, all the analyses and all the data related to a product, or even a family of products, in a single file. This makes it possible to build a history of results, to analyze trends, to automate the edition of analysis certificates.

On the other hand, it forces to create as many files as products, which becomes painful when you handle a lot of items (raw materials, intermediate products, finished products). The files become very heavy and the queries take time. You also spend a lot of time updating all your files because they are independent of each other.

Advantages: Suitable for limited product catalogs. Allows you to centralize data and automate

Disadvantages: Many files to handle. Heavy files to perform queries

Yearly-based strategy

We see this organization most often in companies that operate by monitoring plan. It is “only” necessary to qualify your suppliers and products. Then, you proceed by random control on products and parameters according to the defined risks.

This is an ideal strategy to predict and plan what you will analyze in a year.

On a file, you list all your products and the frequency of controls. In the columns you indicate the analyses.

Then, when you receive a new batch, you refer to the control plan to know and decide what you have to analyze. A small green or red box allows you to indicate if the periodicity is respected and you can record all the results or the numbers of the tested batches.

The main disadvantage is that to avoid the system being too cumbersome, it is simpler to work with one file per year. This obviously poses difficulties in building and consulting results histories.

It is also more complicated to automate the creation of purchase orders and certificates of analysis. That’s why this strategy is mainly used by finished goods companies that simply need to release products into their ERP.

Advantages: 1 global file for all products. Planning and overview of analyses

Disadvantages: No analysis history. Difficult to automate the system.

Step-by-step strategy

This technique is used by companies that carry out many internal or external controls with larger quality teams.

It is “only” necessary to create a first file for the control plans per product. Another file to register samples, order and follow the analyses. A last file to record the results.

This allows a good control of the control plan and to build a history of results. Having different files allows several people to work together without being read-only. On the other hand, you need to be an ace with Ctrl +C / Ctrl +V to copy information between the different spreadsheets.

Advantages: Teamwork, analysis follow-up

Disadvantages: Lots of input, manual COA

Strategy by analysis

This is the last Excel strategy that we have identified, although we see it more rarely. It’s no longer about operating by product, or by year, but by analysis and laboratory. One file for pesticides, one for heavy metals, one for microbiology, etc. This strategy is used by companies that perform many analyses, whether in QC, R&D or for stabilities.

It permits to simplify the follow-up of deadlines and laboratories. It is also easier to cross-reference and compare results.

Obviously, the main drawback is that you find your products on several different Excel files. This means that you have to keep several files up to date and enter the information several times. Also, to edit a certificate of analysis, you have to compile the information in different places.

Advantages: Easier to follow the analyses. Possibility to cross the results and the trends

Disadvantages:  Products broken down into several files. A lot of data entry.

 

In conclusion, unless you set up a real Excel database and therefore go beyond the conditions of optimal use of the software, your system will inevitably be missing a dimension. It is up to you to choose the most suitable approach.

However, if you want a structured and automated system, Excel is no longer suitable. In this case, you have to choose an adapted laboratory management or quality control software. LIMS (laboratory management software) are often disproportionate to the needs of industrial laboratories. The quality modules of ERP are mainly made for online or systematic controls, or even for batch release. However, in addition to the investment required, they are not adapted to the management of control plans and the follow-up of subcontracted laboratories.

CIKLab is a simple and pragmatic software for managing quality control and monitoring analyses. It is particularly adapted to the needs of industrialists who manage periodic control plans and test their products internally and externally.

CIKLab allows you to gather all your data in a single system and automate the control process. Do not hesitate to contact us for more information.