Showing results for 
Search instead for 
Did you mean: 

How to Not Excel with Column Logs

Team TFS
Team TFS

bookI have previously written about the need to reduce the proliferation of Microsoft® Excel® spreadsheets in the laboratory and how our Chromatography Data System (CDS) admin team can utilize Thermo Scientific™ Chromeleon™ CDS to bridge the gap (Let’s Talk About the Elephant in the Lab: Spreadsheets and Data Integrity). The latest step that we are taking is to use to manage the inventory of all our columns for our formulations team.

Currently, all our column details are stored on two Excel spreadsheets, one for HPLC columns another for GC columns (Figure 1). For each column, a unique ID is assigned and the column inventory (or log) associates multiple column details (type, dimensions, stationary phase, etc.) with that ID.

The two spreadsheets can currently be accessed by over 70 analysts who have full editing rights (including deletions) and can save at any time without any automatic traceability (there is a Standard Operating Procedure detailing the use of spreadsheets, of course!) This could be considered a data integrity issue as there is no way of knowing who has changed what, when, or why. Then, there is the unthinkable situation we had quite recently where the spreadsheet was accidentally deleted! Fortunately, we were able to recover the file from our backup, but it just serves to highlight the vulnerability of the current system.

blog-image-101220Figure 1. The Excel GC column log

With this freshly in mind, together with our knowledge of using Chromeleon Reports as a spreadsheet, we set about using Chromeleon software to manage our column logs. What we came up with is a sequence that acts as a column log, with the column ID and details recorded as Chromeleon Injection Custom Variables (ICV) (Figure 2 and Table 1). Then the column ID ICV can be added to every sequence where the column is used giving us the added benefit of keeping all things related to the chromatography and analysis together inside the CDS. Furthermore, it allows us to comply with all data integrity requirements, with all changes attributable to a user, every change logged in the audit trail, the sequence able to be locked to prevent changes, and nothing can be deleted.

blog-image-101220_2Figure 2. The new Chromeleon CDS column log


The new column entry/update process is as follows:

    • The user will add/update the details of the column(s) in the sequence.


    • The sequence is then electronically signed as submitted.


    • A reviewer then checks the information is correct and that only the expected column ICVs have been created/edited using the sequence data audit trail.


    • Then the sequence is electronically signed as reviewed.


    • Later, when other columns need adding or updating, the signatures can be removed by an appropriate manager to allowing controlled editing again.

By using the electronic signature process, we can ensure there are no changes to the sequence between column additions/updates creating a controlled, cyclical process (Figure 3). This ensures the column log is always up to date and, by using the audit trail function in connection with the electronic signatures, the reviewer can pinpoint all the changes that have been made which reduces the risk of accidental updates persisting.

blog-image-101220_4Figure 3. The column log process

This setup also enables us to use a report template that is optimized as a log sheet for each column to produce a report that can be printed for our paper logs (Figure 4).

Figure 4. Column log report template

The new CDS-based column log has many benefits over the Excel-based one (Table 2) including the ability to use the Chromeleon filtering and grouping functions to helps operators find an appropriate, active column in the column log sequence for an analysis.

It also adds the possibility for us to use Chromeleon Queries (see Brian’s previous Blog “To Query or Not Query”) to investigate column usage throughout the laboratory and eventually even look to have our column control charting contained and automated within Chromeleon CDS.

The possibilities created by leveraging Chromeleon CDS capabilities with some lateral thinking are always exciting and help us drive our compliance and lab efficiency forward. What more reason do you need to not excel with your column logs?

Find out more about Sterling Pharma Solutions and the services they offer here.