Adding data label only to the last value

Question

So I have a line chart with multiple lines. Like that.

I would like to add data label to each line, but only to the last value on the right.

I know if can delete individual labels. But when I add new Value to my table, the new value will appear with a new label, which is good, but the previous I would have to delete manually. Is there a way to make the label move to the right automatically?

Answer 1

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.

Answer 2

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.

Answer 3

I would like to suggest you both options. First is Non-VBA and other is VBA Code.

Non-VBA Solution:

Since Excel don't have any arrangement to assign only One Data Label, if series of Cell are involved to create the Chart.

But after Chart has been created you can Reset the Labels.

In your case, after Label is applied, Right Click the Line, you find Labels are ready to Edit. Select Labels one by one, then either Right Click & Delete or un-check the Value Checkbox next to the Chart Area.

VBA Solution:

Create one Command button and enter this code.

Remember, you simply create the Chart but don't apply the Data Labels. After every thing is ready then Click the Command button. Code will first create all Data Labels finally keep the LAST ONE.

Note you may rum this code as RUN MACRO even without the Command Button.

Sub LastDataLabel()

Dim ws As Worksheet
Dim chrt as Chart
Dim srs as Series
Dim pnt as Point
Dim p as Integer

Set ws = ActiveSheet
Set chrt = ws.ChartObjects("Line Chart")
Set srs = chrt.SeriesCollection(1)

    srs.ApplyDataLabels

    For p = 1 to srs.Points.Count - 1 
        Set pnt = srs.Points(p)

        p.Datalabel.Text = ""
    Next

    srs.Points(srs.Points.Count).DataLabel.Format.TextFrame2.TextRange.Font.Size = 10


End Sub

Hope this help you.

Answer 4

you can select the last point only and add data label only to that point.