Adding data labels to a chart using VBA
Part one of a five-part series of blogs

Adding basic data labels to a chart can be done without using any programming code. This blog, however, shows you how to fine-tune Excel chart labels using VBA (Visual Basic for Applications) macros.

  1. The Problem With Labelling the Data Points in an Excel Scatter Chart (this blog)
  2. Setting Up the example for labelling data points in a chart
  3. Using VBA in Excel to Modify Charts
  4. Adding Data Labels to a Chart Using VBA Loops
  5. Improving the VBA system for chart data labels

Posted by Andrew Gould on 08 June 2011 | 12 comments

The Problem With Labelling the Data Points in an Excel Scatter Chart

Labelling the data points in an Excel chart is a useful way to see precise data about the values of the underlying data alongside the graph itself.  In a column chart, for instance, you might show the value of the data point at the top of a column.

Example of a column chart

A useful addition to a column chart is a set of data labels showing the value of each column.

In certain types of charts, for instance scatter charts or bubble charts, the most useful way to label the data points is apparently impossible to achieve!

We can plot the Run Time against the Budget... ...but we can't show the Film Titles in the chart!

The only three options you have when adding a data series to a scatter chart or bubble chart are:

  • The value of the X axis (the Run Time value in the above example).
  • The value of the Y axis (the Budget value in the above example).
  • The name of the data series (the word "Budget" in the above example).

If you choose to show all three options, the resulting chart looks something like this:

A messy chart

It's messy, difficult to read, and it's not even the information we want to see!

There are two ways to get the Film Title in the data labels:

  • Identify which data point represents which film and then manually type in the film title.  Seriously!
  • Use a bit of VBA magic to automatically label each data point with the click of a single button.

Guess which method we're going to describe in part 2 of this series?!

This blog has 12 comments

Comment added on 21 May 2012 at 13:02 GMT

Hi, thank you for your post it was very helpfull but i have some problem with multiple series in the same chart.

Can you please post code and explain how you add labels for multiple series?

//Thank you

Reply from Andrew Gould
I've posted a solution here!
Comment added on 03 September 2012 at 10:49 GMT

I don't agree with the way you're assigning chart labels on the assumption that the cells match the sequence of chart items. Can you guarantee that these two pieces of data are always going to be aligned that way.

Surely it's better to do something like this...

    For chartIterator = 1 To chartDataSeries.Points.Count
        chartDataSeries.Points(chartIterator).dataLabel.Text = Format(chartDataSeries.Values(chartIterator), "#0%")
    Next

This way the data series point label is populated from the same data series' values collection. I've used formatting here to display a percentage as this is what I want to show on this particular chart.

Reply from Andrew Gould
I see what you mean, but that wouldn't work for this example because the names of the films aren't contained within any of the chart data series.  The two series in the chart are the Run Time and the Budget and we could display those as data labels without having to use VBA at all.  The only reason we're using VBA here is to label the chart with values that are stored in cells rather than in the chart itself.
Comment added on 31 October 2012 at 10:44 GMT

Hi Andrew,

Extremely helpful.

What about for multiple series with different numbers of data points in the same graph?

 I.e. series 1 has 4 different films, series 2 has 3 etc..these each would therefore have different labels.

The column with the filmlist contains the labels in the right order to correspond with the data points in each series.

The solution you posted for multiple series seems to reset the counter for each series and go back to the top of the film list.

Do you know how to solve this?

Reply from Andrew Gould

Hi Alex,

 

Thanks for the comment.  You’re right about the labels in my example repeating – the idea is that each film name has multiple data points associated with it, so you’d expect to see each film appear more than once.  The counter doesn’t actually reset however, and the list of films is looped over only once.  What happens is that for each film in the list, every data series in the chart is looped over and a single data point in each series is labelled.  So all the data points associated with one film are labelled before moving on to the next.

 

For your example it depends on exactly how you have your data arranged.  It sounds as though you have a list containing one column of names that you want to use as labels, next to one or more columns of values you’re using as data points.  Your single list is used to create multiple data series in the chart, with each series using a different number of rows from the list.

 

If that’s the case then we need to loop over the collection of cells containing the labels just once to add a label to each data point.  The problem you’ve got is knowing when you switch from one data series to the next.  One solution would be to keep track of the number of data points we’ve labelled and check to see if we’ve exceeded the number of data points in the current data series.  If that happens we can reset our counter and move on to the next data series.  We’ll also need to keep track of the number of data series we’ve labelled.  The code below should do the trick, assuming all of the above:

