Geologic Illustrations Pt 2: Excel

It has been awhile since GG suggested that we need to train students in the art of illustration; sorry for that.  Anyways, let’s look at what most undergraduates (and far too many others) turn to in order to produce plots: Excel.

Now look, Excel has its uses, but making publication-quality plots is not its strength.  You can fight through it to get useful results, but things are usually easier if you master some other program that has these kind of plots on its mind from the start.

A quick preamble: this will use Excel 2011 for the Mac.  Some stupid things might be fixed in other versions….

OK, imagine a really trivial dataset:

Latitude Longitude Value
38.5 -120 1
37.6 -119.5 2
37.8 -119.2 3
38.2 -120.2 4

Say you want to make a map of the points…what do you do.  Spectacularly, some will produce this:


Ay-yup, that is a bar chart. Yes, GG has seen these turned in for scatter plots by students.  A quick change to a scatter plot produces this:


Yeech in so many ways. OK, so let’s at least get longitude on the horizontal axis and latitude on the vertical…. Now many things can be done by moving selection regions in Excel (you can rewrite equations that way, for instance) but try and grab the box outlining the latitude column and…you cannot move it sideways. Instead it is off to the Select Data  Source dialog:


Ah, the intuition this dialog inspires. Clever folk will recognize that they can change the X and Y values in the dialog boxes labelled X values and Y values, and you can even do this graphically by clicking on the little spreadsheet-y icons on the right. (What isn’t too obvious is adding a second set of points to the plot; the answer is in the “Add” button under the Series list…). Solving this gets us here:


Now most readers here are probably familiar with Excel and can reach into their bags of tricks to help make this look less horrifying, but the simple truth is that it is not obvious how to do things like move the latitude text outside the plot and get some equivalent number of vertical lines to match the horizontal ones.  Heaven help you if you want to add a degree symbol to the axis labels [yes, it can be done: get into the Format Axis dialog, choose Number, then choose Custom (you might have to uncheck “Linked to Source”), then make an appropriate custom template including your degree sign–don’t forget to have the right number of decimals!–and add to the type list, then select it.  Wasn’t that both obvious and easy?]. But most critically, this being a map, we want to set the aspect ratio so that we have something resembling a map. There is no way to force this in Excel (or 1:1 or any other ratio you might want).  You have to manually resize the plot–but be sure to wait to do it after you have added labels to axes or decided to remove the legend or any other minor change.

But you know, if you want to change the colors of all the points, or make the text into outlines, or add shadows–well that stuff is sitting in front of you in the toolbar/ribbon at the top of the page. This is part of GG’s point: Excel is designed to make simple plots with some visual oomph for making executive presentations. Most of that fluff is garbage for science. Arguably it can make things worse.

Of course Excel mavens are saying, I know how to turn off shadows, I can change symbols, I can change the limits on the plots, I have strategies for making proper aspect ratios, etc. First question: was that easy?  Second question: Does it take time you’d rather have for doing something else? But you know there are common scientific plots that are truly painful in Excel, so let’s look at a common one…

We often want to make a histogram.  Just imagine we have a column of numbers in Excel and we want a histogram of those.  Certainly there should be a histogram button, right?

Not only is there not a histogram button (have fun trying all the different types of column or bar charts in the hopes of finding one), in this version of Excel there is no way to create a histogram other than counting points on your own.  (Older versions had a tool pack that would at least allow you to be able to calculate the entries for a new table so you could make a histogram without counting yourself).

So, Excel is limited in some important ways, it is designed to make flashy graphics that may not reproduce well and so you have to work to avoid things like 3-D column plots, lousy aspect ratios, poorly place axis labels. Simply put, it is a lousy frontline tool for geoscience graphics. Unless, of course, you are making a plot for the company CFO…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: