2006/05/fixing_excels_charts.html

Fixing Excel’s Charts

Everyone makes fun of Excel, but sometimes it’s all you have. How can we fix its dreadful graphs?

Here’s a data set showing the male and female body masses in kilograms of the ratite birds. Let’s graph it as a scatter plot. (You can download the Excel file and follow along if you like).

excel1.gif

There are a few things we can fix while in the Chart Wizard.

  • Type in sensible axis labels and title.
  • Turn off the gridlines (or, if they’re absolutely necessary, make them pale gray and dotted.)
  • Turn off the legend, since we only have one data series.

excel2.gif

Once the chart is placed on the page, this is what you get. Still a lot of work to do, isn’t there? Time to start double-clicking on various elements and altering them.

excel2b.gif

  • Give the plot area no fill and no line, instead of default gray.
  • Format line color and thickness of each axis; paler and thinner never hurts.
  • Manually choose the best minimum and maximum numbers for each axis scale to minimize waste space. I made the maximum x-axis value 120.
  • Change the font for each axis to something readable, turning off auto-scale, that very annoying feature that makes lettering unreadable if you shrink the chart..
  • Adjust the font and size of all the labels, again turning off auto-scale. I made the heading in Hoefler text plain—bold tends to be over-used in non-professional design, and in fact plain text is often more striking for headings. Georgia is a good alternative to Hoefler Text on Windows machines. Trebuchet in two sizes was used for the axes, since it’s narrow and contrasts well with the heading. Pick the labels up and move them where you need them.
  • Select the y-axis title and change the alignment so it’s rotated 0°. Insert line breaks as needed to make it narrower.
  • Select the data points, turn off the default shadow, and use a foreground or background color (the Excel equivalent of stroke and fill); you’d rarely need both. Adjust size or shape as needed.
  • Start tweaking. Here I resized the plot area so both axes used the same scale (I needed to turn off the x-axis’s auto-incrementing). Drawing a square with the drawing tools (shift-rectangle) tu use as a temporary guideline helps get the intervals on each axis the same. I also added a y=x line and text label with the drawing tools.
  • The next step would be to shift the units to the axes, by floating a “kg” text box next to each one, and then label some of the interesting outliers, such as ostrich and giant moa, with more text boxes. While it’s certainly possible to do all this in Excel, it would be more sensible at this stage to shift to a vector graphics program like Illustrator, and use it to make a self-contained EPS graphic.

So that’s a checklist of steps that can easily improve an Excel chart. Below is the cleaned-up version and the Excel default (note what auto-scale did to the labels when I resized the default chart!)

excel3.gif

Too much work to do to every graph? I agree. A future posting will provide some already-cleaned-up templates you can download and install.