Sub CreateDataLabels()

    'variables for looping over chart objects
    Dim FilmChart As Chart
    Dim FilmDataSeries As Series
   
    'variables for looping over cells
    Dim SingleCell As Range
    Dim FilmList As Range
   
    'variable to keep track of number of films
    Dim FilmCounter As Integer
   
    'variable to keep track of number of series
    Dim SeriesCounter As Integer
   
    Set FilmList = Range("A2", "A11")
    Set FilmChart = ActiveSheet.ChartObjects(1).Chart
   
    'loop over each data series and enable data labels
    For Each FilmDataSeries In FilmChart.SeriesCollection
        FilmDataSeries.HasDataLabels = True
    Next FilmDataSeries
   
    FilmCounter = 1
    SeriesCounter = 1
   
    Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
   
    'loop over each cell in the list of source data
    For Each SingleCell In FilmList
       
        'change the label text to be the film's name
        FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
       
        FilmCounter = FilmCounter + 1
       
        'if the FilmCounter is higher than the number of points in the series, reset the FilmCounter
        'move on to the next data series
        If FilmCounter > FilmDataSeries.Points.Count Then
            FilmCounter = 1
            SeriesCounter = SeriesCounter + 1
           
            'if we've exceeeded the number of data series in the chart then exit the loop
            If SeriesCounter > FilmChart.SeriesCollection.Count Then Exit For
           
            'if we're still in the loop, store a reference to the next data series
            Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
           
        End If
       
    Next SingleCell
   
End Sub

Comment added on 31 October 2012 at 14:48 GMT

Thx Andrew, you are a genius!

One last thing, to extend this to all charts in the sheet (assuming that the series and labels are the same), I assume I should insert:

"For Each SingleChart In ActiveSheet.ChartObjects" at the start of the macro and "Next SingleChart" at the end, would this be correct?

 

 

Reply from Andrew Gould

Yep, that's pretty much it!  Don't forget to declare a new variable to hold each single chartobject as you process the loop.  You'll also want to grab a reference to the chart part of each chartobject as you go through the loop.  I've marked the NEW and EDITED lines with comments below:

Sub CreateDataLabels()

    Dim FilmChart As Chart
    Dim FilmDataSeries As Series
   
    '***NEW LINE***
    Dim SingleChartObject As ChartObject
   
    Dim SingleCell As Range
    Dim FilmList As Range
   
    Dim FilmCounter As Integer
    Dim SeriesCounter As Integer
   
    Set FilmList = Range("A2", "A11")
   
    '***NEW LINE***
    For Each SingleChartObject In ActiveSheet.ChartObjects
       
        '***EDITED LINE***
        Set FilmChart = SingleChartObject.Chart
       
        For Each FilmDataSeries In FilmChart.SeriesCollection
            FilmDataSeries.HasDataLabels = True
        Next FilmDataSeries
       
        FilmCounter = 1
        SeriesCounter = 1
       
        Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
       
        For Each SingleCell In FilmList
           
            FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
           
            FilmCounter = FilmCounter + 1
           
            If FilmCounter > FilmDataSeries.Points.Count Then
                FilmCounter = 1
                SeriesCounter = SeriesCounter + 1
               
                If SeriesCounter > FilmChart.SeriesCollection.Count Then Exit For
               
                Set FilmDataSeries = FilmChart.SeriesCollection(SeriesCounter)
               
            End If
           
        Next SingleCell
       
    '***NEW LINE***
    Next SingleChartObject
   
End Sub

Comment added on 01 November 2012 at 14:06 GMT
Beautiful! Thx a lot.
Comment added on 02 January 2013 at 14:32 GMT

Hi Andrew- thanks so much! :)

Quick question: I used your macro in one of my sheets to add dates to my data points. However, the labels came up in an American format (mm/dd/yyy) rather than in the format the data labels are in in the cells (mmm-yy). When I try to right click on the labels and change the format, despite me selecting other formats, the labels won't change. Do you have any suggestions as to how I can edit the code so the format is mmm-yy?

Many thanks! :)

Reply from Andrew Gould

Hi,

Yep, you should be able to change the format of the date using the Format function. Try setting the DataLabel.Text property to be equal to

Format(SingleCell.Value, "mmm-yy")

Hope that helps!

Comment added on 23 January 2013 at 18:30 GMT
Hey Andrew,

thank you for the post. However, I have tried it and Excel tells me "Run-time error 438":

