The LCGC Blog: An Excel Tutorial for Modeling Chromatographic Separations

August 13, 2013

The LCGC Blog

Chromatography can be approximated by a series of equilibrium events between the mobile phase and stationary phase. Here, LCGC Blog editor Kevin Schug discusses an exceptional spreadsheet macro written by an undergraduate researcher that helps users visualize the development of chromatographic separations.

In a previous blog post, I discussed fundamental aspects of the role of thermodynamics in chromatographic separations. I talked about how, although true equilibrium is never reached, the concept of partition coefficients was directly relatable to the chromatographic concepts of capacity factor and selectivity. This is one of those simple but important concepts that often gets lost in the shuffle of varying myriad mobile phases, stationary phases, and analytes during method development. Before we reinforce the elegance behind the simplicity of using partition coefficients to model chromatographic separations, I want to thank those of you who have taken the time to write me with comments following some of my past postings. In fact, this is a nice opportunity to thank one individual (who will remain nameless) who wrote to me following my previous blog about thermodynamics in chromatographic separations. The person had attended a seminar some years back during graduate school and indicated that the speaker nicely described chromatography as a dilution process. I wanted to mention this, because this makes good sense to me. As the mobile phase moves forward, it pulls the analytes contained therein with it. This movement of the mobile phase dilutes the area above the point where some of the analytes had partitioned into the stationary phase. Such a dilution would induce a concentration gradient and compel analytes in the stationary phase to partition back into the mobile phase. Of course, the analytes still have to diffuse through the stationary phase to the phase boundary.  This is where concepts like film thickness (for example, in gas chromatography) and kinetics come into play (the “C-term” in the van Deemter equation).  That is a discussion for another time, but I hope this concept of dilution helps create a picture of what is happening at the molecular scale in your mind, like it does in mine.

Moving on, I want to return to the simple notion of chromatography being approximated by a whole series of equilibrium events (“theoretical plates” if you will) between the mobile phase and the stationary phase. Not too long ago, my colleague Sandy Dasgupta wrote to me in an email about a simple Excel macro his student had written. I think that part of his excitement came from the fact that this macro was written by an undergraduate researcher in his group, Akinde Kadjo — a demonstration of exceptional potential for a student at that stage of her career. I list her name here because she is also the first author on a nice chromatography tutorial paper that she and Dasgupta published recently, which describes the Excel macro (1). When Sandy wrote me, he was also clearly excited about the simple elegance of the macro and its ability to visualize the development of chromatographic separations. I have to concur that it is exceptional, and that is why I also wanted to share it with you.

The paper begins with fitting citations to early work by Tswett at the turn of the 20th century (he coined the term chromatography, which literally means color writing, following his work on the separation of plant pigments [2]), as well as ground-breaking work of Nobel Prize winners A.J.P. Martin and R.L.M. Synge on liquid–liquid partitioning (3). The authors also give a lot of credit for early theory development to L.C. Craig who introduced and developed countercurrent extraction (4), a technique which, in the form of countercurrent chromatography, is making quite a renaissance in recent years for its preparative separation capabilities. Further credit is given to de Levie, who has more recently been a major proponent of using Excel for instruction and research and analytical chemistry (5). I suppose it is worth noting at this point that Sandy and I are new authors added to the seventh edition of (previously, solely) Gary Christian’s Analytical Chemistry textbook; in this newest edition, which will be released this fall, we have very significantly enhanced and emphasized the power and use of Excel for supplementing instruction in analytical chemistry (and Sandy has to be given a great deal of the credit for this).

After relaying a bit of complicated mathematics, Kadjo and Dasgupta settle into the principle of their Excel macro, which rests on the theory of a simple partition coefficient, K. Readers are directed to interactive figures where they can input their own values and follow the text of the article for step-by-step development of their own chromatogram. Beginning with the concepts of isocratic elution and then gradient elution (changing K over time), the paper moves quickly through the demonstration (and visualization) of a number of interesting situations, such as nonlinear adsorption isotherms, the effect of “active sites,” and column overloading.  Besides being a lot of fun to manipulate, the spreadsheets are very interactive, intuitive, and instructive. 

A subtlety of the work is being able to visualize a separation while it occurs on the column. Usually, we have to wait until the peaks hit the detector before we know if we have done our job separating the analytes. Imagine being able to monitor the separation in real time, as the analytes progress down the column. In fact, if one could do this, then measurements (peak integration and so forth) could be made right at the point where baseline separation was achieved, rather than waiting a bit longer and subjecting the system to extra band-broadening effects. Such a concept may very well be one of the ways of the future. Certainly, computerized simulation and prediction of chromatographic separations is not new (one can buy myriad commercial products). I suppose, for me, it is the transparency of the Excel spreadsheet that is alluring. I hope you will give it a try. At the very least, visit the journal website, where the figures and a simple video can be viewed: journal website.


(1) A. Kadjo and P.K. Dasgupta, Analytica Chimica Acta773, 1–8 (2013).
(2) M.S. Tswett, Trudy Varhavskago Obshchestva Estestvoispytatelei, Otdelenie Biologii14, 20–39 (1905).
(3) A.J.P. Martin and R.L.M. Synge, Biochem. J.35, 1358–1368 (1941).
(4) L.C. Craig, J. Biol. Chem.150, 33–45 (1943).
(5) R. de Levie, How to Use Excel® in Analytical Chemistry and in General Scientific Data Analysis (Cambridge University Press, New York, 2001).

Previous blog entries from Kevin Schug:

The LCGC Blog: A Closer Look at Temperature Programming in Gas Chromatography

The LCGC Blog: Back to Basics: The Role of Thermodynamics in Chromatographic Separations

The LCGC Blog: The Dimensionality of Separations: Mass Spectrometry Is Separation Science

The LCGC Blog: What Can Analytical Chemists Do for Chemical Oceanographers, and Vice Versa?

The LCGC Blog: Do Not Forget to Assess Potential Matrix Effects in Your LC-ESI-MS Trace Quantitative Analysis Method from Biological Fluids

The LCGC Blog: Derivatization

Restricted-Access Media for Biomonitoring Applications: A Solution That Deserves More Attention