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

By Chris Gemignani
April 19, 2006
Find more about: analytics excel tools visualization

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.

CleanCharts.zip

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.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License. All source code is released under a BSD License unless otherwise specified.

23 comments | Show all comments only the last 5 are shown


December 31, 2007
Simon said:

This was an excellent find on Google. Thanks a lot. One issue is that it maxes out at 6 series. I have a stacked area chart with 7 series and it made the seventh white. I only just spotted it as I proof read my report!


May 20, 2008
Andreas said:

Chris -- Just wondered if the problem I had in my December 7 post got addressed. Please advise.

Rgds,
A


May 28, 2008
Ole said:

Thanks for the cleaner. However, I couldn't make it work in Excel 2003. It did say install, and the macros are on and the menu item is there - but nothing happens when I select a chart and press. Any ideas?


May 29, 2008
James said:

Ole, I also had that issue in Excel 2003, but only on charts that aren't located in a sheet. Change the location of the chart to inside another sheet and see if that fixes it for you


June 19, 2008
Vadim said:

Installs and works great on Excel 2k, except that it mangles charts with multiple scale bars (e.g. Amount on Left Y-scale and Date on right Y-scale). But turns out if you turn of the "fix 3d" options, then it leaves the graph in tact.

Also, for a scatter chart type (useful when dealing with dates) it removes the connecting lines, but leaves them for a line chart type. Can this be fixed? Thanks.

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment






Joining Juice | Contact Us | Privacy Policy
© 2005—2008 Juice Inc.