BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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.

- The Problem With Labelling the Data Points in an Excel Scatter Chart (this blog)
- Setting Up the example for labelling data points in a chart
- Using VBA in Excel to Modify Charts
- Adding Data Labels to a Chart Using VBA Loops
- 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.

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:

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?!

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

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!

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

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.

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

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?

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

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?

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