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
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
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
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
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
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.