by Mynda Treacy
(last updated October 19, 2013)
Unfortunately I see too many charts that are disguising information rather than conveying it clearly and succinctly. The most common problems are the wrong type of chart and the overuse of formatting.
Here are my all-time top 10 tips for creating better Excel charts so you can let the numbers do the talking and stop hiding your message in poorly designed and formatted charts.
Keep it Simple Stupid! You've heard it before and it's a great adage to adopt when formatting your charts. Unfortunately Excel gives us far too many formatting options and too often we bring it all into our charts at the expense of the reader and the message. For example, consider these two charts which contain the same data. (See Figure 1.)
Figure 1. Overuse of formatting in a chart.
This chart has a dark background, beveled edges on the columns, labels, years that aren't required in the dates, and a legend that is redundant since the chart is titled. We also don't need the trendline because we subconsciously detect the trendline from the height of the columns. (See Figure 2.)
Figure 2. Reduced formatting noise.
The second chart clearly conveys the message that February was the highest month and we are drawn to it through the use of a contrasting colour for the column. All of the other formatting noise has been removed as it is either duplicating information or distracting formatting which doesn't aid interpretation.
We're not decorating Christmas trees, we're visually presenting data, so tone down the Technicolor and use shades of the same or similar colors instead. (See Figure 3.)
Figure 3. The left chart uses too much color, while the right chart uses contrasting colors for effect.
Remember, when you don't overload your charts with colors you can use it to effectively bring attention to key metrics.
Borders around your charts are redundant. Save your ink and use white space between charts and tables to separate the information. Making sure your charts and tables are aligned creates a subtle, segregating effect without the need for extra lines and borders.
Don't make your readers turn their heads to read your charts. Abbreviate months and years so they can be easily read. (See Figure 4.)
Figure 4. The axis labels on the right are much easier to read than the ones on the left.
And if you're reporting on categories use a bar chart instead of a column chart so there is plenty of room for your category axis labels. (See Figure 5.)
Figure 5. Using bar charts (right) provides more room for labels than in a column chart (left).
Two fonts are enough; one for headings and one for the rest of your data. And don't go silly with them. Plain fonts that come with Excel are just fine. I like Calibri. (What can I say? It's the default.) I haven't got time to choose a different one when it does the job clearly and simply. If you prefer a change try Tahoma or Verdana.
Bar and column chart axes must start at zero. By starting your axis above zero, to create a dramatic effect, you essentially provide misinformation. This is because we subconsciously compare the height of columns and bars to one another.
Consider the fruit sales bar chart, below. In the left-hand chart the bar for apples is twice as long as oranges, but the actual difference is only 33%. Compare it to the chart on the right where the differences between data points are correctly displayed. This provides the reader with a truer picture of what you are trying to convey. (See Figure 6.)
Figure 6. Start axis values at zero for bar and column charts.
When arranging multiple charts and or tables together, like a dashboard report, make sure you align them to one another. Quickly align charts and objects using the alignment tools available on the Drawing Tools tab of the ribbon when you select a chart:
To select more than one chart simply left-click on the first chart, then hold down Shift or Ctrl to select more.
Position your most important information, be it chart or table, in the top left. Position related data together and guide the reader though your report in a logical sequence.
Understand what they want to know from the report and what action they will take. Just because you've got data available doesn't mean you should put that data in your report. If it doesn't support the purpose of the report, don't include it.
Pie charts catch your eye, but trying to get information out of them is more work than it should be and sometimes it's just impossible. Consider two charts that contain the same data. (See Figure 7.)
Figure 7. Pie charts are rarely useful.
The pie chart makes your reader flit from the legend to the pie and back and forth to figure out what color relates to what fruit. It's slow to interpret and since the slices are hard to compare the reader is forced to read the labels to make an accurate judgement as to which slice is bigger than another.
In contrast, a quick glance at the bar chart tells you the order of sales by volume. It's much quicker, more accurate, and makes the reader's life easier.
Do your readers a favor and don't serve them pie.
(Editor's note: Mynda teaches a great Excel dashboard course in which you can learn how to combine tricks like these—and more—to create your own dynamic dashboards.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12685) applies to Microsoft Excel 2007, 2010, and 2013.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel allows you to create custom chart formats that go beyond the standard formats provided in the program. These custom ...Discover More
If you need a number of charts in your workbook to all be the same size, it can be a bother to manually change each of ...Discover More
Need a chart that uses two lines for axis labels? It's easy to do if you know how to set up your data in the worksheet, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.