Make Excel charts less terrible

There have been many articles written about how terrible the default charts are in Excel. They are full of details that detract from the actual information that you are trying to convey. I recently discovered a few great tips for easily improving the quality of your charts.  It doesn’t take much effort to set up, and it very easy to apply once you are done.

The easiest thing to do is to install the free Clean Charts Excel plugin by Juicy Analytics. This very nifty macro just finds all of the charts in your open worksheet and gives you the option of applying some more sane formatting to them. For example:

• The axis labels will all have the same number of significant digits (no more 1 1.5 2 2.5, instead 1.0 1.5 2.0 2.5).
• Axis labels are changed so that they are most meaningful (100,000 becomes 100k so you have less digits that aren’t telling you anything).
• Line and bar colors are changed to be high contrast and distinct from each other.
• The gray background is eliminated. It only reduces contrast to the chart contents.
• Superfluous lines and boxes are eliminated. You are trying to present data, so all elements should contribute to that presentation.

Here is an example of a bar chart before and after

The only caveat to this plugin is that it seems to be Windows only, which makes me sad. I’d love to use this on my mac at work, but it seems to rely on function calls to windows libraries.

The second recommendation I have for improving your Excel charts is to create some custom chart types. You know how you can choose from a list of bar charts and scatter plots? There’s a tab there for custom types, and then an option for user defined plots. The easiest thing to do here is to create a plot formatted exactly as you like it, then right click and choose “Chart Type” then “Custom Types.” Click the “user defined…” radio button and you should finally have the option to click “Add” and then this chart layout will be available to you whenever you want.

From the same site that brought you the Clean Charts plugin there is a spreadsheet with a few nicely formatted charts that you could just copy.

If you want to get especially fancy, here are a few beautiful charts that they put together in Excel to mimic the designs of Edward Tufte, the master of visually communicating information.