1/8/2011

Trend Chart with Events

Introduction

Trend charts show values for a data series over time. There are usually outside factors that affect our trend data series. The addition of these events can significantly enhance the analytical/ educational value of our trend charts.

The two charts below show crude oil cost per barrel  trends ($2004)  from 1861 to 2005. The chart on the left shows the trend chart by itself, the chart on the right shows the same trend data as well as world and oil industry events. The events in the right chart show the affects that new oil discoveries and wars have had on crude oil prices.

Data for this example is based on BP Global - Statistical Review of World Energy - 2005.

Adding Event Data to Excel XY  Charts  in  7 steps:

1. Create - Format Base Trend Chart: With the Chart Wizard, we can create our base trend chart, like the left chart above. This gives us our raw chart ready for adding tour event data.

2. Organize Event Data. The table on the right shows how we can organize our event data so that we can add it to our chart. We have a total of 15 events in Col C that we want to add, starting in 1863 and ending in 2003.  The years for our events are shown in Col A and the Y position for our events are shown in Col B.

The Y positions are staggered to prevent overwriting one Event description with another. 

We will use a vertical error bar to provide our leader line from the event description to the trend line value. The error bar will start at the Y position for our event, the length of the error bar is the Y Position - trend line value year (Col B - Col D).

 

 

 

3. Add Event Data Series to Trend Chart - We add our event series by selecting $A$2:$B$16 and dragging the selection to the chart. We select the Add cells as New Series option and check Categories (x Values) in First column.

4. Format Event Series - We can now format our event series by removing line, adjusting marker color, size.

5. Add Event Data Labels - We can use an add-in to add data labels to our Event Series.  There are several free add-ins that provide data labels. I use John Walkenbach's Chart Tools Add-in for data labeling and a range of other routine chart manipulations.

J Walk's add-in is available on his site   http://j-walk.com/ss/excel/files/charttools.htm.

 

We can remove our Event Series markers and let the text stand alone.

6. Add Leader Lines - We next use the Y Axis Error Bar option to add our leader lines. We use the values in $E$2:$E$16 to specify the length of our Y Error Bar.

7. Final Chart Editing - Our chart is now ready for any final editing.

Click here for Worksheet demonstrating Trend Charts with Events.