How to Create a Timeline in Excel

1. Set up your timeline data table as shown in Figure 3 below.
2. The Year, Mo (month), Day, Event, and Height columns are the inputs. It is very important that the Year be in ascending order at first. You can leave the Month and Day fields blank for now.
3. The Axis column will be used as the horizontal time line axis. We want to be able to handle dates prior to 1900, so we calculate a decimal year. The formula for cell F31 is:
   

   =A31+(DATE(1900,IF(B31="",1,B31),0)+C31)/365.25

   or if you want the table to be more robust to copy/paste, cut/paste, and sorting, use:

   =OFFSET($A$30,ROW()-ROW($A$30),0,1,1)
    + ( DATE(1900,IF(OFFSET($B$30,ROW()-ROW($B$30),0,1,1)="", 1, 
   OFFSET($B$30,ROW()-ROW($B$30),0,1,1)),0)
    + OFFSET($C$30,ROW()-ROW($C$30),0,1,1) ) / 365.25

4. Column G is just referencing column D, but we use the OFFSET formula so that we can copy/paste and cut/paste within columns A through E without messing up the timeline. The formula for cell G31 is:

   =OFFSET($D$30,ROW()-ROW($G$30),0,1,1)

5. Copy the formulas in F31 and G31 down.

Figure 3: Data table used for creating the timeline chart.

The next step is to create a Scatter Chart with the Height values as the Y-axis (vertical axis) and the Axis values as the X-axis (horizontal axis).

1. Select cell E31:E40 (the Height values).
2. Go to Insert > Charts > Scatter and choose the chart type shown in the image on the right.
3. New Chart Tools contextual tabs will show up with the chart is selected. We need to add the X-axis values, so go to the Design tab and click the Select Data button (or right-click on the chart and choose Select Data).
4. In the Select Data Source dialog box for Series1, click the Edit button, and in the "Series X values" field, choose cells $F$31:$F$40 and press OK.
5. You can now clean up the chart by going to the Layout tab and turning OFF the legend, gridlines, and vertical axis.
6. Go to Layout > Data Labels > Right to turn ON the data labels (they will be just numbers for now).

We're going to create leader lines for the timeline by adding vertical error bars to the data series.

1. Select the data series by clicking on one of the data point.
2. Go to Layout > Error Bars > More Error Bar Options.
3. In the Vertical Error Bars tab, select the Minus direction, the No Cap end style, and set the Percentage to 100% then press Close. You may also want to go to the Line Color or Line Style tabs to make the leader line a dashed gray line.
4. We want to turn off the horizontal error bars, so go to the Layout tab and select "Series 1 X Error Bars" from the drop-down list in the "Current Selection" group. Then click on "Format Selection" right below that drop-down box. Set the end style to "No Cap" and set the "Fixed value to 0", then press Close.

This step is the most tricky if you are new to Excel charts (and even if you aren't). We are going to link the data labels, one at a time, to the corresponding cell in the Labels column.

1. Select the first Data Label and then click on that Data Label one more time. The first time you select a data label, ALL the data labels are selected. The second time you click on a data label just that ONE data label is selected.
2. With the first data label selected, press the = key, or click inside of the Formula Bar.
3. Click on cell G31 and press Enter.
4. Select the next data label, press "=", and select the next cell in the Labels column.
5. Repeat the previous step until all of the data labels have been linked to the corresponding cell in the Labels column.

You can add images and pictures to your timeline by selecting the chart and going to Insert > Picture. The problem with this approach is that you have to manually move the pictures around. The way we created the historical timeline above was by formatting the data point markers.

To format a data point marker as an image: After selecting a single data point, right-click on the data point and go to Format Data Point > Marker Options and select the image icon from the Marker Type drop-down box.

Note: The image will be inserted at its true size and cannot be resized. You may need to resize the image outside of Excel first.

If you are creating a project timeline, you can show the duration of an event by using X-error bars. The image below shows the project timeline example that is included in the timeline template.

You may just want to use the timeline template, but if you are creating your chart from scratch, follow these steps to add durations to events:

1. Create a new column in your data table for the Duration (number of days) of an event.
2. Select the chart and go to Format > Current Selection group and select "Series 1 X Error Bars" from the drop-down list then click on Format > Format Selection.
3. In the Horizontal Error Bars tab, select the Plus direction and the No Cap end style. You may also want to format the line to change the color and increase the width of the line.
4. In the Error Amount area, select Custom, click on Specify Value and then for the Positive Error Value choose the cells from your Duration column. You can leave the Negative Error Value as-is. Click OK.

Note: To add a Completion bar like we did in the above example, you would need to add another data series so that you can define another X-error bar.

If you are want to create a timeline that uses date values after the year 1900, then you can add another "dummy" series and change the chart type for the dummy series to a Line Chart. This will allow you to define the horizontal axis as a date-based axis. Doing that can greatly simplify the process of displaying the x-axis labels and editing the date range, but the events in the data table must be ordered by date.

The main changes to the above instructions are:

1. Instead of using the three Year/Month/Day columns, change the Year column to Date and enter date values (e.g. "1/1/2013").
2. Change the formula in the Axis column to:

   =IF( OFFSET($A$30,ROW()-ROW($G$30),0,1,1)=0, NA(),
   OFFSET($A$30,ROW()-ROW($A$30),0,1,1) )
   

3. Format the horizontal axis and set the Base Unit to "Days".
4. It's also important that the events be listed in order by date.

For additional reading: Bill Jelen does an excellent job of explaining the date-based axis vs. category-based axis in his book "Charts and Graphs: Microsoft Excel 2010."