Callout Labels with Error Bars
by Jon Peltier
Tuesday, April 14th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Replacement for and Oil Price Radial Chart I used error bars as callouts, to connect labels to points on a line chart.
The technique is actually pretty simple. Here is the line chart, unadorned by any labels.
The data you need to label two points is shown below. The dates are the same as the corresponding line chart data points. The Y values (under “Labels”) are spaced above the line chart data points by the lengths of the error bars (under “Error Bar”).
Copy the first two columns of the data range shown above, select the chart, and use Paste Special to add the data as a new series.
Add custom negative Y error bars using the values in the table (see Error Bars in Excel Charts or Error Bars in Excel 2007 for assistance). Also, apply the labels in the last column of the range to this new series using Rob Bovey’s Chart Labeler.
Hide the added series (no markers and no lines), and format the error bars to remove the end caps.
With a lot of labels, I positioned the labels to the side of the hidden data points, as above. If it isn’t so crowded, you can center the labels above the points, below.
In an upcoming post, I’ll show how to add callouts using XY series to add line segments oriented at any angle, not just the horizontal or vertical line segments afforded by error bars.
Related Posts:
- Callout Labels with XY Line Segments
- Simple Box Plots
- Draw an Axis With Tick Marks But No Line
- Clustered-Stacked Column Charts with Vertical Separators
- High-Low Line Alternatives in Excel Charts
- Candlestick Alternative: Individually Colored Up-Down Bars
- Add a Target Line
- Error Bars in Excel 2007 Charts
- Add Individual Target Lines To Each Cluster in a Column Chart
- Conditional Stacked Clustered Column Chart with Targets
Posted: Tuesday, April 14th, 2009 under General.
Comments: 6
Comments
Comment from wayne
Time: Tuesday, April 14, 2009, 3:14 pm
What is your opinion on using the drawing toolbar to add text boxes and the vertical lines and then grouping the chart with these drawings in order to form the final chart for reports in word purposes?
Comment from Jon Peltier
Time: Tuesday, April 14, 2009, 4:00 pm
Wayne -
These drawing objects are not tied to any particular values in the chart, but for a static report, they are fine.
However, I would not follow exactly the procedure you describe. Instead, I would select the chart prior to clicking on the drawing tool. In this way, the drawing object is created in the chart, not in front of the chart. You will not have to do any grouping, and only copy the chart and paste it.
Comment from Danièle
Time: Thursday, April 16, 2009, 8:46 am
Thanks again Jon for your “elegant” callout labels as well as the extra tip about the drawing objects created in the chart rather than grouped with the chart.
Danièle
Comment from Stijn
Time: Friday, April 17, 2009, 9:12 am
Hi Jon,
This is indeed a nice solution; thanks for sharing it with us.
However, I tried to apply the steps listed above, but when I paste special the ‘events’ as a new series, the dates of those events are not interpreted correctly because the X values of the original (fuel price) data series are used. It seems like this is because a line chart is used.
Can you clarify what I’m doing wrong?
Thanks,
Stijn
Comment from Jon Peltier
Time: Friday, April 17, 2009, 10:16 am
1. Make sure the date axis of the original line chart uses Days as the Base Unit.
2. Add the data (X = dates) as a new series.
3. Convert the added series to XY type, then assign error bars.
Pingback from Excel Deja Vu All Over Again « Charts & Graphs
Time: Wednesday, June 17, 2009, 8:43 pm
[...] trends. The topic looked familiar, I got a mild deja vu feeling. A few days later, Jon wrote a 2nd post explaining how he used error bars as leader lines to connect his notes to the data points on his [...]
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.