Add Individual Target Lines To Each Cluster in a Column Chart
by Jon Peltier
Wednesday, December 2nd, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In I Keep Saying, Use Bar Charts, Not Pies, I described the process of making better column charts to replace a horrendous pie chart. At one point I said “Let’s add a weighted average of all responses, using a set of horizontal lines.” I neglected to share the technique for adding individual target lines, though I’ve shown similar techniques elsewhere.

Here is the data used in the chart, part of a larger pivot table.

The chart without error bars uses the first four columns: one column of category labels and three columns of favorable ratings data.

Step one is to add the fifth column (the fourth column of ratings). It is added as another column chart series.

The added series has to be converted to an XY chart type. Only XY series can have horizontal error bars, which we’ll use for the horizontal target lines. Be aware that changing the chart type is likely to add tick marks to the horizontal axis.

Having added the series and converted it to XY, we now add horizontal (X) error bars. The Excel 2003 Format Series dialog’s X Error Bars dialog is shown below; in Excel 2007 you have to add error bars from the Chart Tools > Layout tab. At first I used a Fixed Value of 0.45, but when I saw the resulting error bars, I realized they were not symmetrical. I changed the negative value to 0.48 to make the bars look the same. Excel 2007 may or may not need a similar correction.

Here is the chart with its error bars.

The end caps have to be removed from the ends of the error bars.

The XY series is hidden by choosing None for marker style (it already had None for line type). To provide a line symbol for the “All” legend entry, I inserted an em-dash at the start of the series name, changing “All” to “— All”. You can type an em-dash by holding Alt, typing 0151 on the numeric keypad, and releasing Alt.

Maybe next time I’ll show how to add target lines to a pie chart. Or not.
Related Posts:
- Conditional Stacked Clustered Column Chart with Targets
- Adding an Arbitrary Target
- Add a Target Line
- Build a Bar-Line Combination Chart
- Callout Labels with Error Bars
- Simple Box Plots
- High-Low Line Alternatives in Excel Charts
- I Keep Saying, Use Bar Charts, Not Pies
- Candlestick Alternative: Individually Colored Up-Down Bars
- Callout Labels with XY Line Segments
Posted: Wednesday, December 2nd, 2009 under Formatting.
Comments: 3
Comments
Comment from Haffy
Time: Wednesday, December 2, 2009, 7:12 am
Thanks, Jon – a nice simple one!
FYI I noticed an article on the BBC website
http://news.bbc.co.uk/1/hi/magazine/8381597.stm
and the author’s site
http://www.informationisbeautiful.net/
I don’t think he’s been reading the PTS Blog!
Comment from Jon Peltier
Time: Wednesday, December 2, 2009, 8:30 am
A lot of the “beautiful information” types are really too touchy-feely, too artistic to make numerically meaningful graphics. But those colors look nice, don’t they?
Tufte calls these people “chartoonists”.
Comment from gerdami
Time: Saturday, December 5, 2009, 9:45 am
Very nice and clean solution.
Thanks.



















Write a comment
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.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.