A timeline chart allows the reader to understand the chain of events as they take place over a period of time. The events are typically represented as markers along a horizontal or a vertical line.
In our previous post on adding interactive labels to chart, we saw how to fit a large number of event descriptions in a single chart. Today we look at a similar approach in order to make an interactive timeline chart where we pack a large number of events and their descriptions into a single vertically oriented graphic.
So let’s get started.
Layout the data for the Timeline Chart
The basic data for the timeline chart consist of essentially just three columns. The first one (labelled ‘Date’) is the date of the event and the second one (labelled the ‘Event Description’ and right next to Date) is the description of the event. The date that have no events associated with them can be filled with a ‘#n/a’ so that they don’t show up when we construct the chart.
Now comes the slightly tricky part. In order to make our timeline chart interactive, we will add a scrollbar to it. (To add a scrollbar to the chart, select the chart and then use the ‘Form Toolbar’ to draw a scrollbar within the chart boundaries.) Double click on the scrollbar and link it to a particular cell which will act as the counter. In our case, we use cell E5.) This step can also be postponed till we set up the other elements of the charts so as to avoid cluttering the chart.
It’s time now to go back and create the first series. The first series (an x-y chart) consists of 10 data points (you can increase or decrease the number of points that you want to show on the timeline).
If you noticed, we have used a named range to set the X-values of the first chart series. The named range refers to a dynamic range consisting of dates from column C (constructed using the OFFSET Formula. As the user moves the scrollbar, the value in cell E5 changes, (remember we linked both of them in the earlier step). That causes the named range to pick up a new date range (by virtue of it using the OFFSET function).
This is how the chart looks like after the first step.
Adding Active Date Marker for the Timeline Chart
We now insert a marker that will highlight the active date. As the user scrolls along, the marker lets him know which particular date in the timeline is active. The x and y constituents of this chart series are shown below.
How did we get these values? Well … if you look at the formulas used for cells C3 and D3, they essentially offset the first cells in the basic chart data series by the number of rows shown in the cell (E5 in our case) which is linked to the scrollbar. Cell C3 offsets the date and D3 offsets the event description. Both of them ensure that the marker x-y coordinates correspond the user current selection when he/she is using the scrollbar. Once this series has been entered into the timeline chart, it looks like this:
Adding Event Description to the Timeline Chart
This part is slightly tricky. Turn on the label for the second data series that we created in the prebious step. Now carefully select that label, press “=” (without the quotes) and select the cell D3 (the one that contains the current event description). Press enter. (Thanks Jon for this tip). You will have a new label appearing on the chart. As the user scrolls along, the label gets updated with new values.
Formatting the Timeline Chart
If you want, make changes to the marker, remove the legend, align the description and add a title to the chart (o yes 😎 ).