1. Skip to navigation
  2. Skip to content
  3. Skip to sidebar

Our Blog

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

Chris Gemignani

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.

Related posts
Topics:
, , ,
  • http://moustacheanalytics.wordpress.com/2007/03/20/slide-design-vs-noise-rena-slides-och-diagram-for-basta-forstaelse/ Slide design vs Noise -Moustache Analytics

    [...] Se även Juice Analytics exempel på hur excel-grafer kan tvättas för att bli lättare för ögat. Tyvärr så är ju default-funktionen i Excel vad gäller diagram och grafer att fylla dem med linjer och färger som inte känns 100%. Därför är rådet att skapa dina egna standardgrafer som du kan använda (Customized graphs) det bästa du kan göra. Utan linjer på staplarna, 3D-effekter, gridlines bakom och med naturtrogna färger blir graferna mycket mera lättförståeliga. Jämför graferna nedan som har samma data men där den vänstra är default med Excels färger och den andra är gjord med mina val. [...]

  • http://moustacheanalytics.wordpress.com/2007/03/20/slide-design-vs-noise-rena-slides-och-diagram-for-basta-forstaelse/ Slide design vs Noise – Rena slides och diagram Moustache Analytics

    [...] Se även Juice Analytics exempel på hur excel-grafer kan tvättas för att bli lättare för ögat. Tyvärr så är ju default-funktionen i Excel vad gäller diagram och grafer att fylla dem med linjer och färger som inte känns 100%. Därför är rådet att skapa dina egna standardgrafer som du kan använda (Customized graphs) det bästa du kan göra. Utan linjer på staplarna, 3D-effekter, gridlines bakom och med naturtrogna färger blir graferna mycket mera lättförståeliga. Jämför graferna nedan som har samma data men där den vänstra är default med Excels färger och den andra är gjord med mina val. [...]

  • http://jonplummer.com/archives/90 » Knocking Excel 2007 down a peg or two » Jon Plummer

    [...] Excel 2007 makes some flashy charts. Charts that are maybe too flashy, and requiring of much clicking and prodding to simplify. But why cast stones when you can pick up a hammer? [...]

  • David Mudge

    Very nice. Simple to install, simple to use. Just one issue: when I used the “Remove 3D” option, my nice line charts are converted into column charts, which is not wanted.

  • Peter Basch

    So, I follow instructions, turn security (in Excel 2003) to Medium, open the workbook, and get this:

    This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.

    What am I doing wrong?

    Thanks, P B

  • http://techcommdood.blogspot.com techcommdood

    This is quite impressive. I’m really liking it!

  • Aaron

    Looks appealing, but I couldn’t make it work on Mac, Excel 2004… Any hints?

  • http://juiceanalytics.com Chris Gemignani

    Aaron, We don’t have a copy of Mac Excel 2004 to test it on.
    If you’re a code jockey or you know someone who is, the Visual Basic code is available and it is open source.

    Another post you may be interested in that should work on the Mac: http://www.juiceanalytics.com/writing/2006/08/tufte-charts-in-excel/

  • tim

    I like it – if only your idea of ‘nice colours’ and my idea where the same… ;o)

  • Andreas

    Clean Charts.xla

    The xla file does not download at all rather it appears to open in my Firefox browser.

    Please advise.

    Also, will Clean Charts work with Excel 2000?

    thx.
    A

  • andrew

    Brilliant – I use it often. Thank you for sharing it.

  • Andreas

    Sorry, I accidentally submitted this in another thread,it belongs here. I hope someone can help…

    I’ve tried downloading the two files:

    Clean Charts Installer.xls
    Clean Charts.xla

    The xla file does not download at all rather it appears to open in my Firefox browser.

    Please advise.

    Also, will Clean Charts work with Excel 2000?

    thx.
    A

  • Oliver Hofmann

    Same problem with the Mac Excel version — getting a runtime error 53 (file not found: user32) which seems to indicate it’s looking for a particular window? Did any Mac user get this to work yet?

  • http://juiceanalytics.com Chris Gemignani

    Oliver: I don’t think it works on Mac. The code is available if any Mac Excel expert wants to take a shot.

    Andreas: I believe it will work in Excel 2000. Sorry to hear about your downloading troubles. I’ll take a look at the server side and see if I can figure anything out.

    Tim: Hey, they were “scientifically chosen”. You might like the colors in our Chart Chooser (http://juiceanalytics.com/writing/2007/11/introducing-chart-chooser/) better. I do.

  • http://juiceanalytics.com Chris Gemignani

    I’ve joined the two Excel downloads into a zip file. This should solve the downloading woes some have experienced. Thanks for your patience.

  • Andreas

    Chris — Just got around to downloading the zip file and trying to install the add-in as per the directions. When I open the .xls file and select “Enable macros” I then get an error message stating “File error: data may have been lost”.

    Please advise.

    Thx,
    Andreas

  • Andreas

    Chris — I also just realized that after I close the above described error message window and look at the Clean Charts Installer.xls file, sheet 1, it has no buttons. Also, after making a chart and selecting “Clean this chart form the format menu, it cleans the chart but leaves with no way to select the chart again.

    Please advise,
    Andreas

  • Chris

    I have the same problem as comment 16. I also don’t get buttons like 17. I’m running Office 2000.

  • Simon

    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!

  • Andreas

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

    Rgds,
    A

  • Ole

    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?

  • James

    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

  • Vadim

    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.

  • Nathan

    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.

  • David

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

  • Garth

    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!

  • jeff weir

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

  • Lohit

    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

  • Visual

    I am looking for a way to change the default colors similar to those used in the Chart Chooser web site. Can someone please help?

  • Bill

    Very nice improvement in both 2003 and 2007. The results are much cleaner looking. Many thanks.

  • Slackademic

    Is there a chance that you would develop a Mac version of this tool now that Excel 2011 has Visual Basic?

  • Pingback: CleanCharts: Excel add on creates better looking charts with a single click | freewaregenius.com

  • Pingback: Create Better Looking Excel Charts With Clean Charts

  • Pingback:   Create Better Looking Excel Charts With Clean Charts by Ekschi Technology

  • Pingback: Create Better Looking Excel Charts With Clean Charts | e News Online

  • Pingback: Create Better Looking Excel Charts With Clean Charts | e News Online

  • Pingback: Create Better Looking Excel Charts with Clean Charts « WTI NewsBlog

  • Pingback: CleanCharts: Free Addon Tool for Excel That Makes Your Charts More Visually Appealing

  • Pingback: Fix, Format, And Clean Multiple Chart Elements In Excel 2010 Workbook

  • Pingback: Creare diagrammi in Excel più attraenti | Consulente Informatico - Sergio Gandrus

  • Pingback: Dramatically Improve the Appearance of Your Excel Charts and Graphs | BNET

  • http://www.astwoodconsulting.co.uk/ excel training in london

    Excellent post.Really very impressive.I enjoyed while reading your article a lot.simple short and very descriptive.Thanks for sharing such article with us.

  • http://pulse.yahoo.com/_CVRZGLKVPIGZCJ253WWLMV7AQU drm

    better and faster idea
    http://www.officearticles.com/excel/set_a_default_chart_in_microsoft_excel.htm

    basically, make a chart, format it the way you like.  then right click, select chart type, and put a checkmark next to the default formatting box.

  • Attila

    The strange thing is that if I apply the CleanChart command on a graph and save the document, some of the formatting is lost when I reopen the file again. It is the colors of the lines in a Line chart and a Bar chart that are reverted back to standard MS Office colors. My graphs are linked to pivot tables so it could be that when I automatically refresh the data the graphs (lines/bars) are redrawn and the formatting is lost. So how do I avoid this?

  • http://www.westcoastdrugs.net/ Online Pharmacy

    Awesome post.Well
    written.I enjoyed reading your article.Many Thanks for sharing.Keep sharing
    in future too.

  • http://www.lapeches.com/ Lingerie

    Smart written and shared post.
    Excel 2003 professional really works great.Thanks a lot for sharing.Keep sharing as always.

  • Eyeofdali-graphics

    This is exactly what I need. I got it to work once, then when I went back to try it again, the option is grayed-out—for any chart I try this with. What am I missing? Thanks so much! //

  • http://www.technibond.co.uk/ simontufel

    nice article it is very good for all 
    the  viewer who get lots of information regarding it.

  • Li jing

    can it apply to excel 2010?

  • regi

    hi I just down loaded the clean field zip and get a run-time error’53:
    File not found user32

    Any clues