Link to home
Start Free TrialLog in
Avatar of PTRUSCOTT
PTRUSCOTT

asked on

Microsoft Excel 2007: How can I create a time line graph with marker Flags

Dear Experts,
I would like to be able to do the following type of chart using Excel 2007.

Suppose I have two columns as follows.  Column A has the number of tickets my sold on a given day.  Column B has the date in as a date format variable.  

I know how to convert this to a line graph showing ticket sales over time.  Let's suppose the lines are more or less horizontal - going up or down as the ticket sales get closer to the event itself.  What I want to do is put in some vertical lines showing. events that happened on particular dates.  For example if on January 15th we did an email campaign on a particular subject I would like a white rectangle at the top of the vertical line marker explaining what happened on that date.  You frequently see the same type of display in newspapers.

Can anyone explain how to do this in Excel 2007?
Best Wishes,
PT
Avatar of Azeem Patel
Azeem Patel
Flag of Canada image

Create the Graph the way you create. After that

In excel 2007
Go in insert > Shapes > Lines - Create the line from the peak value till the outside of graph then
Go in insert > Shapes > Round Rectangle- Create the Box at the tip of the line outside the graph and add your comments.

In excel lower than 2007 you will find insert at
Tools > insert and follow the same procedure.
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Hello,

This can be automated, so you do not need to add/remove lines manually when your data changes.

Create an XY chart with the date and the ticket sales data and set it to line without markers.

Add two more columns to your data table for the events. In the first column, "event", just enter a 1 value for each event. In the second column, "event label", enter the event title as text.

Now select the "event" column and add it to the chart. Select the series for "event" and format it to be on the secondary axis and set it to have no line, only markers. Add vertical error bars to that series, set to 100%, and set the markers and the error bar lines to a color of your choice.

Then, as a last step, download and install the XY Chart Labeler tool, which is free and can be downloaded from here: http://www.appspro.com/Utilities/ChartLabeler.htm

Use this tool to add the labels to the event series from the "event label" column. (you can set the labels manually, but this tool is so genius, it should be a standard Excel feature)

You can hide the secondary axis. (it's still visible in the attached file)

See attached chart. You can add any event by entering a 1 in column C. Enter a text in column D in the same row and you will see the chart upate.

You can use this as a template for any event that you are running. No need to manually draw lines with the drawing tools.

cheers, teylyn
event-chart.xlsx
Avatar of PTRUSCOTT
PTRUSCOTT

ASKER

Dear Teylin,

Your solution looks really super but I'm afraid I'm having some trouble following your steps.  When you say an XY Chart is that the same a simple line graph?

I have done that and I get up to the point where you say:

"select the 'event' column and add it to the chart.  I'm not quite sure how to add it to the chart.  I can right-click the chart I have created and I get a "select data" option.  However I cannot work out how to add the Event column to my chart.  

If I click the "Add" button in the "select data" dialog box I can add the range for the event column but the resulting chart does not look anything like your wonderful chart.  

Could you expand on the process of adding the column to the existing chart please.

Best Wishes,

PT
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dear Teylyn,

Yes I was able to work through this second explanation.  Wow you worked hard on this.  I'm glad that the put a maximum of 500 on this otherwise I would have been tempted to give you more.

I was somewhat amused that you said "After that, it's just styling".  This set of steps would have produced terrified gasps from most computer science classes that I have taught!

You are a brilliant MS office jedai.  Many thanks.
Phil