Object doesn't support this property or method

And, if I try to debug, it highlights this line

FilmDataSeries.Points(FilmCounter).DataLabels.Text = SingleCell.Value

The setup of my db is exactly the same as yours. Can you help me here?

Thank you in advance for your help.


Reply from Andrew Gould
Sorry you're having issues!  As a starting point for resolving the problem could you download the completed example from the bottom of Part 5 of the series and just check that you can run the code in there without receiving the error message.  When you've done that get back to me and we'll see what else we can do!
Comment added on 05 July 2013 at 16:43 GMT
Hi Andrew

Thanks your solution is really helpful.

I have managed to label all my data points, but because my data points at the beginning of the chart are very close together I would like to move all data point labels below the x-axis and have the text vertically aligned rather than horizontally.

Is that possible?

Thanks for your help!
S
Reply from Andrew Gould

Changing the text orientation is straightforward enough, you can change the Orientation property of the DataLabel object, like so:

FilmDataSeries.Points(FilmCounter).DataLabel.Orientation = xlDownward

Instead of using a constant like xlDownward, you can also use any whole number between -90 and 90.

Positioning the labels below the x axis is a little more difficult. You'll need to make sure that there is enough room between the bottom of the plot area and the bottom of the chart area for the labels to be displayed. You'll then need two lines of code, one which sets the horizontal position of the label and one which sets the vertical position.

For the horizontal position I assume that you want the label to be in line with the data point?  If so you can set the Left property of the label to be equal to the Left property of the data point:

FilmDataSeries.Points(FilmCounter).DataLabel.Left = FilmDataSeries.Points(FilmCounter).Left

For the vertical position you need to set the Top property of the label to be equal to the Top property of the X axis:

FilmDataSeries.Points(FilmCounter).DataLabel.Top = SingleChart.Chart.Axes(xlCategory).Top

Like I said, you'll need to make sure there's enough room below the plot area otherwise the labels will be 'pushed up' into the chart area.

Hope that helps!

Comment added on 24 July 2013 at 22:25 GMT

Thx Andrew! getting closer to solving my dilemma, but what if the data label I want to show is a percentage? I've got my macro set to pull in the text, but it's pulling "12%" in and displaying it as "0.12". I can't seem to find out how to change the formatting since it is recognizinf the value as text and not a number. Any suggestions?

Reply from Andrew Gould

Can you use the Format function?  Something like:

FilmDataSeries.Points(FilmCounter).DataLabel.Text = Format(SingleCell.Value, "Percent")

Give that a try and let me know if it works!

Comment added on 05 August 2013 at 21:18 GMT

Andrew - Thank you!  This series has been a huge help to me.  I have one more question - what do I need to change in the code if my chart is in a separate worksheet from the data?  I have it all working when in the same worksheet, but am struggling to connect the labels when the scatter chart is on its own page.

I am new to programming macros, so sorry if this has an obvious answer.

Thanks in advance.

Reply from Andrew Gould

Even with experience this isn't necessarily an obvious answer!

When you have charts embedded within a worksheet they are part of the ChartObjects collection of that sheet.  When your chart is on its own page it is part of the Charts collection of the workbook.

If you have a single chart called Chart1 and you wanted to adapt the code at the bottom of Part 4 of this series, all you need to do is change this line:

Set FilmDataSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

To this:

Set FilmDataSeries = Charts("Chart1").SeriesCollection(1)

Of course, if your chart isn't called Chart1 then you'll need to modify the name. Otherwise it should just work as normal.

If you had a set of charts to update and you wanted to loop over them, you could adapt the code in Part 5 of the series to the following:

Sub CreateDataLabels()

    Dim FilmDataSeries As Series
   
    Dim SingleCell As Range
    Dim FilmList As Range
   
    Dim FilmCounter As Integer

    Dim SingleChart As Chart

    Set FilmList = Range("A2", Range("A2").End(xlDown))
   
    For Each SingleChart In Charts
       
        FilmCounter = 1
       
        Set FilmDataSeries = SingleChart.SeriesCollection(1)
   
        FilmDataSeries.HasDataLabels = True
       
        For Each SingleCell In FilmList
       
            FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value
            FilmCounter = FilmCounter + 1
           
        Next SingleCell
   
    Next SingleChart
   
End Sub

Comment added on 18 September 2013 at 15:13 GMT

Hi Andrew,

First of all, thank you, this is a great first step in the solution to my current problem, however I now have one final issue.

