Mark a real-world event on a Chart at a designated point

[Richard Schollar]

Given a data table like the following:

Book1
	A	B	C	D
1	Date	Sales	CostOfSales
2	01/01/2007	95	65
3	01/02/2007	100	68
4	01/03/2007	75	50
5	01/04/2007	65	45
6	01/05/2007	80	61
7	01/06/2007	100	71
8	01/07/2007	150	103
9	01/08/2007	135	97
10	01/09/2007	125	94
11	01/10/2007	90	63
12	01/11/2007	60	45
13	01/12/2007	62	41
Sheet1

I can produce a nice simple line chart that plots both the Sales lines and the CostOfSales lines (values on the Y axis, timeline on the X axis)- that's no problem.

But supposing I have an event (eg a business acquisition) that I want to mark out on my chart (eg because it materially affects the sales/cost of sales numbers), and given that I have the date of this event and a label stating what this event is in two cells, is there some way I can plot this event on my graph? Specifically, I'd like to see eg a vertical line thru the date axis (X axis) at the point the event occured with a descriptive text label next to it (eg at the top, so it isn't getting in the way of the chart).

I need to automate this as much as possible as I have c. 500 charts to produce, each with a relevant event.

Can anyone help?

Thanks in advance

 

[Peter_SSs]

Richard

I am not sure that you are allowed to actually ask a question.

However, just in case you are, and given all the help you have given everybody else, yours should not remain an "Unanswered Post".

I don't have an answer for you and I guess that you have searched (including Mr Peltier) already, but this is the closest thing to what you might be asking that I could find.
http://peltiertech.com/Excel/Charts/AddLineVertSeries.html

 

[Richard Schollar]

Peter

Thank you very much - that looks pretty much exactly what I want to do. I had hoped there would be a completely straightforward way of doing this, but if not I can work around it with what you've provided.

Once again, thank you very much!

Richard

 

[Peter_SSs]

I had hoped there would be a completely straightforward way of doing this ...

I have not done very much with charts, but what I have done would lead me to believe that not very much to do with charts could be called straight forward!

 

[Gerald Higgins]

Hi Richard

Peter's solution may look complicated, but I really do think it's very straightforward. I have often done this kind of thing - apologies but I don't know how to insert pictures of my examples.

You can set up another chart data series which refers to column D, and use this series to drive your event markers. Although I imagine you only want one event marker per chart, there's nothing to stop you having more than one. For example, if you've got a series of months lasting several years, adding an event marker to highlight the end of each year can be good.

You then EITHER use the 2nd Y axis option, OR use the same Y axis.
If you are using the 2nd Y axis, enter "1" on the relevant date, and set the maximum scaling of the 2nd Y axis manually to 1.
Make sure all the other dates are set to zero or blank.
If you are using the same Y axis, just enter some number that is very large in the context of your data.

You can do all of this manually, or you could create formulas that will automatically position the marker on the correct date, and make the value equal to the maximum of the date in column B or column C (or some multiple of the max, eg 80%, 120%, etc.)

Assuming your main data is in the form of a line chart, as you say, I would then make the event marker a bar - click on the data series once it's on the chart, and change the chart type for just that one series to "Column". You can then tinker with the series formatting to make the column wider, narrower, semi transparent, or whatever you like.

Personally, I like making the bar fill the full height of the chart, so that it doesn't appear to be a data bar, and looks more like an abstract colour band across the chart (if that makes sense!)

 

[Richard Schollar]

Hi Gerald

Yes that makes sense - I'm definitely going to give this a go when I get home (it's for a private project).
When you create these vertical lines, can you give a descriptive label to it that is obvious on the chart (but preferably not interfering with the line graphs that the line intersects)? If I can do this, I'll have everything I need.

Thanks for your thoughtful post - you and Peter have really helped me out of a hole.

 

[Gerald Higgins]

Hi Richard

As I was writing my first post, I thought it was easy to add a label to the event marker, but on researching it a bit more, I can't find an easy way to add a text label.

You could let the legend describe the event marker, by naming the event marker series as "Year End" or whatever, but this might not be what you want. Off course you could manually draw a text box too, but that might be difficult if you've got 500 charts to do, with the event in different places in each one. I'd be interested if there is a neater solution to the label problem.

 

[Jon Peltier]

There are so many ways to add labels to data points.

If you use the error bar technique for adding the line, you can name the series after the name of the event, and add data labels to the series using the series name. Or you can use the two-point method, and the same data labels, and delete the one you don't need. Or select just the one point and add the data label. You can add any arbitrary data label and edit its text. Or you can select the data label, type = in the formula bar, and select a cell, and the data label will display any text that appears in the cell. Or you can use one of these free utilities:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

 

[Richard Schollar]

Just wanted to say a big thanks to everyone who posted to this thread - I managed to implement exactly what I needed. I never would have been able to without this forum. Thanks again!