When you plot multiple series in a chart the labels can end up overlapping other data. A solution to this is to use custom Excel chart label positions assigned to a ghost series.
For example, in the Actual vs Target chart below, only the Actual columns have labels and it doesn’t matter whether they’re aligned to the top or base of the column, they don’t look great because many of them are partially covered by the target column:
The solution is to assign the label to a ghost series which is based on the maximum value of the Actual and Target series, giving the effect below:
Note: The colour coding of the labels associates them with the actual column, so the reader knows which series they related to.
Watch the Video
Enter your email address below to download the sample workbook.
Custom Excel Chart Label Positions – Setup
The source data table has an extra column for the ‘Label’ which calculates the maximum of the Actual and Target:
The formatting of the Label series is set to ‘No fill’ and ‘No line’ making it invisible in the chart, hence the name ‘ghost series’:
The Label Series uses the ‘Value From Cells’ setting (available in Excel 2013 onward) to reference the ‘Actual’ column values:
Now all you need to do is format the label font colour to match the Actual column so your reader knows what series they refer to.
Tip: If necessary, go one shade darker. This will make the labels easier to read but they’ll still appear to be the same colour as the columns.