Annotating charts in Excel

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 which are sequentially
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 with
numbers, is there any quicker way of doing it?  The main problem comes
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 longer
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 think
of doing it was working out what X- and Y- co-ordinates were the
origin and maximum points on the two axes, calculating what proportion
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 number
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
charts.

Thanks
Robert
0
rewb2 (15)
9/19/2008 9:03:14 AM
excel 39879 articles. 2 followers. Follow

1 Replies
912 Views

Similar Articles

[PageSpeed] 8

<rewb2@yahoo.co.uk> schrieb im Newsbeitrag 
news:b24c4a2b-7a35-4d8f-9ce0-12bbeb01e7b6@j22g2000hsf.googlegroups.com...
>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 which are sequentially
> 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 with
> numbers, is there any quicker way of doing it?  The main problem comes
> 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 longer
> 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 think
> of doing it was working out what X- and Y- co-ordinates were the
> origin and maximum points on the two axes, calculating what proportion
> 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 number
> 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
> charts.
>
> Thanks
> Robert

Robert,

The only way I can think of is to use the fact that when the mouse is moved 
over a point on an Excel chart, the point number is shown. You can use this 
as a reference to the explanation of the particular points.

There will be no link to the explanation (you can not click on the point to 
get to it) and the points with additional comments will not be highlighted, 
but the problem of changing data values will be solved.

Thomas 


0
9/21/2008 1:28:12 PM
Reply:

Similar Artilces: