Label the Last Point in an Excel Chart's Series.
Updated Tutorial on Peltier Tech Blog: Label Last Point for Excel 2007
The information below was written for a very old version of Microsoft Excel. You may still find it useful, but a totally updated tutorial has been published on the Peltier Tech Blog.
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
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.
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.
Finally, if a point is not plotted because the corresponding cell is blank or contains the #N/A error, the ApplyDataLabels command will cause an error. The following labels the last point that can be labeled, and reemoves labels from all other points.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.