Personal Timeline Excel Template

							                             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
below.


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:


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


     6.   I n c ell 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 , 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
things .


     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 .


http://www.vertex42.com/ExcelArticles/create-a-timeline.html