How to Create a Timeline in Excel

There are many ways to create a timeline in Excel, but most of the methods I have seen make use of the drawing tools or bar charts. I wrote an article about this a while back (see Timelines in Excel). Well, I am happy to say that I have finally found an easier way to rapidly create timelines. This means being able to quickly generate timelines for various historical periods or documenting events in a person's life (for geneology projects, school reports, etc.). The figure below is an example, showing some of the events in the life of Benjamin Franklin.

Figure 1: An Excel chart showing an example timeline.

If you don't have time to learn how the timeline is created, and want to just jump right in and create your own timeline, go ahead and download the Excel Timeline Template.

Excel Timeline Template This template creates a timeline using an XY chart with error bars as leader lines. (Microsoft Excel spreadsheet, No Macros)

Download Now File Type: .xls Size: ~40 KB License: Free (For Personal Use)

This article provides a tutorial describing how to create a timeline like the one above, from scratch (without requiring any macros or VBA). I won't go into detail on some of the specific formatting used to fancy up the chart, but the necessary details are listed below.

Set Up the Data Table

1. Open up a new Excel workbook.
2. Starting with cell B5=1, create a column of numbers 1-10.
3. Starting with cell C5="Event 1", create a column of event labels.
4. Set up a column starting with cell E5 that will represent the heights of the leader lines in the timeline.
5. In cell F5, enter the following formula:
   

   =OFFSET($B$4,ROW()-ROW($F$4),0,1,1)

6. In cell G5, enter the following formula:

   =OFFSET($C$4,ROW()-ROW($G$4),0,1,1)

7. Copy the formulas in F5 and G5 down to F14 and G15, respectively.

When you are done with these steps, your spreadsheet should look similar to Figure 2 (you can add the coloring and column labels however you see fit).

Figure 2: Data tables used for creating the timeline chart.

Note: The timeline chart will be referencing columns E, F, and G. Using the OFFSET formula in columns F and G allows greater flexibility in how the year and event labels in columns B and C are modified to create other timelines (particularly when wanting to sort, copy and paste, cut and paste, or insert and delete events).

Create the Timeline Chart

1. Start the Chart Wizard (Insert > Chart...)
2. In Step 1, choose the XY Scatter Chart (see image).
3. In Step 2, set the X and Y values as follows (see image):
   X Values: =Sheet1!$F$5:$F$14
   Y Values: =Sheet1!$E$5:$E$14
4. In Step 3, do the following in each of the tabs ...
   Titles: Leave all fields blank (unless you want a Title)
   Axes: Turn off the y-axis
   Gridlines: Turn off all gridlines
   Legend: Turn off the legend
   Data Labels: Turn on the Y values (see image).
5. Click Finish

At this point, you might want to apply some cosmetic changes such as removing the border and the gray background in the Plot Area, coloring the x-axis line, or making the data point markers lines rather than the blue diamonds (or whatever the default marker is). You can apply fill patterns such as shading, or even at a watermark image to the chart by right-clicking on the chart edge and selecting Format Chart Area ... Then click on the Fill Effects button.

Add the Leader Lines

The leader lines are created by adding one-sided error bars to the data series.

1. Right-click on a data point (not the label) and select "Format Data Series..." to open the Format Data Series dialog box, or use the Chart toolbar to do the same thing:
   
2. In the Y Error Bars tab, select the Minus display and set the Percentage to 100 (see image).
3. Select OK. You're done with this part.

Add the Timeline Event Labels

1. Select the first Data Label (the one on the far left as shown in the image below).
   
2. Click on the data label once more so that you are editing just the one label.
3. Press the = key, or click inside of the Formula Bar.
4. Either type in the reference, or click on cell G5. The result should look like the following:
   
5. Repeat the first 4 steps for the rest of the data labels.

Note: If you were to reference column C instead of column G, then if you cut and paste or insert/delete events, the ordering of the data labels would be messed up. As long as you don't insert or delete entire rows, you shouldn't have a problem adding, deleting, or sorting the events in columns B and C.

Using a Date Format in the Timeline Axis

1. Right-click on the x-axis and select Format Axis...
2. In the Number tab, select Custom from the Category list and enter yyyy as the format in the Type: field.
3. In the Scale tab, enter 365.25 in the Major Unit: field.
4. In the Scale tab, enter the date for the first day of the beginning year (e.g. 1/1/2005) in the Minimum: field and the date for the first day of the ending year (e.g. 1/1/2007) in the Maximum: field. Excel converts the date to the corresponding serial number.

Using Month Names in the Timeline: When specifying month names as tick marks using a scatter plot like in the figure below, you can't be exact because the months don't have the same number of days, but you can get pretty close. Right-click on the x-axis and select Properties. In the Scale tab, set the 'Minimum' value to the 1st, 2nd, or 3rd day of the month (e.g. 1/2/2007) and the 'Major Unit' to 30.5 days to avoid repeating month names.To determine what day to start on, use the following guide ( Month(startday) ): Jan(2), Feb(1), Mar(3), Apr(3), May(3), Jun(3), Jul(3), Aug(3), Sep(2), Oct(2), Nov(2), Dec(2).

Showing the Duration of an Event

Although it may be more work than it is worth, it is possible to show the duration for an event by using X-error bars.

1. Create a column for the Duration (number of days) corresponding to an event. You could use column D.
2. Right-click on a data point (not the label) and select "Format Data Series..." to open the Format Data Series dialog box, or use the Chart toolbar to do the same thing.
3. In the X Error Bars tab, select the Plus display and set the 'Custom: +' range to the Duration column that you just created.
4. Select OK. You're done with this part.
5. Using the Chart toolbar, select the X Error Bars from the drop down box and then click on the Format button. This allows you to change the color and linewidth properties for JUST the X Error Bars.

Note: In order to make the duration bars different colors, you would need to use more than one data series. You CAN format each individual data point label, but the error bars for a single data series all have the same color, linewidth, etc.

Additional Comments and Help

Before bringing this tutorial to a close, I need to explain the reason for column E. This column allows you to adjust the heights of the leader lines as needed to make sure your event labels do not overlap. This is the key to making the chart work as a timeline creator. I have found that it helps to temporarily turn on the y-axis when I am making a new timeline so that it is easier to pick the y-values. To do this: Right-click on the chart edge > Chart Options... > Axes tab > Check the Value (Y) axis box.

Adding and Deleting Events: In the downloadable timeline template, I have included instructions for how to add and delete events. These instructions assume that you know how to modify the data series references. Refer back to the Step 2 in the Chart Wizard, if you have a question about this.