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