Business Tools Blog

I promise … you will love this!  The Timeline shows all of the companies that Zayo has purchased as of 6/1/08 …. pretty impressive since the first employee wasn’t hired until May 2007.

As Ken desGarennes said after he saw the timeline, “I am convinced that you can do ANYTHING in excel … you just have to figure it out.”

1. Start by setting up your data:

• Column A is the date
• Column B is the order in which the information should appear on the timeline
• Column C is the description that should appear in your chart (Hint: keep it short)
• Column D references column A, but shows the date as a number instead of date format

2. Download an Excel addin called XY Chart Labeler (click left, and follow the instructions, it’s free).

3.  Insert a Scatter Chart:  INSERT >CHART> XY (Scatter) > NEXT

 4. Select a Data Range: A1:B9 (Don’t select columns C or D), then click Next

5.  Use the Chart Wizard

6. Remove the gray background 

• Click the permiter of the gray area
• In the “Format Plot Area” window, select Border = None and Area = None

7. Use the XY labeler that you installed in Step #2.  Go to TOOLS > XY CHART LABELS > ADD CHART LABELS

8. Add Labels 

• Select a Label Range should be your descriptions. Here it is the M&A activity in column C - rows 2 through 9. (Do not include the title row in your range)
• “Select a Label Position” = “Right”

Your chart now looks like this:

9.  The next step is to change the Y axis so that the first item is at the top of the chart

• Doubleclick on the Y axis
• Check the boxes for “Values in reverse order” and “Value (x) axis crosses at the maximum value”
• Uncheck the “Maximum” box - the maximum should be fixed at 1 more than the number of items in your timeline.  We have 8 items (8+1=9)
• Click OK

 
 10.  Now we’ll drop lines to connect the data series to the timeline

• Double click on the data series to bring up the “Format Data Series” menu
• On the “Y Error Bars” tab, select “Plus”
• Make the fixed value = the number of items used in your timeline - here we have 8 items
• Click OK

Your chart looks like this:

 11.  Format the dates on the X axis

• Double click the X Axis
• On the Number tab, change the date format



• On the “Scale” tab
• Change the Minimum = the oldest date …  Column D that was created in Step 1
• Change the Maximum = the most recent Date  
• Hint - you may want to play with these to make the chart look cleaner
• Click OK

12.  Make the Y axis invisible

• Double click on the Y axis
• Then select none in all of the radial boxes
• Click OK

Your timeline is done:

I saved the template at the BusinessToolsBlog Shared Files Google Group.  Feel free to download.