Excel Timelines

Excel wasn't designed for making timelines, but why should that stop us? :D In this example, we're going to create a timeline with overlapping dates.

1. Start by entering the dates into your spreadsheet as shown:


2. Next, subtract the "bar start" values from the "bar end" values to calculate "bar length."

bar length

3. Now, Format the dates as numbers (Format/Cells/Numbers...). This is a temporary step to allow Excel to properly graph the data that you will undo later.

dates to numbers

4. Next, highlight your data and Insert a Chart of the type "Bar/Stacked Bar."

insert chart

5. You can now Format the dates as dates once more in the data column and the chart will continue to work.

format axis

6. Next, double-click on the x-axis in the area where the dates are displayed. We will rotate them to make them more legible.

rotate dates

7. In the Format Axis controls, select Text Box and adjust the Custom Angle from 0 to 45 degrees.

adjust scale

8. Set the scale of the x-axis so that the minimum is the lowest number (the earliest date) in the timeline. In our example, this was "37042.0" which corresponds to 6/1/2005.

rescaled chart

9. Now, we need to hide the blue "Date" bars. Double-click on the blue bars or on the "Date" area in the legend to bring up the "Format Legend Entry" controls.

format legend

10. Under Fill, in the Solid tab, select "No Fill" from the Color pull down menu and the blue bars vanish!

hide bars

11. Finally, delete the legend itself to cover your tracks! Continue to tweak the chart to get it exactly the way you want it.

axis labels

12. Add a title and axis labels from the Chart Layout/Chart Title and Chart Layout/Axis Titles ribbon. Here's a sample of the final product:

final chart

13. You can download this example to play around with here. There's also a second, simpler example of a non-overlapping timeline in the spreadsheet.