Waterfall Chart
Box and Whiskers
9 Steps to Simpler Chart Formatting
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A complaint about many charts in general, and Excel charts specifically, is that they look awful and are hard to understand. However, you have the power to make your charts clear and clean and easy to read. The way to improve your chart is to remove clutter and reduce the amount of ink used to print the chart. To do this you must keep asking: does the chart need this feature, and if so, does it need to stick out so strongly?
By “Simpler Chart Formatting”, I don’t necessarily mean easier ways to format the chart. Instead, I mean ways to make the formatting of a chart simpler, less cluttered, and easier to understand. The techniques are also pretty easy. At first, the hard part is just remembering to carry them out.
As we all know, the Excel defaults are pretty ugly. They are somewhat better in Excel 2007, but I still never use the defaults. Aside from this set of images, I will stick to “Classic” Excel, that is, Excel 2003 and earlier, but the general concepts hold true for any version of Excel, and any graphics package.
Default chart formats in Classic Excel (2003 and earlier).
Default chart formatting in Excel 2007.
1. Backgrounds
The default charts in Excel 2003 and earlier feature a dull gray background (British and Canadian versions use dull grey instead). The first step to cleaning up your chart is to change this ugly background to white.
Removing the muddy background is a major improvement.
2. Borders
How important is the border around the plot area, around the legend, or around the entire chart? The legend never needs a border. Other borders can often be removed entirely, or at least lightened.
Removing the outer border and the legend border cleans up the charts.
Removing the plot area border and gridlines makes the charts simpler.
Sometimes I use a light gray chart area border on embedded charts, so the chart border matches the gridlines between cells in the worksheet.
Sometimes a light border is okay.
3. Gridlines
If your chart uses gridlines, they should be the lightest features on your chart. The custom palette I use for my own work has a special gridline gray several shades lighter than the 25% gray on the default palette. But often gridlines are not even needed.
Above: Dark gridlines (left) and light gridlines (right).
Below: No gridlines and no plot area border.
4. Axes
De-emphasize the axis lines by using a light or medium gray instead of black. You could even use medium gray for the axis tick text, but I usually stick with black text. For a category X axis, you can eliminate tick marks.
If you are using gridlines, you may be able to remove the corresponding axis line, leaving only the tick labels.
You can often unclutter a chart by using fewer tick mark labels. As pointed out in the comments, keeping the unlabeled tick marks is useful.
5. Number Formatting
Use custom number formats to reduce the complexity of axis labels and other text in the chart.
Above left: original cluttered axis labels.
Above right: remove unnecessary ‘cents’.
Below: replace thousands by ‘K’ suffix.
6. Chart Types
Replace every 3D chart with the corresponding 2D type.
Only use pie charts if there are three or fewer wedges. Use column or bar charts for more categories.
Cluttered pie (above left) and simpler pie (above right).
Unclutter a pie chart’s data by using a column or bar chart.
Use column charts to show values for discrete categories. Use line charts to trends over time.
7. Series Formatting
For column, bar, and area charts, remove the black outline. Use light to medium colors for fills, and don’t use patterns or gradients. Use darker, saturated colors for lines and markers. Use colors with appropriate contrast against the background and compared to other series colors. Avoid combinations that react with each other.
Too gaudy (left) and too faint (right).
Better color combinations.
8. Horizontal Text
Vertical text is difficult to read, and inclined text on a monitor is bad because of distortion of the characters.
In a column chart, Excel will leave out some tick labels if they would overlap, but this leaves the readers guessing about the missing labels.
Turn the whole chart on its side.
9. Labels, not Legends
It is easier for a reader to identify series if they are directly labeled. Using a font color that matches the series formatting is also helpful. A legend takes up valuable space and makes the reader divert attention back and forth.
Top left: Ineffective Legend. Top right, bottom left and right: Effective Labels.
Possibly Related Posts:
- Add a Target Line
- Highlight Certain Time Periods in a Chart
- How to Build a Simple Panel Chart
- Integer Values on Line Chart Category Axis
- Magazine Quality Chart (Economist)
- Adding an Arbitrary Target
- Clustered-Stacked Column Charts with Vertical Separators
- Overlapped Bar Chart - Longer Bars in Back
Posted: Monday, October 13th, 2008 under Formatting.
Comments: 27
Comments
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
Read the PTS Blog Comment Policy.
Comment from Doug Glancy
Time: Tuesday, October 14, 2008, 12:09 am
Jon, thanks for the clarification on the Commonwealth background colors :). Seriously, this is a good reference.
I don’t do many charts but have worked on some lately for other people at work. They specified pie charts, but I had taken all the comments about the ineffectiveness of pie charts to heart, and designed some pretty clean bar charts. I stopped somebody in the hall and asked them what the charts meant. I then realized one critical fact about pie charts: people know immediately what they mean, i.e., that the slices are part of a whole. They don’t necessarily get that with bar charts. I’m sad to admit the finished product contains pie charts.
Comment from Anonymous
Time: Tuesday, October 14, 2008, 2:55 am
You can often unclutter a chart by using fewer tick marks.
I definitely like to unclutter a chart with fewer tick mark labels, but I often find the ticks themselves to be harmless enough at the density chosen by Excel by default, so I reduce the labelled (long) ticks, then introduce unlabelled (short) ticks to restore the original density.
If I’m feeling particularly energetic, or the scale warrants it, I sometimes use dummy axis techniques to simulate a three level scheme, long labelled, long unlabelled, and short. I find this particularly useful for long horizontal axes where I don’t want labels overrunning each other, but don’t want to lose the accuracy of the scale.
You can easily mark multi-century-long time series this way, with labels every fifty years, long ticks every ten years (5:1), and short ticks every two years (5:1 again). Points are identifiable to the exact single year by whether they fall on a tick or between ticks.
Another item on my never-ending list of demands to MS is that they implement the three-level tick scheme as default to avoid the workaround. I’m also inclined to think that they should reference each lesser tick by how many there are to the next level up, instead of absolute size, since 99% of applications will involve the same simple small integer ratio.
Comment from ExcelPro’s chart blog
Time: Tuesday, October 14, 2008, 3:59 am
It ’s quite great. thank you !
To my surprise, as a expert , a maestro of excel,you write these basic things yet. Is it a part of your new book?
Comment from Jon Peltier
Time: Tuesday, October 14, 2008, 6:49 am
Anonymous -
Is that you Derek? Good point about the tick marks. I’ve adjusted that section of this post to reflect this.
Comment from derek
Time: Tuesday, October 14, 2008, 7:35 am
Yes, well-spotted (it was the never-ending list of Excel wants that gave me away, no doubt). I don’t know my details weren’t automatically filled in that time.
Comment from Hadley
Time: Tuesday, October 14, 2008, 8:29 am
“But usually gridlines are not even needed.” - I’d disagree. Usually tick marks aid in the assessment of patterns. See e.g. Section 3 of W. Cleveland. A model for studying display methods of statistical graphics. Journal of Computational and Graphical Statistics, 2: 323–364, 1993. URL http://stat.bell-labs.com/doc/93.4.ps. Tufte’s advice is generally good, but he often goes too far and removes things that actually are important. See e.g. W. A. Stock and J. T. Behrens. Box, line, and midgap plots: Effects of display characteristics on the accuracy and bias of estimates of whisker length. Journal of Educational Statistics, 16(1): 1–20, 1991.
Otherwise, great advice!
Comment from Jon Peltier
Time: Tuesday, October 14, 2008, 8:43 am
Hadley -
Maybe I should have said “sometimes” instead of “usually”. I admit to using gridlines frequently, maybe about half the time.
I concur that Tufte sometimes goes too far towards minimalism with his data/ink philosophy, and he tends to blame the tool for the sins of the artisans. The tool does have some ill-advised defaults and capabilities that ought to be ignored, and there is no easy way for the craftsmen to learn the proper use of the tool.
This was originally going to be “10 Steps…”, but I only came up with nine good topics. Perhaps #10 should be something along the lines of “your mileage may vary” or “like all rules, these may be selectively and carefully ignored”.
Comment from Steve
Time: Tuesday, October 14, 2008, 11:04 am
Great article, thanks for taking the time to write it. I have a question about labels vs. legends. I’d like to implement the label on the last point like your example shows, but I’m a bit confused about how to do this with excel 2002. Is it possible?
Thanks,
Steve
Comment from Jon Peltier
Time: Tuesday, October 14, 2008, 12:00 pm
Steve -
Select the series, then select the last point, right click and choose Format Data Point, select the Data Labels tab, and choose the Series Name option (I believe Excel 2002 introduced these options). If this option is not available, simply use any of the options, then edit the resulting label.
You can right click on the data label to bring up its distinct formatting dialog.
Comment from Tony Rose
Time: Tuesday, October 14, 2008, 12:03 pm
Number 8 is such a simple tip yet not done often enough. I see text at crazy angles so it fits (sort of), but you end up tweaking your neck trying to read it.
Number 9 is one that comes at a perfect time. In the next day or two I have a post that shows how to label line graphs instead of using a legend. Stay tuned.
Great list! And I like the fact that it’s a list of 9. Everyone uses a list of 10… and ten is overrated. Nine is the new ten.
Comment from JP
Time: Tuesday, October 14, 2008, 7:19 pm
I’m embarrassed to see how many of these I am guilty of, especially #6 (pie charts) and 8. But charting was never my strong point; I’m sure someday when I need to do it, I’ll figure all this out.
–JP
Comment from Steve
Time: Wednesday, October 15, 2008, 7:19 am
Thanks for your reply John,
Using other tips on this site I have a chart that auto updates as new data is added. I want to use labels like your example 9, but see that as the chart auto extends to include the new data it automatically adds a new label meaning my last 2 values have labels and it all looks a bit ugly.
So I have some VBA that deletes all but the last label, I thought I’d share it.
ActiveSheet.ChartObjects("Chart 8").Activate
For i = 1 To ActiveChart.SeriesCollection.Count
For j = 1 To ActiveChart.SeriesCollection(i).Points.Count - 1
on error resume next
If ActiveChart.SeriesCollection(i).Points(j).HasDataLabel Then
ActiveChart.SeriesCollection(i).Points(j).DataLabel.Delete
End If
on error goto 0
Next
Next
I’m not convinced by the “on error resume next” bit in there, but I’m seeing errors if I try to check for the existence of a label using the hasdatalabel property where I have empty cells.
Anyway, this might be useful for someone. Thanks again for your post.
Cheers,
Steve
Comment from Jon Peltier
Time: Wednesday, October 15, 2008, 8:02 am
Steve -
Thanks for the code. I actually have a similar routine that labels the last point in a series:
Comment from derek
Time: Friday, October 17, 2008, 6:10 am
Back when I did this sort of thing monthly, I knew even less VBA than I do today. I dealt with the “moving end label” problem by entering the data back-to-front, and graphing them backwards. Entering a new month meant copy-inserting the first row into a new second row, then changing the first row. Easier to do than to describe.
Comment from Jon Peltier
Time: Friday, October 17, 2008, 7:02 am
Derek -
I was going to suggest this as an alternative, then I tried it with my sample data, and it didn’t work. I was using dates and a date axis, which Excel sorts internally before plotting, so the first point ends up being the last point, and the label doesn’t keep up with it.
However, one alternative I’ve used (maybe I should save it as another post) is to make a new series which plots hidden points using the X and Y values of the last point in each series. Through the magic of OFFSET and LOOKUP, these points always link to the last points in each series.
Pingback from Links sobre charts de Excel « Optimización y Estadística
Time: Saturday, October 18, 2008, 12:13 am
[...] 9 Steps to Simpler Chart Formatting [...]
Pingback from Excel Links of the Week - Birthday Edition | Pointy Haired Dilbert - Chandoo.org
Time: Monday, October 20, 2008, 2:04 pm
[...] 9 steps to better chart formatting [...]
Comment from jenmoocat
Time: Tuesday, October 21, 2008, 12:25 pm
Huzzah!
Great post.
Now if I could only get everyone here at work to adopt these simple changes!
Comment from Jim Beckwith
Time: Thursday, October 23, 2008, 1:28 am
Jon
Thanks for your very helpful blogs. I have been trying to write code for re-formatting charts. I’ve been battling trying to work out how to change the font size of the axes numbers (xlValue and xlCategory). I would have thought that something like this might have worked:
ActiveChart.Axes(xlValue).Select
With Selection.Font
.Size = 8
End With
Have you got any ideas on how the font size can be changed?
Jim
Comment from Jon Peltier
Time: Thursday, October 23, 2008, 7:15 am
Jim -
Whenever I get stuck, or I’m too lazy to consult the object Browser, I record a macro:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/23/2008 by Jon Peltier
'
'
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End Sub
This can be cleaned up:
Sub ResizeAxisTickFont()
' Macro cleaned up 10/23/2008 by Jon Peltier
With ActiveChart.Axes(xlValue).TickLabels.Font
.Size = 8
End With
End Sub
Comment from Jim Beckwith
Time: Thursday, October 23, 2008, 6:18 pm
Thanks again for your help, Jon. Very much appreciated!
Actually, I had tried to record the macro to format both axes. This is is how it turned out:
Sub Macro6()
‘ Macro6 Macro
ActiveSheet.ChartObjects(”Chart 1″).Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects(”Chart 1″).Activate
ActiveSheet.ChartObjects(”Chart 1″).Activate
ActiveChart.Axes(xlCategory).Select
End Sub
Is there a setting that I’ve got turned off that causes the macro recorder to miss internal operations once the Axes(xlValue) is selected? Or is this just a quirk of Excel 2007?
Best regards, Jim
Comment from Jon Peltier
Time: Friday, October 24, 2008, 12:02 am
Jim -
Excel 2007 uses new shapes for the elements of its charts. Development of these new shapes was finished late in the process of finishing Office 2007, and there was not time to incorporate them into the macro recorder. When you record a macro involving shapes, the recorder misses everything, and when a recorded macro deals with charts, it misses almost all formatting. There is enough compatibility with previous versions that code recorded in 2003 will (mostly) work in 2007, and the formatting code in 2003 will still produce thee equivalent formatting in 2007.
Comment from Gordon
Time: Thursday, October 30, 2008, 9:38 am
Great tips there Jon, I’ve made this standard reading for all involved in creation of charts at my workplace.
Comment from George
Time: Wednesday, November 5, 2008, 5:08 pm
Great site–lot’s of good advice, and this article is no exception! Tip #8 concerns me, in that some scientists and engineers are taught to read a chart with the x-axis as the independent variable and the y-axis as the dependent. Flipping them willy-nilly may lead to false conclusions about what the author is trying to present. I suggest this one be included in the category of “consider when appropriate”.
Comment from Jon Peltier
Time: Wednesday, November 5, 2008, 5:47 pm
Hi George -
Even scientists and engineers should be flexible enough to get the swapping of dependent and independent axes, and in fact, they would probably understand more quickly that the vertical axis in a horizontal bar chart is the X axis in an Excel chart.
Of course, a bar or column chart is generally inappropriate for most scientific or engineering charts, eh?
Comment from Zakaria
Time: Saturday, November 8, 2008, 9:42 am
Hi John,
Just want to thank you for your great website. Keep up with the good work.
Comment from Del
Time: Monday, January 5, 2009, 7:49 am
Definitelly, it’s a great example for those improve step by step. good job!
Write a comment