Tutorial: Simulating chromatography with Microsoft Excel Macros

Chromatography is one of the cornerstones of modern analytical chemistry; developing an instinctive feeling for how chromatography works will be invaluable to future generation of chromatographers. Specialized software programs exist that handle and manipulate chromatographic data; there are also some that simulate chromatograms. However, the algorithm details of such software are not transparent to a beginner. In contrast, how spreadsheet tools like Microsoft Excel™ work is well understood and the software is nearly universally available. We show that the simple repetition of an equilibration process at each plate (a spreadsheet row) followed by discrete movement of the mobile phase down by a row, easily automated by a subroutine (a “Macro” in Excel), readily simulates chromatography. The process is readily understood by a novice. Not only does this permit simulation of isocratic and simple single step gradient elution, linear or multistep gradients are also easily simulated. The versatility of a transparent and easily understandable computational platform further enables the simulation of complex but commonly encountered chromatographic scenarios such as the effects of nonlinear isotherms, active sites, column overloading, on-column analyte degradation, etc. These are not as easily simulated by available software. Views of the separation as it develops on the column and as it is seen by an end-column detector are both available in real time. Excel 2010™ also permits a 16-level (4-bit) color gradation of numerical values in a column/row; this permits visualization of a band migrating down the column, much as Tswett may have originally observed, but in a numerical domain. All parameters of relevance (partition constants, elution conditions, etc.) are readily changed so their effects can be examined. Illustrative Excel spreadsheets are given in the Supporting Information; these are easily modified by the user or the user can write his/her own routine.