Fixing Excel charts…Or, why cast stones when you can pick up a hammer

It’s always better to suggest a solution than criticise. Recently, we’ve slung stones at Excel’s default charts. The default colors are bad, and some of the built-in charts are "what were the smoking?" ugly.

Edward Tufte and others provide principles for making good infographics, but beating Excel’s rusty butterknife into an explanatory sword is hard. People who want to make nice looking charts waste time fixing them up. People who don’t care about making nice looking charts inflict those charts on others.

We have a solution. The "Clean Charts" tool turns hard-to-read Excel default charts into Tufte-compliant wonderwerks in a single click. Here’s what it does:

  • Removes "chart-junk" (the contrast-reducing light grey background on most Excel charts, extraneous lines
  • Formats the axes with easy to read numeric formats (22000 becomes "22k")
  • Changes series colors to an optimally chosen set that are designed for maximum contrast and readability
  • Removes 3D from the chart. 3D charts introduce distortions that make it hard for people to understand your numbers.
  • Fixes axis scaling problems.
  • Fixes font and marker sizes to make them readable if you have resized your chart

To try Clean Charts and install it, download both these files into the same directory. Then open the Clean Charts Installer.xls file with macros turned on. Follow the instructions inside the installer.

To turn macros on, go to Tools, Macro, Security. Select Medium security level. Close the workbook and re-open it. On re-opening, when Excel gives the security warning that asks if you want to enable macros, choose "Enable Macros".

The add-in puts a menu item in the Format menu. If you have a chart already selected it will say “Clean this chart…" otherwise, it says, “Clean all charts…". Select the option and you’ll get a number of ways to clean and simplify the chart.

This project is offered under the MIT License.