USING EXCEL VBA TO LABEL MULTIPLE SERIES IN A CHART

This follow-up article to a previous blog describes how to label the data points in multiple series in Excel using VBA.

You can read the original blog here. In addition to blogging furiously, we also run courses for businesses in both Excel and VBA.

Posted by Andrew Gould on 30 May 2012 | 1 comment

Using VBA to Label Multiple Series in a Chart

A while ago I wrote a blog describing how you could use VBA in Excel to label the data points in a scatter chart. Recently someone asked a question about how to adapt the code I created to label more than one series in the same chart. Here's the problem we're trying to solve:

The chart contains one data series plotting RunTime against Budget and another plotting RunTime against Box Office. We want to label each data point with the name of the film.

The Code to Solve the Problem

The key to this solution is looping over each data series for every data point that we want to label. The code below will work for any number of data series that the chart contains:

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

FilmCounter = 1

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

'loop over each cell in the list of source data

For Each SingleCell In FilmList

'loop over each series in the chart

For Each FilmDataSeries In FilmChart.SeriesCollection

'change the label text to be the film's name

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

Next FilmDataSeries

FilmCounter = FilmCounter + 1

Next SingleCell

End Sub

The End Result

The end result of running this code is that each data point in the chart will be labelled:

Each data point in the chart now shows the name of the film that it represents.

You can download the completed example for this blog.

Comments on this blog

This blog has one comment:

Comment added by alexbell12 on 30 October 2012 at 17:29 GMT

This is extremely useful! I am trying to alter the macro to fit with my spreadsheet. It has 5 series with different numbers of data points in them and different labels, but the labels are all in the correct order in the 'list'. Currently, each time the macro starts labelling a new series, it goes back to the start of the 'list' and repeats the labels for each series. How could the macro be altered just to run continuously through the whole 'list' without going back to the start for each new series?

Reply from Andrew Gould (blog author)

Hi Alex,

Thanks for the comment. I've posted the answer shown below in the other thread on this subject along with an explanation but thought I should repeat it here too. Sorry for the delay in replying!

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

USING EXCEL VBA TO LABEL MULTIPLE SERIES IN A CHART

Using VBA to Label Multiple Series in a Chart

The Code to Solve the Problem

The End Result

BLOGS BY TOPIC

BY AUTHOR

BY YEAR