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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.