In Chart Label Trick – Descriptive Labels we talked about adding custom labels to chart. In this article we discuss offsetting chart labels – which means positioning the labels above or below the actual data points at a predetermined distance.
Getting the chart data in place
Like always, we first get the chart data in place. For the purpose of this chart, we will use two data series – the first one represents the sales volume for a certain period and the second one to label the data points.
The first series consists of first two columns. The second series – the series that we use for adding the labels – consists of the label descriptions, the offset that want to use (Label Offset Column) and the final label position (Label Position Column). The final label position is actually a sum of the data point (the sales in that period) and the label offset. Essentially, we have :
Sales + Label Offset = Label Position
Adding Chart Sereis
The second steps is to add the chart series. The first one is a simple line chart while the second one is an X-Y chart. The data points to be taken for the second one is shown below:
Once we are done, this is how the chart will look like. The points represent the positions where the labels will appear later.
Turning on the Chart Labels
Right click on the chart and turn on the chart labels. We will use the X-axis values being used as the labels. Once this step is done, we will have the labels appear on the chart with the offset that we specified. All that needs to be done is to add the drop-down bars.
Adding the Y-Error drop-down bars
Labels floating above or below the actual data points present an issue – there is a chance that they could be read wrongly with the label of one data point being thought of as the description of another. So we connect them to their corresponding points using a drop-down bar. Each bar will have its own height and direction so that they exactly stretch the distance between the chart and the labels. To achieve this we use the Y axis error bars to generate and align those drop-down lines. Let’s add the error bars as shown below. The range used for the error bars is the label offset that we had specified earlier (4th Column).
Once this step is complete, the drop-down bars which extend below (or above) the chart labels will look like as shown below:
You can download a sample worksheet containing chart labels with offset
Ajay, do you know if this works out in XL2007? I have downloaded your workbook and the Descriptive labels do not appear as seen on your blog.
October 5th, 2009 at 11:21 pmChrisham,
October 7th, 2009 at 12:22 amI think it should work in Excel 2007 too. Let me look into why that’s not happening. Welcome to da TaB.
Regards,
Chrisham,
In Excel 2007, you would have to right click the chart and choose “Select data”. Then in the box that appears, select Series 2 and press Edit. In the X values, select the column with “Descriptions” and in the Y values select the column with “Label Position”. That should make the chart labels appear correctly.
Thanks for highlighting this out. I will have to work a bit more to understand why the chart labels don’t appear in the first place.
October 9th, 2009 at 2:32 amRegards,
Hello,
I am not able to reciprocate your given chart in Excel 2007 , what type of chart it is in the first place I didn’t understand. Could you please provide me more direction.
PS : It looks SEXY !
April 20th, 2010 at 8:34 amThis is excellent, I have just done mine. Cool! For those of you who could not reciprocate, useaddin from the link below “Download the XY Chart Labeler 7.0.12″. It is handy!
http://www.appspro.com/Utilities/ChartLabeler.htm
Clarence
November 27th, 2010 at 1:36 pmLike this trick, but it assumes having a floating data label at every data point on the graph.
May 9th, 2012 at 8:38 amWhat can I do if I have say a hundred data points, but only want floating data labels at 5 particular data points on the graph?
(I’m plotting something akin to sales figures, and want to highlight there have been jumps/dips at particular dates/events)
If you only want to label some points then you could use the XY Chart labeller with a series that only has some data points, and similarly use a custom error bar range. (Google for XY Chart Labeller)
December 14th, 2012 at 8:09 am