Custom Excel Chart Label Positions

Mynda Treacy

February 20, 2020

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:

custom excel chart label positions

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:

assign the label to a ghost series

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

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:

custom excel chart label positions set up

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’:

no fill no line

The Label Series uses the ‘Value From Cells’ setting (available in Excel 2013 onward) to reference the ‘Actual’ column values:

value from cells setting

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.

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

8 thoughts on “Custom Excel Chart Label Positions”

  1. Hi Mynda
    I need to create a dashboard either in excel or in Power BI whichever you recommend.
    I have the data in excel that I download from different websites on daily basis. If you could please give me a chance to discuss it further.

    Reply
  2. Mynda,

    I’ve used this technique before but (Duh!) had forgotten all about it. Too bad as I could have used this recently.

    It’s very powerful, such as in the case where you have:
    – A horizontal bar chart,
    – With bars extending to the LEFT,
    – So that all labels are to the RIGHT of the vertical axis and left justified for easier reading.

    Reply
    • Hi Andrea,

      The alternate method is to add the labels to the ghost series, and then manually assign the actual value cells, one by one, to the labels by clicking each one twice (slowly, not a double click) to select the individual label > click in the formula bar and type = then click on the cell that contains the actual value for that label. Rinse and repeat for remaining labels. Of course this means you would need to manually update them if you add data to your chart.

      Mynda

      Reply

Leave a Comment

Current ye@r *