Automatically Label First and Last Chart Points

August 16th, 2011 by datapig Leave a reply »

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
  4.  
  5. For Each oChart In ActiveSheet.ChartObjects
  6.     For Each MySeries In oChart.Chart.SeriesCollection
  7.    
  8.         'Clear ExistingData Labels
  9.            MySeries.ApplyDataLabels (xlDataLabelsShowNone)
  10.            
  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
  15.        
  16.         'Make sure label ends up in an appropriate position
  17.        MySeries.DataLabels.Position = xlLabelPositionBestFit
  18.    
  19.     Next MySeries
  20. Next oChart
  21.  
  22. End Function
Advertisement

6 comments

  1. Eric says:

    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. m-b says:

    This will come in handy. Thanks!

  3. Jeff Weir says:

    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.

  4. datapig says:

    Jeff: Great idea to color the data labels to match the series! I’m using that idea from now on.

  5. Jon Peltier says:

    xlLabelPositionBestFit is only applicable to pie charts, by the way.

  6. Handy tip. The first and last labels show help show the range of the data without making the chart feel cluttered. Wish this was a standard feature in Excel.

Leave a Reply

Powered by sweet Captcha