10 Commandments for Excel Charts

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.

Tip 1: KISS

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.

Tip 2: Use Color to Emphasise, not Blind

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.

Tip 3: White Is the New Black

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.

Tip 4: Axis Labels Should be Horizontal

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

Tip 5: Don't Go All Fancy with Fonts

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.

Tip 6: Don't Lie with Your Charts

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.

Tip 7: Alignment

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.

Tip 8: Location, Location, Location

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.

Tip 9: Know Your Audience

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.

Tip 10: Pie Charts are Fools Gold

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.

Author Bio

Mynda Treacy

Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas list, and popular Excel Blog. ...

MORE FROM MYNDA

Linked Combo Boxes

Want to add a great way to interact with your worksheet users? Try adding combo boxes that can modify the information shown ...

Discover More

Dynamic Text Boxes

You probably know that text boxes can contain text. (Else why call them text boxes?) Did you know that you could make that ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (RIBBON)

Dynamic Data Based on Chart Changes

Change the data on which a chart is based and Excel obligingly updates the chart to reflect the change. What if you want to ...

Discover More

Using Go To to Jump to a Chart Sheet

Create a chart on its own worksheet, and you can display it by simply clicking the tab at the bottom of the Excel work area. ...

Discover More

Changing Chart Size

Place a chart on a worksheet and you may not be satisfied with its size. Changing the size of a chart is a simple process ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share