10 Commandments for Excel Charts
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Leave your own comment:
Comments for this tip:
malek 20 Jan 2014, 15:39
Totally agree with SheyanB on the CHOICEST of the pie charts.
Exploded pie chart.
Add category AND percent labels.
The negative pie arguments don't fly when this chart is used.
It's also very well accepted and is better than a bar or column chart in some applications. The exploded pie chart makes both a visual and informational statement; it is not confusing.
Other types of pie charts maybe should be buried; this isn't one of them...
MTreacy 08 Jan 2014, 01:46
Thank you for all your comments and feedback.
@Bryan I couldn't have said it better.
There will always be pie chart fans (and multiple colour fans etc.) and if they write your pay cheque and you can't educate them in better visualisation techniques, then by all means give them what they want until you find a boss who will aid in your growth rather than hinder it.
It's interesting the number of comments about colour blink issues. I have been meaning to write about some solutions for this for over a year. Perhaps I should do it sooner rather than later.
Bryan 03 Dec 2013, 11:26
@Sheyan: Jon Peltier just started crying right now wherever he is, and he has no idea why. I can only hope your comment was a joke, because it's pretty much exactly what all the data is heavy-hitters tell you *not* to do.
SheyanB 02 Dec 2013, 15:26
Really don't know why so many are against PIE charts. Tweak your pie...
1. Explode your pie chart.
2. Add both: Category and Value labels.
Done this way, makes a good, quick visual of the information you're trying to present. And no one is confused.
Shreepad S M Gandhi 23 Oct 2013, 08:03
Thanks to all for your wonderful inputs. Mynda no doubt has some valid and practical tips in her above write up.
I have often come across users complaining about Excel that it doesn't plot a graph as expected. While trouble shooting, I had to tell them that the type selected was incorrect.
For example, if it is required to plot a curve for data in two columns to study the behaviour progresseively the chart type selected was Line Chart, which will never give the desired output. The right choice would be X Y (Scatter). I enjoyed the smile on the faces of this complainants after changing the type from Line to Scatter. I do not know the reason but this is true misconception observed many a times.
Bryan 22 Oct 2013, 13:26
It's interesting to see all the talk about color-blindness. Usually color-blindess is cited as the reason to *use* one color, not avoid it! In Jeff's example, if someone can't tell the difference between red/green, then how would using multiple colors help a color-blind person differentiate the data? Conversely, if a person sees the color blue how I see the color gray, they should still be able to distinguish pale/normal/heavy hues. Pattern fills definitely level the playing field between the color-blind and the normal-visioned users -- neither one can read it very well!
"Rather than using different hues to differentiate them, if you use a single hue but vary its intensity from light to dark or pale to bright, even someone who is color blind will have no problem distinguishing them." Stephen Few: http://www.perceptualedge.com/articles/Whitepapers/Dashboard_Design.pdf
Joe 22 Oct 2013, 11:11
There are many color blind selectors on the web. I use those, download them and set up palettes for future use.
Unfortunately, when 508C is fully met, it can violate Rule #2.
When I removed much of the additional info, as is Rule #1, my clients had difficulty. As more is removed, it can take more effort to understand. "J" requires a moment of thinking, while "Jan" is standard and requires none. Often it is better to supplement with a small companion chart to draw out meaning, such as a sparkline for trends to make the point.
Rule #2: What are all those different shaded bars for in the second chart? Different years? Different products? Unless your client knows what they are for, label them.
These are good rules to keep in mind, but only as general guides, they are not hard rules by any means.
Paul 22 Oct 2013, 09:48
In light of Jeff's comment on the color blindness, instead of using different shades of the "same" color, one could use different pattern fills (of course as long as the solid color does not overemphasize something you don't want to overemphasize).
marco 21 Oct 2013, 11:36
Oranges should be orange, bananas should be yellow. I'm cool with red for the apples :)
Carol 21 Oct 2013, 11:17
Very good point Jeff! Hadn't thought of the color issues for some. Thanks!
Jeff 21 Oct 2013, 10:19
As a color blind male, I do take exception to Tip 2. Shades of the same color frustrate me, and my mind tells me to ignore the data and move on. My mind is frustrated trying to differentiate between the shades.
My suggestion would be to use primary colors, so all can easily discern the differences.
If the group you're presenting to contains more than about 5 males, chances are at least one of them has some color "issues" like me.
Anne 21 Oct 2013, 08:44
There's a way to use color effectively. For instance, using varying shades of one color. But it also depends on the data. Sometimes, contrasting shades are not effective when there are multiple points represented.
I personally like to use 3D effects. But they should be used on a limited basis and with subtlety, and using lighter hues. The example used above for Tip 1 is extreme. I wouldn't think that most excel users would go with that option.
I usually like to design my own charts by going deep into the design options and formatting each aspect of the chart. I find most of the canned options that excel provides somewhat garish.
Generally, charts should be used keeping in mind your audience and message. I like to think that I can use color and effects and keep my message plain and simple.
Saying that, another reason to keep your charts vanilla is to keep the file size manageable.
Bryan 21 Oct 2013, 08:29
WRosocha: there are some situations where pie charts CAN be useful, but those situations are so few and far between that it's better to just say "never use them" and follow up with the exceptions. (Unfortunately, as well, so many people are used to pie charts that they want them even if they aren't the most clear -- in your case you should give the clients what they pay for!)
For an investment portfolio, I might use a pie chart with only two colors (or hues of the same color): one for conservative investments and one for aggressive investments. Then within those two colors you can break up the segments by individual investment. Your customers probably don't care so much that they have 12% invested in small cap and 13% invested in foreign exchange, they want to know that 25% is risky.
Jorge Camoes (in the data-vis blogging world, he's definitely the most pro-pie I've found) has a lot of discussion about when and when not to use pie charts: http://www.excelcharts.com/blog/chart-types/pie-charts/
And of course we can't forget Mynda's article: http://www.myonlinetraininghub.com/worlds-most-accurate-pie-chart
Rudra: Unfortunately, sometimes it's best to give customers and management what they want, even if it's not what's best (they do, afterall, write your paycheck). That being said, I've also gotten the "wow" factor from a well-presented Tufte-compliant (or in this case, Mynda-compliant) graph.
Rudra Sharma 21 Oct 2013, 05:55
Useful tips, but I can't say I agree with all the points made over here.
Colors and contrast of charts got me wow!!! from my clients and manager.
R Paterson 19 Oct 2013, 17:47
I struggle with charts. Everything in this tip is a winner love it and will take all on board.
WRosocha 19 Oct 2013, 12:46
Pie charts are IMMENSELY useful.
They can quickly display and communicate proportions.
In my investment analyses, my clients can quickly see what proportion of their portfolios are invested conservatively or aggressively. You can use colours effectively for this purpose and you can also attach percentage labels to the segments.
I can't think of a better way than pie charts for this purpose.