Adding data label only to the last value
In my other answer I did not make any allowance for a moving last point (i.e., added data points). It's not a big deal to adjust your data to allow for a single moving label.
I'm only showing one data series, but it's easy enough to do as many as needed, once you know the trick. In the data set below, I have a Date column, and two columns with the same header, Value. The first value column has values down to the middle of the Date range. The second column has a formula that only displays the last value. This is the formula in cell C2:
=IF(AND(ISNUMBER(B2),LEN(B3)=0),B2,NA())
This formula is copied down the column.
Now make a chart of all of the data. The first value series is the blue circles and lines, the second is just the orange circle on the last blue point. I've added a label to the orange series, not the blue one.
The second chart just shows it cleaned up: I've formatted the second Value series so it uses no markers and no lines, and I've deleted the legend.
In the next chart, all I've done is add a couple data points. Without any further effort on my part, the label has moved to the new last point.
If you select the whole series, Excel will put a label on each point in the series.
Select just the single point you want a label on: click once to select the series, then click again to select one point. Now when you use the right-click menu or the plus sign icon to add data labels, it will add a label only on the one point you've selected.
For a VBA approach which only adds a label to the last point and doesn't leave a bunch of "" labels in the chart, here is a simple procedure from my tutorial Label Last Point for Excel 2007. It specifically labels the last point of each series in the active chart with the series name.
Sub LastPointLabel()
Dim mySrs As Series
Dim iPts As Long
Dim vYVals As Variant
Dim vXVals As Variant
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
Application.ScreenUpdating = False
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
vYVals = .Values
vXVals = .XValues
' clear existing labels
.HasDataLabels = False
For iPts = .Points.Count To 1 Step -1
If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False, _
AutoText:=True, LegendKey:=False
Exit For
End If
Next
End With
Next
' legend is now unnecessary
ActiveChart.HasLegend = False
Application.ScreenUpdating = True
End If
End Sub
I've written a bunch of articles about this:
Label Last Point
Label Each Series in a Chart
Label Last Point for Excel 2007
Label Last Point – Updated Add-In
In addition, my commercial Excel charting software includes an updated version of this feature.