Make your charts look amazing!

There’s nothing worse to read and analyse than poorly made charts.  You know the ones I mean: axes with diagonal text, multi-coloured bar charts where you need to spend 20 minutes just working out which colour is which bar, and my personal favourite, pie charts, where the formatting makes you think one segment is larger than another when in fact they are the same size.  To help, I thought I would put together some hints and tips for making your charts look great

Use the correct graph type

There’s no point displaying the data at all if you use the wrong type of graph.  For example, if you’ve got more than 6 or 7 cateories, you need to use a bar / column chart rather than a pie chart, as you can see in the example below

But in general, when should you use one chart rather than another?  It’s not an easy decision.  If you are interested in knowing the various different chart types found in Excel 2010 along with their uses, it can be found in this article from Microsoft here.   Please note, most of these can be combined and adjusted to make other graphs, for example: Combined Stacked Bar charts, Box plots, Waterfall charts, Bullet charts, Thermometers and Gauges.  In simple terms though, if your data is continuous, use a line graph or area graph.  If the data is discrete, use bar / column charts and so on

Sort your data

In some instances, it’s useful to have the data sorted by value (for example in a pareto chart).  You can also do this with bar and column charts and other graphs to allow easier analysis

It could also be useful to groups your categories to allow better analysis before creating the graph.

Format the axis correctly

Never have diagonal or vertical text on an axis.  It forces the viewer put their head on one side to read it.  Instead, always keep the text horizontal.  There are several ways to do this.  For dates, you can simply show each week, or each month instead of every day

If the category is too long and cannot be simplified like dates, you can also use a bar chart for example instead of a column chart

You can also use staggered labels, using ALT+Enter.  To do this, type in the first category, then move to the next cell.  In the next cell, type Alt+Enter (to add a new line in the cell), then type in the category.  Category three should be entered in the same way as Category 1, and Category 4 the same way as Category 2 and so on

Make sure you scale the axes correctly as well.  If your data values are all between 10 and 20, there’s no point showing a scale up to 100, even if you did have data in that range a few years ago.  Make the range match what is important now.  In the two graphs below, the data is the same, but it’s much more difficult to tell in the first graph that the costs are increasing

If necessary, use a broken Y-axis if you have one or more data points that are very different from the others, otherwise the other data is difficult to read.  If you don’t know how to do this, see this great article on Peltier tech

Adjust the legend appropriately

If you are only showing one set of data, completely delete the legend.  It’s really not necessary.  If you do need a legend, move it so that it is out of the way of the data.  I tend to put it either above or below the graph, and usually if I am showing more than one graph, I try to keep it consistent

Give the axes a title

If you are going to put the chart in PowerPoint then remove the title from the excel chart and use the formatted title in Powerpoint instead.  Otherwise, put a meaningful title, that explains your data set succinctly.  For example, don’t put “Sales by region”, put “Percentage sales by region in 2013.  This also means you don’t need units for your axes and you can simplify the date format to Jan, Feb, Mar, Apr… for example, instead of Jan 13, Feb 13…

Get rid of the horizontal and vertical lines

They just add clutter to most graphs.  If you don’t want to remove them all, then at least reduce them significantly

Choose a colour scheme and stick with it

Corporate colours aren’t always the best, but go for contrasting colours, or shades of the same colour where you can.  Obviously the choice of colour is personal, but be aware that some of your viewers may have difficulties seeing reds and greens.  Don’t use too many colours on the same page, the rainbow effect is distracting

If you can use conditional formatting techniques on your graphs, do it.  This way the viewer can quickly see when the target was achieved and when not

Use simple gradients or use them sparingly for the same reason as not using too many colours on the same graph

Either get rid of the outlines around the bars / columns, and or data markers on line charts, or simplify them.  You can even add data labels and so get rid of your vertical axis if you choose.

Darker colours have more visual weight than lighter ones.  In the pie chart below, it almost looks as though there are more Birds than Fish, when in fact they are the same value

Even though Excel 2010 is much better in terms of formatting than its predecessor, the default line is never good and having multiple different markers is again distracting.

The same principle applies to fonts.  Don’t use fonts that are too distracting or too many different fonts!

Use macros or dynamic charts

To simplify your reporting and updating the reports, use macros or dynamic charts to update the chart automatically.

Adding comments to the data

If you want to be sure that the data is interpreted correctly, or if there is additional data not shown by the graph, you can add notes, but do it sparingly!  Think post-its rather than sentences  You can even simply just add arrows

Use 3D formats sparingly

Like gauges and pie charts, 3D graphs can be difficult to read.  Less is definitely more here.  Again try to keep your look consistent.  The graph below is actually the same data as one of the line charts above, but you cannot see or read the values at all (especially for series 2 and 3).

Use the camera tool or import into PowerPoint to add shadow and reflection

When you add a graph to PowerPoint, you can format it like a picture, giving you some cool effects.  However, always remember the data has to be readable.  Again, less is more.  See this post for details on how to use the camera tool.

Keep it consistent

I’ve already said this, but I will say it again.  When you are showing more than one graph, keep the same look from one graph to the next.  It makes the viewer experience easier.

 

If you are interested in any of the graphs above, you can download the PowerPoint presentation here  Graphs & Charts_AB.  All of the graphs are PowerPoint objects, but if you right click on any of them you can adjust the data.  The only exception to this are the slides which are not actually graphs, but are instead Excel tables.

Alesandra Blakeston