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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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!

Data: run time against budget Data points without labels
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 3 threads Add post
07 Sep 16 at 15:57

So I've pieced together bits of code to generate a bubblechart (lsee 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!

07 Sep 16 at 16:01

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

07 Sep 16 at 15:49

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.

07 Sep 16 at 15:55

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

07 Sep 16 at 15:36

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?

07 Sep 16 at 15:38

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

07 Sep 16 at 15:40

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?

Andrew G  
07 Sep 16 at 15:41

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