Macro 82: Label First and Last Chart Points
One of the best practices for 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 — typically, the first and last data points.
That being said, it is a bit arduous to continuously adjust labels every time data is added or when a new
chart is needed. The macro outlined in this section automates the adding of labels to the first and last
data points.
How it works
All charts have a SeriesCollection object that holds the various data series. This macro loops
through all the series, bringing each one into focus one at a time. With the series in focus, we can use
any of its many properties to manipulate it. Here, we are activating the data labels for the first and
last data point in the series.
Sub Macro82()
‘Step 1: Declare your variables
Dim oChart As Chart
Dim MySeries As Series
‘Step 2: Point to the active chart
On Error Resume Next
Set oChart = ActiveChart
‘Step 3: Exit no chart has been selected
If oChart Is Nothing Then
MsgBox “You select a chart first.”
Exit Sub
End If
‘Step 4: Loop through the chart series
For Each MySeries In oChart.SeriesCollection
‘Step 5: Clear ExistingData Labels
MySeries.ApplyDataLabels (xlDataLabelsShowNone)
‘Step 6: Add labels to the first and last data point
MySeries.Points(1).ApplyDataLabels
MySeries.Points(MySeries.Points.Count).ApplyDataLabels
MySeries.DataLabels.Font.Bold = True
‘Step 7: Move to the next series
Next MySeries
End Sub
1. Step 1 declares two variables. We use oChart as the memory container for our chart. We
use MySeries as a memory container for each series in our chart.
2. This macro is designed so that we infer the target chart based on the chart selection. That is
to say, a chart must be selected for this macro to run. The assumption is that we want to per-
form the macro action on the chart we clicked on.
Step 2 sets the oChart variable to the ActiveChart. If a chart is not selected, an error is
thrown. This is why we use the On Error Resume Next statement. This tells Excel to
continue with the macro if there is an error.
3. Step 3 checks to see if the oChart variable is filled with a chart object. If the oChart vari-
able is set to Nothing, no chart was selected before running the macro. If this is the case,
we tell the user in a message box, and then we exit the procedure.
4. Step 4 uses the For…Each statement to start looping through the series in the active charts
SeriesCollection.
5. If data labels already exist, we need to clear them out. We can do this by using
xlDataLabelsShowNone.
6. Each data series has a Points collection, which holds all the data points for the chart. Like
most collections in the Excel object model, data points have index numbers.
Step 6 of the macro uses index numbers to get to the first and last data points. The first data
point is easy; we capture it by using MySeries.Points(1). After we have it in focus, we
can use the ApplyDataLabels method to turn on data labels for that one point.
The last data label is a bit trickier. We use MySeries.Points.Count to get the maximum
number of data points in the series. That is the index number of the last data point. We place
the last data point in focus, and then we apply labels to it.
Finally, we adjust the formatting on the data labels so they have bold font.
7. Step 7 loops back around to get the next series. After we have gone through all the data
series in the chart, the macro ends.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it will be named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list box. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
