How to Create a Timeline in Excel
There are many ways to create a timeline in Excel. Some methods use drawing tools or bar charts, or a gantt chart for project timelines. I wrote about these other techniques a few years ago (see Timelines in Excel). In 2005 I developed a way to create timelines quickly in Excel using an XY scatter chart with events as the data labels. Continue reading below to learn how to create the timeline from scratch or download the free timeline template.
The technique explained on this page is particularly useful for creating historical timelines and project timelines, as well as genealogical timelines that highlight events in a person's life. The image below was created using the original Excel Timeline Template in Excel 2003.
The timeline example above is sort of bland, but with a little formatting and some images you can make your timeline look much more interesting. You can insert images into Excel charts and you can also format a data point marker so that it displays an image. That is what I did to create the new version of the timeline shown below.
If you want to start creating your own timelines right away, you can download the free timeline template. If you want to learn more about how to create a chart like this from scratch, read the "how to" section below the download block.
Excel Timeline Templatefor Excel 2007+
This free timeline template helps you create a timeline using the technique described in the tutorial below.
New! (5/6/2013): The historical timeline in this new version works for dates prior to 1900 (though the x-axis still only shows the year labels).
The spreadsheet includes a project timeline example as well, where the horizontal axis is based on dates rather than years, and events can show both duration and completion status.
"No Installation, No Macros - Just a simple spreadsheet"
Rate and Review this Timeline Template
To view and write reviews, click on the button below.
How to Create a Timeline in Excel
The following instructions have been updated for Excel 2010.
Set Up the Data Table for the Time Line
- Set up your timeline data table as shown in Figure 3 below.
- 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.
- 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.25or if you want the table to be more robust to copy/paste, cut/paste, and sorting, use:
+ ( DATE(1900,IF(OFFSET($B$30,ROW()-ROW($B$30),0,1,1)="", 1,
+ OFFSET($C$30,ROW()-ROW($C$30),0,1,1) ) / 365.25
- 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:
- Copy the formulas in F31 and G31 down.
Create 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).
- Select cell E31:E40 (the Height values).
- Go to Insert > Charts > Scatter and choose the chart type shown in the image on the right.
- 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).
- 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.
- You can now clean up the chart by going to the Layout tab and turning OFF the legend, gridlines, and vertical axis.
- Go to Layout > Data Labels > Right to turn ON the data labels (they will be just numbers for now).
Add the Leader Lines
We're going to create leader lines for the timeline by adding vertical error bars to the data series.
- Select the data series by clicking on one of the data point.
- Go to Layout > Error Bars > More Error Bar Options.
- 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.
- 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.
Add the Timeline Event Labels
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.
- 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.
- With the first data label selected, press the = key, or click inside of the Formula Bar.
- Click on cell G31 and press Enter.
- Select the next data label, press "=", and select the next cell in the Labels column.
- Repeat the previous step until all of the data labels have been linked to the corresponding cell in the Labels column.
Add Pictures and Images to the Timeline
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.
Showing the Duration of an Event in your Timeline
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:
- Create a new column in your data table for the Duration (number of days) of an event.
- 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.
- 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.
- 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.
Using a Date-Based Axis
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:
- Instead of using the three Year/Month/Day columns, change the Year column to Date and enter date values (e.g. "1/1/2013").
- Change the formula in the Axis column to:
=IF( OFFSET($A$30,ROW()-ROW($G$30),0,1,1)=0, NA(),
- Format the horizontal axis and set the Base Unit to "Days".
- 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."