Label the Last Point in an Excel Chart's Series.Updated Tutorial on Peltier Tech Blog: Label Last Point for Excel 2007The 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 CodeThe 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 ImplementationThe 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.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |