|This tutorial shows you how to
add important events to your trend chart to show cause - effect relationships
between the data series and outside events. Click
here for Worksheet demonstrating
Trend Chart with Events.
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
Global - Statistical Review of World Energy - 2005.
Adding Event Data to Excel XY Charts in 7 steps:
- 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.
- 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
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).
- 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.
- Format Event Series - We can now format our event series by
removing line, adjusting marker color, size.
- 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
J Walk's add-in is available on his site
We can remove our Event Series markers and let the text stand alone.
- 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
- Final Chart Editing - Our chart is now
ready for any final editing.
Click here for Worksheet
demonstrating Trend Charts with Events.