How to Create a Timeline in Excel
- Jon Wittwer (9/2/05)
Vertex42, L LC
T here are many ways to c reate a timeline in E xcel, but mos t of the methods I have s een make us e of the drawing tools or bar
c harts . I wrote an artic le about this a while back (s ee Timelines in E xcel). Well, I am happy to say that I have finally found an
eas ier way to rapidl y creat e time lines. T his means being able to quic kly generate timelines for various historical per iods or
doc umenting events in a pers on's life (for geneology projects , sc hool reports , etc .). T he figure below is an example, s howing s ome
of the events in the life of Benjamin Franklin.
Figure 1: An Excel chart showing an example timeline.
I f you don't have time to learn how the timeline is c reated, and want to jus t jump right in and creat e your own ti meli ne , go
ahead and download the Exc el T imeline T emplate.
Excel Timeline Template Download Now
This template c reates a timeline using an File Type: .xls
XY chart with error bars as leader lines . Size: ~40 KB
License: Free (For
(Microsoft Excel spreadsheet, No Macros) Personal Use)
T his artic le provides a tutorial describing how to c reate a timeline like the one above, from sc ratc h (without requiring any mac ros or
VBA). I won't go into detail on some of the s pec ific formatting used to fancy up the c hart, but the nec essary details are listed
Set Up the Data Table
1. O pen up a new E xcel workbook.
2. Starting with cell B5 =1 , c reate a column of numbers 1 -10 .
3. Starting with cell C5 ="E vent 1", c reate a c olumn of event labels .
4. Set up a column s tarting with c ell E5 that will repres ent the heights of the leader lines in the timeline.
5. I n c ell F5 , enter the following formula:
6. I n c ell G5 , enter the following formula:
7. Copy the formulas in F5 and G5 down to F14 and G15 , res pectively.
When you are done with thes e steps , your spreads heet s hould look similar to Figure 2 (you c an add the c oloring and c olumn lab els
however you s ee fit).
Figure 2: Data tables used for creating the timeline chart.
Note: T he timeline c hart will be referencing c olumns E , F, and G . Us ing the O FFSET formula in c olumns F and G allows greater
flexibility in how the year and event labels in c olumns B and C are modified to c reate other timelines (partic ularly when wan ting to
s ort, copy and pas te, c ut and paste, or insert and delete events ).
Create the Timeline Chart
1. Start the Chart Wizard (I nsert > C hart...)
2. I n Step 1 , c hoose the XY Scatter C hart (s ee image).
3. I n Step 2 , s et the X and Y values as follows (s ee image):
X V alues : =Sheet1!$F$5 :$F$14
Y Values : =Sheet1!$E$5 :$E$14
4. I n Step 3 , do the following in each of the tabs ...
Titles : Leave all fields blank (unless you want a T itle)
Axes : T urn off the y- axis
G ridlines : T urn off all gridlines
L egend: T urn off the legend
D ata Labels : T urn on the Y values (s ee image).
5. Click Finish
At this point, you might want to apply s ome c os metic c hanges s uch as removing the border and the gray background in the Plot
Area, c oloring the x-axis line, or making the data point markers lines rather than the blue diamonds (or whatever the default
marker is ). Y ou can apply fill patterns s uch as s hading, or even at a watermark image to the c hart by right-clicking on the c hart
edge and selec ting Format Chart Area ... T hen click on the Fill E ffec ts button.
Add the Leader Lines
T he leader lines are c reated by adding one-s ided error bars to the data s eries .
1. Right-click on a data point (not the label) and s elec t "Format D ata Series ..." to open the Fo rmat D ata Series dialog box,
or use the Chart toolbar to do the s ame thing:
2. I n the Y Error Ba rs tab, s elect the Minus display and set the P ercentage to 100 (s ee image).
3. Selec t O K. Y ou're done with this part.
Add the Timeline Event Labels
T his s tep is the mos t tricky if you are new to Exc el c harts (and even if you aren't). But, hopefully the images will help explain
1. Selec t the firs t D ata Label (the one on the far left as s hown in the image below).
2. Click on the data label once more so that you are editing jus t the one label.
3. P ress the = key, or click inside of the Formula Bar.
4. Either type in the reference, or click on c ell G5 . T he res ult s hould look like the following:
5. Repeat the firs t 4 s teps for the rest of the data labels .
Note: I f you were to referenc e column C ins tead of c olumn G , then if you c u t and pas te or ins ert/delete events , the ordering of the
data labels would be mess ed up. As long as you don't ins ert or delete entire rows , you s houldn't have a problem adding, deleting,
or sorting the events in c olumns B and C .
Using a Date Format in the Timeline Axis
You c an make the x-axis in the c hart be a date format s uc h as "yyyy" or "mm/yyyy" if you enter full dates in the "Y ear" column. For
example, if B5 is a date in the form mm/dd/yyyy, the "Axis " column will dis play the s erial number of the date. T he trick is to format
the axis of the timeline to display the yearly divis ions c orrectly:
1. Right-click on the x- axis and selec t Format A xis...
2. I n the Number tab, s elec t Custom from the Category lis t and enter yyyy as the format in the Type: field.
3. I n the Scale tab, E nter 365 .25 in the Major Unit: field.
4. I n the Scale tab, enter the s erial number of the firs t day of the beginning year in the Mini mum: field. (eg. the serial
number c orresponding to 1 /1 /2005 )
T his won't be exac t, but the 365 .25 day inc reme nt s hould work pretty well. (Monthly divisions don't work well because of the
inc onsis tent number of days in eac h month)
Additional Comments and Help
Before bringing this tutorial to a c los e, I need to explain the reason for column E . T his column allows y ou to adjus t the heights of
the leader lines as needed to make s ure your event labels do not overlap. Thi s i s the k ey to maki ng the cha rt work a s a
timeli ne cre ator. I have found that it helps to temporarily turn on the y- axis when I am making a new timeline s o that it is eas ier
to pick the y-values . T o do this : Right-c lic k on the c hart edge > C hart O ptions ... > A xes tab > C heck the Value (Y ) axis box.
A dding a nd De let ing Eve nts: I n the downloadable timeline template, I have included ins tructions for how to add and delete
events . These ins tructions ass ume that you know how to modify the data series references . Refer back to the Step 2 in the Cha rt
Wizard, if you have a ques tion about this .