Excel: time series with markers for events

Question

I have daily data for two stocks for about 10 years, and sometimes there are some specific events (such as an announcements or a political events, not numerical data) I would like to show on my graph.

When I'm plotting the 2 time series I would like to add a cross or a line from the x-axis on the graph when the specified eventS happen.

My data on the Excel sheet are presented as follow: 1st column is the dates, 2nd colum is the value of stock 1, column 3 is the value of stock 2, and last column is the name of the event when there is one otherwise it's written #N/A.

I've tried a few tutorials online but they don't work.

Thanks for any help you can provide.

:)

Answer 1

To achieve an effect like this:

1) Convert your existing data into a Data Table Insert>Table.
2) Add a helper column, called "Event_Helper", with the following formula =IF([@event]="",na(),0). This returns a 0 value whenever you have a named event in your Event column, otherwise it returns #N/A which is ignored by Excel when charting a series.
3) Create an XY/Scatter Chart (not a line chart), using

• X Values = Date column
• Y Values = Stock A, Stock B, and Event_Helper columns

4) Add data labels to your event data series and format to taste.

If you'd like to add vertical lines like this:

5) Add an additional helper column "Error_Helper" to your table with the following formula:=IF([@Event]="",NA(),MIN(Table1[@[StockA]:[StockB]])). This returns the minimum and lowest charted stock value when there's a named event, otherwise it returns #N/A.
6) Add error bars to your Event_Helper data series.
7) Delete the horizontal error bars.
8) Format the vertical error bars with a Custom Error Amount, using:

• Positive Error Value = Error_Helper
• Negative Error Value = 0

9) Format the error bars to taste.