Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-02-22
Medium Priority
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,
Question by:PTRUSCOTT
  • 2
  • 2

Expert Comment

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.
LVL 50
ID: 34958722

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

Author Comment

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,

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 34978868

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


Author Comment

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.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 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