Blog Archives

Very simple level schemes in Excel

I worked out a very quick and easy way to generate level schemes in Excel, based on a query from one of the other students in the group. Normally I would resort to something like the astonishing TikZ for this sort of task, however our group is very much a Microsoft Office ‘What You See Is A Metaphor For Cosmic Horror‘ group and recommending that a colleague learns two new markup languages to produce a figure is probably not helpful in the short term. One of the issues with charting energy levels in Excel is that levels are typically represented by horizontal bars connected at their vertices with lines representing transitions. Whilst Excel does have a horizontal bar as a marker, it possesses two show-stopping limitations:

  1. It is only uniformly scalable, and can only be scaled so far – we cannot make it anywhere near wide and thin enough to look normal.
  2. It is only a marker for the chart vertex. Thus, lines connecting to the marker will meet at the center, which looks goofy.

An obvious workaround is to define each point in our chart twice, separated by some horizontal distance so that our chart has plateaux:

A crude energy level plot

Possible topic detected: Energy (??)

This is okay in that the levels are roughly the conventional shape, however it would be nice if we could break the style of the chart up into clearly defined levels and transitions. This turns out be be rather easy to achieve by creating extra series for each level, containing only two points. Each series turns up in your chart as bar overlaid on the first series, in Excel’s characteristic tutti-frutti colours. By tweaking the colour and style of the first series to a dashed grey, and simply making all of the other series black, we can make something Ed Tufte would be proud of wouldn’t puke at.

Various energy level formats

Amazing, Margaret!

We can add multiple levels at the same coordinate (for instance, separating out spin state energies) by simply creating two more series, like so.

Table of energy levels

The excitement never actually stops. Ever.

What about labels? Well, you can add those in manually, or you can expand each series to three points per reaction coordinate, only putting a coordinate label in the second row of each.

I hope someone finds this useful!