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.

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


October 16, 2008
Nathan said:

I tried to use this in Excel 2003 - all my charts are in their own sheets. I got some kind of error, and had no idea how to debug, so I uninstalled it.


December 16, 2008
David said:

Since this was written in VB script, it won't work with Mac versions; well, at least not with Office 2008.


April 30, 2009
Garth said:

I installed and tried it with Excel 2003 professional and works great. Is there a simple way to change the default color scheme Clean Charts uses? Thanks!


May 7, 2009
jeff weir said:

This is great. Would be better still if it removed the legend for a one series chart - a bad example of chart junk.


August 19, 2009
Lohit said:

Cant thank you enough for this wonderful Add in
Was facing issues when the location of the chart was in a separate chart, but works like a charm when the chart is part of a sheet
Thanks again, you have a fan!!
Regards
Lohit

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—2009 Juice Inc.