I am working on a spreadsheet where I find it necessary to scatterplot over 1800 values, and the text that appears over/beside the data points occludes them and makes the plot very difficult to read.

I was wondering if you have any suggestions for hiding these values in any sensible way?

I must admit I am not entirely familiar with the VB language and so have struggled a bit.

Regards

Reply from Andrew Gould
Hi Finlay, I think that the easiest way to create a scatter plot without displaying data labels is not to use VBA at all! The code in this blog was written to solve a very specific problem related to the display of certain values in XY Scatter charts and Bubble charts in Excel. If you don't want to display data labels on your chart you can simply create it in the normal way: Go to the Insert tab of the ribbon and choose Scatter. If you do end up with data labels you can then just head to the Layout tab of the ribbon while you're working on your chart and use the Data Labels option to turn them off. Hope that helps!
Comment added on 07 November 2013 at 10:34 GMT
Hi Andrew

Great help thus far, I appreciate your open, friendly approach! So I've pieced together bits of code to generate a bubblechart (look up Tom Hollander's bubblechart post) with auto labels (you might recognise that bit) but I need them to not overlap and still be linked to each bubble. I've only found code to fix overlapping line charts not scatter/bubbles and I was hoping leader lines might provide an easy option to link points to labels, but in excel 2010 they're only available for pie charts. Any guidance appreciated!
Reply from Andrew Gould

Hi, This is a tricky problem to solve with no simple, one-click solution available. As you've discovered, there's no lead-line option for bubble charts and, even if there were how would you move the labels other than manually?

The horrible solution that I've posted below loops over the labels for each data series and checks to see if each label overlaps another. If so it moves the label up a tiny amount and keeps doing this until it no longer overlaps. It's complicated and hopelessly ineffective (most of the labels end up sitting nowhere near their respective data points) but hopefully it highlights the complexity of this type of problem. Given enough time I suspect you could write a clever enough algorithm to place each label exactly where you want it but I suspect that it would simply be easier to move each one by hand!

Sub CheckIfLabelsOverlap()

    Dim FilmChart As Chart
    Dim FilmDataSeries As Series
    Dim OtherFilmDataSeries As Series
    Dim FilmPoint As Point
    Dim OtherFilmPoint As Point

    Set FilmChart = ActiveSheet.ChartObjects(1).Chart
   
    For Each FilmDataSeries In FilmChart.SeriesCollection
        For Each FilmPoint In FilmDataSeries.Points
           
            For Each OtherFilmDataSeries In FilmChart.SeriesCollection
                For Each OtherFilmPoint In FilmDataSeries.Points
                    If FilmPoint.Name <> OtherFilmPoint.Name Then
                                               
                        Do While LabelsIntersect _
                            ( _
                                FilmPoint.DataLabel.Left, _
                                FilmPoint.DataLabel.Top, _
                                FilmPoint.DataLabel.Width, _
                                FilmPoint.DataLabel.Height, _
                                OtherFilmPoint.DataLabel.Left, _
                                OtherFilmPoint.DataLabel.Top, _
                                OtherFilmPoint.DataLabel.Width, _
                                OtherFilmPoint.DataLabel.Height _
                            )
                       
                            FilmPoint.DataLabel.Top = _
                                FilmPoint.DataLabel.Top - 1
                        Loop
                       
                    End If
                Next OtherFilmPoint
            Next OtherFilmDataSeries
        Next FilmPoint
    Next FilmDataSeries

End Sub

Function LabelsIntersect _
    ( _
        Left1 As Double, _
        Top1 As Double, _
        Width1 As Double, _
        Height1 As Double, _
        Left2 As Double, _
        Top2 As Double, _
        Width2 As Double, _
        Height2 As Double _
    ) As Boolean

    Dim Right1 As Double
    Dim Bottom1 As Double
    Dim Right2 As Double
    Dim Bottom2 As Double
   
    Right1 = Left1 + Width1
    Bottom1 = Top1 + Height1
    Right2 = Left2 + Width2
    Bottom2 = Top2 + Height2

    LabelsIntersect = False
   
    If ((Left2 >= Left1) And (Left2 <= Right2)) _
        Or ((Right2 >= Left1) And (Right2 <= Right1)) Then
       
        If ((Top2 >= Top1) And (Top2 <= Bottom1)) Or _
            ((Bottom2 >= Top1) And (Bottom2 <= Bottom1)) Then
            LabelsIntersect = True
        End If
       
    End If

End Function

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.