In 9 Steps to Simpler Chart Formatting I suggested using data labels to identify each series rather than using a legend. I have a small VBA procedure that I use for this. It labels the last point of each series, and removes other labels. It also has an error trap that skips points that are not plotted because of blank cells or #N/A errors.
Sub LabelLastPoint()
Dim mySrs As Series
Dim iPts As Long
Dim bLabeled As Boolean
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
For Each mySrs In ActiveChart.SeriesCollection
bLabeled = False
With mySrs
For iPts = .Points.count To 1 Step -1
If bLabeled Then ' series has already received valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label if it's not the last point
mySrs.Points(iPts).HasDataLabel = False
On Error GoTo 0
Else ' series does not yet have valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label (linked labels otherwise resist reassignment)
mySrs.Points(iPts).HasDataLabel = False
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False, _
AutoText:=True, LegendKey:=False
bLabeled = (Err.Number = 0)
' 2010 no error if point doesn't exist: label applied, but it's blank
If bLabeled Then bLabeled = (Len(mySrs.Points(iPts).DataLabel.Text) > 0)
If Not bLabeled Then
' remove blank label
mySrs.Points(iPts).HasDataLabel = False
End If
On Error GoTo 0
End If
Next
End With
Next
' remove legend
ActiveChart.HasLegend = False
End If
End Sub
To implement this procedure, follow the steps in How To: Use Someone Else’s Macro.
