Comment from Jon Peltier
Time: Thursday, October 16, 2008, 4:04 pm

Do you mean you can’t see the data because it’s too close to zero? What kind of chart is it? are the curves plotting the same kind of information?

To get around this problem you could use a logarithmic scale, put a break in the axis, or plot the curves in two different panels of a panel chart.

Comment from Jon Peltier
Time: Tuesday, October 21, 2008, 3:09 pm

Jaanus – Good idea. I missed it because I probably allready deleted the legend, but I’ve added it to the code above.

Comment from Jon Peltier
Time: Wednesday, October 22, 2008, 7:45 am

Petr -

IMO, leader lines do not add to a chart, they seem to add clutter. If you need leader lines to clarify which label corresponds to which series, you may be dealing with excessive clutter. When I am almost cluttered enough to use leader lines, I try to rely instead on coloring the label text to match the series format.

That said, you could in fact use VBA to simplify the task of adding leader lines. I would extend the series by one point, remove the last marker and change the last line segment to a leader line kind of format (thin line, different format from the series lines) and center the label on the added point. You could do this manually, and see if you like it, if so, automate it.

Comment from Jon Peltier
Time: Friday, October 31, 2008, 8:07 pm

Stružák –

Good point. I usually remember. In this case it probably wouldn’t make too much difference, but it’s a good habit to get into.

Comment from Jon Peltier
Time: Thursday, January 8, 2009, 11:56 am

LEM -

You need to change one line of code:

    ' add label
    mySrs.Points(iPts).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowValue:=True, _
        Separator:="" & Chr(10) & ""

Chr(10) puts the value onto a new line, that is, separates it with a line feed (ASCII character 10). A little testing shows you can use any string you want, even multiple characters as the separator. I tried these and all did as expected:

Separator:=", "
Separator:=" - "
Separator:=" ### "

Comment from Jon Peltier
Time: Tuesday, June 16, 2009, 4:04 pm

Barrett -

I turned on the macro recorder while making some minor adjustments and came up with this modified procedure (note the red text). It doesn’t do the number format, since it assumes the series name is a string, but if you format the series names the way you want, it should work.

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
            ' 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
            ' handle error if point isn't plotted
            On Error Resume Next
            ' add label
            mySrs.Points(iPts).ApplyDataLabels _
                ShowSeriesName:=True, _
                ShowCategoryName:=False, ShowValue:=False, _
                AutoText:=True, LegendKey:=False
            With mySrs.Points(iPts).DataLabel
              .Position = xlLabelPositionCenter
              .Orientation = xlUpward
            End With
            bLabeled = (Err.Number = 0)
            On Error GoTo 0
          End If
        Next
      End With
    Next
    ActiveChart.HasLegend = False
  End If
End Sub
 

Comment from Jon Peltier
Time: Thursday, February 11, 2010, 6:59 am

Jeff -

That’s the way data labels work. A label is always at the ith point, so if you remove j points at the beginning of the series, the label sticks to what is now the ith point, but which was the (i+j)th point.

If you need the label to stick to a given XY value in a dynamic setting, you should use a hidden series with points to anchor labels (one custom XY point per label). To keep the last point labeled in a dynamic setting, use a dynamic pair of XY values for that point’s label. No need for VBA.