Automatically Label First and Last Chart Points

One of the best practices of dashboard building is to avoid overwhelming your customers with too much data at one time – especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data. One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points.


For example, I can make this chart easier to digest by removing all but the first and last data labels.


Once I have only two data labels, my eyes immediately take in the message of this chart (that we went from 11 to 53).


All that being said, it is a bit of a pain to label just the first and last points in a chart.

So I wrote a small macro to do just that!


This macro will alter each chart in the active sheet so that only the first and last data points have labels. To use this macro, simply copy and paste the code you see here into a standard code module.

  1. Function FirstAndLastLables()
  2. Dim oChart As ChartObject
  3. Dim MySeries As Series
  5. For Each oChart In ActiveSheet.ChartObjects
  6.     For Each MySeries In oChart.Chart.SeriesCollection
  8.         'Clear ExistingData Labels
  9.            MySeries.ApplyDataLabels (xlDataLabelsShowNone)
  11.         'Apply Labels to First and Last Point
  12.            MySeries.Points(1).ApplyDataLabels
  13.             MySeries.Points(MySeries.Points.Count).ApplyDataLabels
  14.             MySeries.DataLabels.Font.Bold = True
  16.         'Make sure label ends up in an appropriate position
  17.        MySeries.DataLabels.Position = xlLabelPositionBestFit
  19.     Next MySeries
  20. Next oChart
  22. End Function

7 thoughts on “Automatically Label First and Last Chart Points

  1. Eric

    Another idea is to take this macro and generalize it so that there is an input of which point you wanted to be labeled (i.e., Function LabelPoint(lPointID as long). Then you could have other code so that you could identify local minima or maxima, or changes in direction etc. First and last would be two specific runs of the function: LabelPoint(1) and LabelPoint(MySeries.Points.Count). You would need some error checking on lPointID…

  2. Jeff Weir

    Adding mySrs.DataLabels.Font.Color = mySrs.Border.Color will ensuresthe text color of the data labels always matches the line color of the appropriate series. Very handy when you have multiple series.

  3. Ratnakar Venkata


    Could you please suggest the above macro will work in PPT as well? I tried but it couldn’t

    I need to try this in PPT as well. Please help me.


Leave a Reply

Your email address will not be published. Required fields are marked *