Categorized | excel chart

Chart Label Trick : Applying Offset Labels to Excel Chart Series




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

chart-label-trick-offset-labels

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.

chart-label-trick-data-for-chart-label

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:

chart-label-trick-adding-series

Once we are done, this is how the chart will look like. The points represent the positions where the labels will appear later.

chart-label-trick-plotting-series

Turning on the Chart Labels

chart-label-trick-y-axis-labelRight 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.

chart-label-trick-adding-labels

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).

chart-label-trick-y-axis-error-bars

Once this step is complete, the drop-down bars which extend below (or above) the chart labels will look like as shown below:

chart-label-trick-offset-labels

You can download a sample worksheet containing chart labels with offset

chart-label-trick-applying-offset-labels-to-chart-series

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. chrisham wrote:

    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.

  2. Ajay wrote:

    Chrisham,
    I think it should work in Excel 2007 too. Let me look into why that’s not happening. Welcome to da TaB.
    Regards,

  3. Ajay wrote:

    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.
    Regards,

  4. pakz wrote:

    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 !

  5. Clarence wrote:

    This 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

  6. Bram Stoker wrote:

    Like this trick, but it assumes having a floating data label at every data point on the graph.
    What 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)

  7. John Cosgrave wrote:

    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)

  1. Chart Label Trick - Using Interactive Labels on Chart | Excel & VBA - da Tab Is On wrote:

    [...] Applying Offset Labels to Chart we looked at how a chart’s labels can be offset to avoid cluttering. But what if you wanted [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel