[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-02-22
5
Medium Priority
?
1,130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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