Label the Last Point in a Series.

This simple VBA procedure labels each series in a chart with the name of the series. In some cases this may be more useful than using the legend. This procedure can easily be modified, for example, to label the maximum or minimum point in a series, using any label that is needed.

Highlights of the Code

The procedure loops through all the series in the active chart:

For Each [Series] In ActiveChart.SeriesCollection
  ...
Next

It determines the number of points in each series:

nPts = [Series].Points.Count

It applies a data label to the last point in the series:

[Series].Points(nPts).ApplyDataLabels _
    Type:=xlDataLabelsShowValue, _
    AutoText:=True, _
    LegendKey:=False

And it changes the data label to the name (legend entry) of the series:

[Series].Points(nPts).DataLabel.Text = [Series].Name

Implementation

The code below represents a regular code module in an Excel VBA project. LastPointLabel is the procedure which applies a label to the last point in each series, and changes the text of the label to the name of the series.

Copy the code below, and paste it into a new module. Make sure the Option Explicit line appears only once. LabelLastPoint.zip is a zip file containing this procedure and a floating command bar with a Label Last Point button to make it work.

    Option Explicit

    Sub LastPointLabel()
      Dim mySrs As Series
      Dim nPts As Long
      For Each mySrs In ActiveChart.SeriesCollection
      With mySrs
        nPts = .Points.Count
        mySrs.Points(nPts).ApplyDataLabels _
            Type:=xlDataLabelsShowValue, _
            AutoText:=True, LegendKey:=False
        mySrs.Points(nPts).DataLabel.Text = mySrs.Name
      End With
      Next
    End Sub

A chart must be selected before this code is run, otherwise an error will occur on this line:

  For Each mySrs In ActiveChart.SeriesCollection

A small amount of error prevention can be included which checks for an active chart, and prompts the user to select one if necessary.

    Option Explicit

    Sub LastPointLabel()
      Dim mySrs As Series
      Dim nPts As Long
      If ActiveChart Is Nothing Then
        MsgBox "Please select a chart and try again.", vbExclamation
      Else
        For Each mySrs In ActiveChart.SeriesCollection
          With mySrs
            nPts = .Points.Count
            mySrs.Points(nPts).ApplyDataLabels _
                Type:=xlDataLabelsShowValue, _
                AutoText:=True, LegendKey:=False
            mySrs.Points(nPts).DataLabel.Text = mySrs.Name
          End With
        Next
      End If
    End Sub