Solved

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

Posted on 2011-02-22
5
1,090 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:PTRUSCOTT
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:azeempatel
ID: 34958545
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34958722
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
0
 
LVL 1

Author Comment

by:PTRUSCOTT
ID: 34978656
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
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 34978868
Hello,

an XY chart is not the same as a line chart, although it can look the same. A line chart uses numbers for the Y axis and categories (which can be text) for the X axis. An XY chart uses numbers for both axes, and the chart source definition box looks a bit different.

To create an XY chart, you need two columns of numeric data (dates are numeric, so they are OK). In the file I attached above, right-click the blue line and select "Select data", then edit the series "Ticket sales" and look at the data source. You will see a box for the series name, another box with the range for the X axis values and a box with a range for the Y axis values.

You can add a series to a chart from the "Select Data Source" dialog box. Click the Add button and fill in the boxes in the "Edit Series" dialog.  Another way to add a series (a nice shortcut) is to select the Y values for the new series (including a column title), copy them, then select an existing XY chart and use Paste Special.

This will open a Paste Special dialog box, where you can accept the default options and just click OK. The new data series will be added to the chart. Then you select the data series (if you cannot see it on the chart, use the Layout tab on the Chart Tools, select the new series in the drop down box in the Current Selection group to the left) and open the Format Series dialog.

Here you can specify the axis on which to plot the series, i.e. primary or secondary. In this case, the Events series needs to be plotted on the secondary axis. You will then see the secondary Y axis on the right hand side of the chart.

After that, it's really just styling: adding Error Bars, removing the line for the Even series, formatting the markers for the Event series, and adding the text labels with the XY Chart Labeler tool.

Step by step, based on the data of the previously attached file:

- select A1 to B100 (column A has the dates, column B has the ticket sales numbers)
- click Insert > Scatter > Scatter with straight lines
- select C1 to C100 and copy the selection
- select the chart and use Paste Special (Alt-E-S)
- accept the defaults and hit OK
- in the legend you will see a new series, but the data values are so small that they don't seem to appear on the chart
- click the Layout tab on the Chart Tools
- towards the left of the ribbon, use the drop-down to select the series "event"
- click Format Selection below the drop-down
- click "Secondary Axis" -- do not close the dialog
- click Marker Options in the side panel of the dialog and select "Built-in" -- do not close the dialog
- click Marker Fill in the side panel of the dialog and select "Solid Fill" and a color
- close the dialog
- now you should see the markers for the four events plotted in the chart
- with this new series selected, click Error Bars on the Layout tab in the Chart Tools and select "More Error Bar options"
- tick "Minus" in the display group and "Percentage" in the Error Amount group. Enter 100 as the percentage value and close the dialog box.
- in the chart elements selection drop-down on the left of the Layout tab, select 'Series "event" X error bars' and hit the delete key (to delete horizontal error bars, which Excel, annoyingly, puts in by default)
- now format the error bar lines to your liking, move the legend (or delete it)
- format the secondary Y axis max value so that the event markers sit at your preferred position, then hide the secondary Y axis by setting it to no major or minor tick marks and no labels.
- finally, use the XY Chart Labeler to put in the labels for the event markers. The tool is fairly self-explanatory and comes with good documentation. (It is possible to put the data labels in manually, but with the Chart Labeler it's sooooo much easier)

If this has helped, you may want to re-consider the points allocation for the question.

cheers, teylyn




0
 
LVL 1

Author Comment

by:PTRUSCOTT
ID: 34985263
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now