Categorized | excel chart

Timeline Chart in Excel with Interactive Event Descriptions (Download & Template)

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.


force-align-xy-pointsAt the same time, edit the chart’s X-axis and increase the maximum value by a factor of 1000 or more so that all the x-y points in the chart now get forced aligned with the left y-axis.

Formatting the Timeline Chart

If you’ve come all the way till this point, the rest is easy. Remove the chart plot border and the X-axis. Also set the tick mark types on the Y-axis to none. Also reverse the values on the Y-axis.

If you want, make changes to the marker, remove the legend, align the description and add a title to the chart (o yes 😎 ).


You can download the timeline chart here or click on the button below.


What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Chandoo wrote:

    Wow.. very good idea Ajay… I like the use of form control to show a time window. Thank you.

    PS: it might be a good idea to show timeline horizontally, it looks natural that way.

  2. Ajay wrote:

    @Chandoo – Yeah. The orientation of the dates was a bit a problem – they appear more legible when the chart is vertically oriented. But I agree – a timeline makes much more sense when it is inclined horizontally.


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel