Annotating line graphs using arrows



I am creating a line chart in Excel, and want to annotate various
movements in the line. It is a share price graph, with price against
date (and volumes on a secondary axis), and I want to explain the
significant peaks, troughs, movements etc.

Ultimately I want arrows to point to the line, with the arrows
numbered - I will have a separate table which shows the date and
narrative for each number (space is limited on the chart itself, as
I'll have about 10-15 arrows).

Rather than drawing the arrows on manually, and adding text boxes
numbers, is there any quicker way of doing it? The main problem
with making changes (eg updating the chart) as arrows will need to be
moved (as there will be more data), new arrows added (if something
interesting has happened to the price) and the numbering may need to
be changed (older arrows may need to be removed, some may be no
considered significant etc).

I have written some VBA to do this, but it doesn't work particularly
well and is difficult for some of my colleagues to use as they don't
know what to do when something goes wrong. The only way I could
of doing it was working out what X- and Y- co-ordinates were the
origin and maximum points on the two axes, calculating what
of these distances were equivalent to e.g. 3 Sep 07 on a scale of 1
Jan 06 to 31 Dec 08, and doing similar for the price itself. Once I
have the point on the chart I can draw an arrow, with an input
gradient and direction, and add a text box with the appropriate
on it (all done in VBA).

This all seems quite clumsy to me, and doesn't always do what I want
it to. Is there a more effective solution to this? - it takes a long
time to update manually, and I'm having to do quite a lot of these



I’d bet the most comfortable method how to mark the chart events is labeling.

The gist of the following procedure is

- location of label texts in a spreadsheet column and the rows corresponding
with point values (the column next to that with values is being offered

- omitting all empty cells in the label column.

You can

- renew labeling on every change,

- shunt the labels in the chart arbitrarily to not interfere with curves and

This variant is comfortable further in copying colors and sub- and
superscript fonts of the original text. The alignments and framing of the
labels, and font size, have to be altered inside the procedure. The label
texts should be as short and apt as possible.

Sub AddLabels()
'Petr Bezucha, 2008
Dim RngLabels As Range, Labels As DataLabels, Ser As Series, I As Long, _
J As Long, StrSer As String
Const LabelFontSize As Long = 9
If TypeName(Selection) = "Series" Then
Set Ser = Selection
StrSer = Ser.Formula
StrSer = Left(StrSer, InStrRev(StrSer, ",") - 1)
StrSer = Right(StrSer, Len(StrSer) - InStrRev(StrSer, ","))
Set RngLabels = Range(StrSer).Offset(, 1)
On Error GoTo ErrExit
Set RngLabels = Application.InputBox(Prompt:="Select labels array", _
Default:="=" & ActiveSheet.Name & "!" & RngLabels.Address, Type:=8)
Ser.HasDataLabels = True
Set Labels = Ser.DataLabels
With Labels.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlLineStyleNone 'alternatively xlContinuous
End With
With Labels.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Labels
.Shadow = False
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Position = xlLabelPositionAbove
.Orientation = xlUpward
.AutoScaleFont = False
.Font.Size = LabelFontSize
End With
For I = 1 To Ser.Points.Count
If Not IsEmpty(RngLabels(I)) Then
Ser.Points(I).DataLabel.Text = RngLabels(I).Text
Ser.Points(I).DataLabel.Characters.Font.ColorIndex = _
For J = 1 To Len(RngLabels(I))
If RngLabels(I).Characters(J, 1).Font.Subscript Then _
Ser.Points(I).DataLabel.Characters(J, 1).Font.Subscript = True
If RngLabels(I).Characters(J, 1).Font.Superscript Then _
Ser.Points(I).DataLabel.Characters(J, 1).Font.Superscript = True
Next J
Ser.Points(I).DataLabel.Text = ""
End If
Next I
MsgBox "Select a series in the chart"
End If
Exit Sub
On Error GoTo 0
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question