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.
- Function FirstAndLastLables()
- Dim oChart As ChartObject
- Dim MySeries As Series
- For Each oChart In ActiveSheet.ChartObjects
- For Each MySeries In oChart.Chart.SeriesCollection
- 'Clear ExistingData Labels
- MySeries.ApplyDataLabels (xlDataLabelsShowNone)
- 'Apply Labels to First and Last Point
- MySeries.DataLabels.Font.Bold = True
- 'Make sure label ends up in an appropriate position
- MySeries.DataLabels.Position = xlLabelPositionBestFit
- Next MySeries
- Next oChart
- End Function