How to create a Project Timeline Template today in 10 simple steps using Excel 2010

Timeline-cartoonIf you manage projects it’s important to show how many tasks there are and when they’re due to happen. This helps to explain key events in your project’s life to team members and other stakeholders.

In my experience I’ve seen project managers show project tasks using Gantt charts, but here I offer a compelling alternative – the simple project timeline tool – which you can create in Excel.

A project timeline shows how events relate to each other in time. Although timelines don’t look as sophisticated as Gantt charts, they do a really good job at communicating the big picture. A well crafted project timeline can convey useful information relating to task progress and overall project progress.

In this article I’ll show you how to make a timeline in Excel. And if you want to buy a copy you can find the purchase page by clicking here.



Easy Project Management using Microsoft Excel (Series)

This is the second part in a series on Project Management using Microsoft Excel. To read the introduction click here. You should also check out the follow-up article “5 bonus ideas that will make your Project Timeline Template even more useful” which you can find by clicking here.

I’ll add more articles in future months so be sure to bookmark this page and come back, or subscribe to my email newsletter to get free updates – you also get a free PDF with over 100 popular Excel shortcuts if you subscribe on this page.



Project Timeline Template Overview Video

We will get to the detailed how-to soon but first you should watch this 6 minute video. It gives an overview of the timeline template.

Project Timeline Template in 10 simple steps using Excel 2010
Runtime
6:08
View count
47,647



Watch for the following features:

  • Today line – red vertical line to show where we are today.
  • Duration bars – gray horizontal lines to show how long each task or phase is going to take.
  • Completion bars – green horizontal lines to show how much of each task or phase has been completed, and how much is left to do.


Purchase the Timeline Template here

If you like what you see in the video above and want to have a copy of your own to play with, buy the template now by clicking here. The downloadable template contains the completed timeline, ready for you to use for your own projects. Also included are the two tutorial videos.

timeline-template download

Click image to buy timeline template

I created the timeline template with “before” and “after” worksheets for every step in this how-to article, and the worksheets match the sections of this article to make it easier for you to follow along while reading.

Note: Timeline Template & Instructions were prepared using Excel 2010

The instructions in this article are written for Excel 2010 as that is the version of Excel I used to make the template. If you have another version of Excel you should be able to follow along, but I can’t guarantee the steps will be the same.

In fact if you try this out with Excel 2003 or Excel 2007, please let me know if it works for you – leave a comment in the comments section below. Thanks!



Watch my Video Tutorial here

I’d suggest you set aside half an hour to watch my detailed step-by-step video instructions – ideal for watching in a lunch break.

Project Timeline Template Detailed Instructions (24 mins) Excel 2010
Runtime
24:34
View count
98,570



Key moments in the video:

  • Step 1 (00:23) – Begin with the end in mind (Sketch out the timeline)
  • Step 2 (01:53) – Create your project events
  • Step 3 (02:33) – Insert XY scatter chart
  • Step 4 (05:39) – Clearn up chart formatting
  • Step 5 (09:51) – Add X and Y Error bars (gray duration & blue dropdown bars)
  • Step 6 (13:37) – Add data series “Completion”
  • Step 7 (14:55) – Format data series “Completion”
  • Step 8 (15:53) – Add X Error bars to show %Completion (green completion bars)
  • Step 9 (18:13) – Add Red Today Line
  • Step 10 (21:41) – Add Chart Legend (floating textboxes linked to cells)



Excel Timeline Step 1 – Start with the End in Mind

timeline-step-1

This image shows the finished timeline

If you were doing this from scratch without my step-by-step guide, I would recommend you get a blank piece of paper and sketch an outline of the timeline and keep it in front of you as you create it in Excel.

Don’t worry too much about how pretty it is, just get the shape down and remember that it’s for your own reference.

Since I’ve already created the timeline, I can show you a screenshot of the finished worksheet. This will help you picture the end result so you have a target to aim for.

My finished timeline shows orange “At Risk” bars, which I won’t explain in this post. You’ll need to read my next blog post where I’ll explain how to add these (coming soon).




Excel Timeline Step 2 – Create your Project Events Table

The timeline template has project events laid out as a table in Excel. I split the example project into four phases, and each phase has a certain number of sub-tasks.

timeline-step-2

This image shows the pure data with no timeline chart

Each event is given these values:

  • Phase – Phase 1, Phase 2, Phase 3, Phase 4 or “Today”. This show which phase of the project the event falls under. The rows labelled “Today” are used in step (9) “The Today Line” when we add a vertical line to show where we are today.
  • Type – Phase, Task or “Today”. This shows the type of event, whether it is a project phase or a task. The rows labelled “Today” are used in step (9) “The Today Line” when we add a vertical line to show where we are today.
  • Start Date – This is the event’s start date.
  • Event name – This is the name of the event that shows up in the chart data series labels.
  • Duration (days) – This is the duration of the event measured in days.
  • % Complete – Shows the % complete for each task and phase. 0% means the task has not started, and 100% means the task is fully complete. Phase % complete is an estimate and not calculated with reference to task completion. You will need to apply your own rules to assess the Phase % complete.
  • Completion (days) – This is the duration of the event x %Complete. A task that lasts 10 days and is 50% complete will have completion of 5 days (= 10 days x 50%).
  • Height – The height value is important because it determines how high or low the event is displayed in the timeline chart. You can set any height values you want, but it’s a good idea to stick to small numbers near to zero. I used numbers between +25 and -25. If the height is +ve the event shows above the X-axis. If the height is -ve the event shows below the X-axis.



Excel Timeline Step 3 – Insert XY Scatter Chart & add your first data series “Duration”

timeline-step-3

Insert XY scatter chart and add Series "Duration"

It’s time to insert a chart to visualize the project event data you created in Step 2. I used an XY scatter chart as it allows us to be flexible with the position of data points on both X-axis and Y-axis.

In Excel 2010 here are the instructions you need to follow:

  1. Click on the Insert Menu
  2. In the Charts group, click on Scatter
  3. Select Scatter with only Markers (the first option) and a blank chart appears on your worksheet
  4. Reposition that to cover the range B4:K26 by dragging the edges and resizing the chart (tip: hold the ALT button to snap the edges to cell borders for precision alignment)
  5. Right click the empty chart and click on Select Data
  6. Add a data series by clicking on the Add button
  7. For series name select E30 (“Duration”)
  8. For Series X values select C33:C46 (“Start Date”)
  9. For Series Y values select H33:H46 (“Height”)
  10. Click on OK to close the Add data series dialog
  11. Click on OK to close the Select Data dialog

This gives us a simple scatter chart with default formatting. Let’s format the event markers to green diamonds:

  1. Click on a data point to select the Series “Duration”
  2. Right click on any data point selected, and select Format Data Series
  3. Click on Marker Options, and select the Built-in marker type diamond
  4. Change the size to 10 pt
  5. Click on Marker Fill, and select Solid fill
  6. Change the Fill Color to green

If you require detailed step-by-step instructions, watch my video from 2 min 33 sec (Step 3).

So we have created a scatter chart with one data series called “Duration”. And at the moment it only shows the start date with a green diamond marker.

Change the title of the chart by double clicking the textbox – enter something like “Project Timeline”.

We’ll add the actual event durations in days in step 5 “The Error Bars” by adding horizontal X-error bars, but first we should clean up the chart formatting.




Excel Timeline Step 4 – Clean up the chart formatting on your Timeline Scatter Chart & Add Event Labels

timeline-step-4

Clean up the chart formatting by removing items we do not need

OK, let’s clean up our chart. You can see in the screenshot above that I got rid of some things we don’t need:

  • Y-axis – Hide the vertical Y-axis because it does not add any information to this chart.
  • Horizontal guidelines – Hide the horizontal guidelines because they do not add any information to this chart.
  • Legend – Get rid of the default chart legend because we will replace it with something that looks better in step 10 “The Chart Legends”.

I also chose to display Data labels and position them left of the data points.

In Excel 2010 a nice way to do all the above is to use the Chart Tools menu. Many of you will be familiar with changing these options so you won’t need any further guidance.

But if you want further instructions just watch my video from 5 min 39 sec (Step 4), and I take you through each step I used to clean up the chart formatting with the Chart Tools menu in Excel 2010.

How to change the text on the event labels

Changing the text for every event label on the timeline is quite tricky and time-consuming. This is because you need to select each and every label in the data series, then relabel it.

You can watch me to this in my video from 6 min 47 sec to 8 min 42 sec. In the video I sped up the process x4 times, so if you are doing this at normal speed it would take longer.

Here are instructions for how to change the event label text:

  1. Click on an Event label, to select the labels for the whole data series. Don’t click on the green diamonds because that selects the data points.
  2. Click on the Event label again, to edit just that one single label.
  3. Press the = key, or click in the Formula Bar.
  4. Either type in the cell reference for the Event label, or click on the cell with the Event label.
  5. Repeat the first 4 steps for the rest of the Event labels.

If you find this step too difficult or cumbersome, you could use VBA to make it faster – and I revisit this idea in my followup article (click here).

How to Freeze Panes

I also decided at this stage that it would be useful to freeze panes at row 28. Doing this keeps rows 1 – 27 in view, while limiting the scroll area to rows 28 and below. This means we’ll always be able to see the timeline chart.

To freeze panes in Excel 2010:

  1. Select cell A28
  2. Click on the the View menu in the Ribbon
  3. In the Window group, click on Freeze Panes
  4. In the drop-down select “Freeze Panes
  5. This adds a black horizontal line above row 28, and everything above the line is frozen. The scroll bar will only move what’s below the line.



Excel Timeline Step 5 – Add Error Bars to bring your Timeline Template to life

timeline-step-5

Add X Error Bars to show task / phase duration

After step 5, your scatter chart will start to look like a timeline as we add grey bars to show how long each event takes.

The way to do this is using X Error Bars:

  1. Select the scatter chart
  2. In the Chart Tools menu group in the Ribbon, click on the Layout menu
  3. In the Current Selection group, select the Series “Duration” from the drop down
  4. In the Analysis group, click on Error Bars, then click on More Error bar options. This brings up the Format Error Bars dialog box
  5. Back on the Current Selection group, make sure the Series “Duration” X Error Bars is selected. Excel may bring up the Y Error Bars instead, but we want the X Error Bars for this step.
  6. Look at the Format Error Bars Dialog box and make sure it says “Horizontal Error Bars
  7. Set the options: Direction = Plus, End Style = No Cap
  8. For Error Amount, click on the radio button labelled “Custom“, then the click on the button “Specify Value
  9. For Positive Error Value, select the range E33:E46 and click OK
  10. Note: Keep the Format Error Bars dialog box open

You’ll be able to see the horizontal duration lines extend out from the green diamond markers. Now we can apply some formatting:

  1. Click on Line Color. Change the line color to Solid Line, and choose a gray color.
  2. Click on Line Style. Change the width to 4pt.
  3. Note: Keep the Format Error Bars dialog box open

OK, we now have gray duration bars extending to the right of our diamond markers. Let’s add blue drop lines from the markers down to the timeline. This helps you to see the start date for each event on the timeline.

The way to do this is using Y-error bars:

  1. Make sure your chart is still selected and you can see the Chart Tools layout menu (Excel 2010)
  2. In the Current Selection group, select the Series “Duration” Y Error Bars from the drop down
  3. Look back in the Format Error Bars dialog box to check that it says “Vertical Error Bars
  4. Set the options: Direction = Minus, End Style = No Cap
  5. For Error Amount, click on the radio button labelled “Percentage“, and enter 100%
  6. Note: Keep the Format Error Bars dialog box open

Now you’ll be able to see the vertical drop lines extend down from the green diamond markers. Let’s apply some formatting:

  1. Click on Line Color. Change the line color to Solid Line, and choose a blue color.
  2. Click on Line Style. Change the width to 2pt and select a dash type.

If you require detailed step-by-step instructions, watch my video from 9 min 51 sec (Step 5).




Excel Timeline Step 6 – Add your second data series “Completion” to the Timeline Chart

timeline-step-6

Add second data series "Completion"

In step 6 we add another data series to the chart to show task completion estimates. These are calculated as a percentage of the task or phase duration.

If a task takes 10 days to complete and is 50% complete, the completion bar should show 5 days done, with 5 days remaining. This completion bar is a visual aid and does not take into account weekends and public holidays.

Add the second data series to the scatter chart with these values:

  • Name – G30 (“Completion”)
  • X values – C33:C46 (Start dates)
  • Y values – H33:H46 (Heights from axis)

In this tutorial I added the Series “Completion” after the Series “Duration”, so Excel automatically makes it appear on the top layer of the chart. You can see the Series “Completion” with its red markers in the above screenshot.

If you require detailed step-by-step instructions, watch my video from 13 min 37 sec (Step 6).




Excel Timeline Step 7 – Format your second data series

timeline-step-7

Format the markers for the second data series "Completion"

In step 7 we format the Series “Completion” so it looks like the Series “Duration”, with green diamond markers (size 10 pts).

You should remove the data labels from this series. Select the chart Series “Completion” and then:

  • Click on Chart Tools
  • Click on Layout
  • Select Data Labels and choose “None”

You should also remove the marker line:

  • With the Series “Completion” selected click on Format Data Series
  • Select Marker Line Color and set the value to No Line

If you require detailed step-by-step instructions, watch my video from 14 min 55 sec (Step 7).




Excel Timeline Step 8 – Add Error Bars to show the %Completion of each Task or Phase in your Timeline

In step 8 we add X Error Bars to the Series “Completion”. Y Error Bars are not needed because the start dates are the same.

timeline-step-8

Add green X Error Bars to show %Completion

To remove the Y Error Bars:

  • Select the Series “Completion” Y Error Bars
  • For Error Amount select Fixed Value
  • Set this to 0.0

After we complete this step we have horizontal green Completion bars for those tasks that are partially or fully complete. I decided to set the width of the green Completion bars at 7 pts wide compared to the gray Duration bars which are 4 pts wide.

If you require detailed step-by-step instructions, watch my video from 15 min 53 sec (Step 8).




Excel Timeline Step 9 – Add a vertical red “Today Line”

timeline-step-9

Add red vertical "Today Line" to show where we are today

I decided to add a vertical marker line to show where we are today. It’s a bright red vertical line that goes to the top and bottom of our chart to make it clear where today it:

  • Add a new data series and name it “Today”
  • X values – C31:C32 (Start dates)
  • Y values – H31:H32 (Heights from axis)
  • Change the top label to read “Today”
  • Move the top label so it sits above the marker
  • Make the top label bold
  • Delete the bottom label
  • Format the series to remove markers
  • Make sure there are no X Error Bars by setting the Fixed Value to 0.0
  • Set the Y Error Bars to Direction = Minus, End Style = No Cap, Percentage = 100%
  • Format the Y Error Bars to solid red lines of 3 pts width

In my template I used a fixed date for today’s date. This is to make sure when you open the template in a couple of months (or years) the today line will still be in the right time frame, and not months (or years) after the events on the project timeline.

But when you use this for live projects I suggest you replace the fixed date in cell B2 with the formula
= TODAY()

The first two lines of our data table are reserved for the today line (one for the part above the axis and one for the part below the axis). The formulas in cells C31 and C32 point to cell B2, so if you update B2 the today line will automatically move.

The = TODAY() formula will automatically use today’s date, as long as you have automatic calculation on. Or you can press F9 to force Excel to do a re-calculation of the worksheet.




Excel Timeline Step 10 – Create your custom Chart Legend by adding Textboxes linked to cell text

In step 4 we removed the chart legend, and in step 10 we are going to add our own chart legend.

timeline-step-10

Add Chart Legend using textboxes linked to cell text

We’ll use text boxes to add our own chart legend.

  1. Click on the Timeline chart
  2. Click on the Insert menu (in the Ribbon)
  3. Click on Textbox
  4. Create a Textbox in the top right hand corner of the chart
  5. Type anything in the textbox (e.g. xyz)
  6. Format the textbox background so the color matches the gray of the Duration X Error Bars
  7. Format the foreground text color to contrast with the background (e.g. make it white)
  8. While the textbox is still selected, go to the formula bar, press = then select cell E30 (“Duration”) to link the text of the textbox to the contents of cell E30

This means that whatever is in cell E30 will show up in the textbox. Now create another textbox for the Series “Completion” and link it to cell G30. Hey presto – you now have your custom chart legend.

You could stick to the default chart legend, but I like using text boxes because they are easier to reformat and reposition than the standard Excel chart legend.

If you require detailed step-by-step instructions, watch my video from 21 min 41 sec (Step 10).

Buy a copy of the Timeline Template

If you want the timeline template now instead of following the instructions to create your own, please visit the the purchase page here.

Cheers,
Victor

P.S. I have a followup Timeline article in the pipeline, and will cover some further ideas for extending the timeline template. Make sure you check it out – I will link to it once it’s published.

UPDATE (6-Feb-2012) – I have published my followup article, read it here.


About-Launch-ExcelWelcome to Launch Excel
If this is your first time to LaunchExcel.com - Welcome to Launch Excel. My name is Victor and I'm the Chief Teaching Officer of Launch Excel, a website I started to help you learn how to use Microsoft Excel more effectively. Check out my welcome page created specially for you: Click here to view Welcome Page


45 Responses to How to create a Project Timeline Template today in 10 simple steps using Excel 2010
  1. [...] so you may get variable results with other versions of Excel. Read the timeline template article here for step-by-step guidelines on how this template was put [...]

  2. Easy Project Management using Microsoft Excel
    January 31, 2012 | 11:15 am

    [...] project timelines (graphical summary of milestones) [...]

  3. [...] are many ways you could enhance the popular Project Timeline template we looked at in our previous post. In this post I’ll cover 5 different ideas you can try [...]

  4. James Black
    March 6, 2012 | 6:35 pm

    This is great info on how to set up a project timeline. Thank you for sharing this information and for your insights.

    • Victor
      March 6, 2012 | 9:49 pm

      James, thanks for the feedback – hope you enjoyed downloading and using the template.

  5. Mikael
    April 12, 2012 | 5:49 pm

    Hi, I have problems with adding more objects than there is room for on the rows. How do I extend the cell 46 limit?

    • Victor
      April 12, 2012 | 8:10 pm

      Mikael

      To add more tasks and phases I recommend that you insert new rows above row 46.

      If you simply add new rows after 46 then the chart Data ranges need to be adjusted for each of the series (Duration, Completion, Today, At Risk) – much easier to get Excel to automatically expand the Data ranges for you by insert new rows above row 46.

      To insert a new row, right click on row 46 and then select “Insert”. To insert 10 new rows, select rows 46 to 55 then right click on one of the highlighted rows and select “Insert”.

      Once you add new rows you should copy the formulas from an existing row. Then you can update the new rows to your desired values.

      Oh and if you’re adding a lot of new tasks and phases, you may need to perform a lot of labelling and I’d recommend installing a free Excel add-in to help automate the labelling process. I talk about the XY Chart Labeler in tip #5 of my follow-up bonus post that you can read here:

      http://www.launchexcel.com/timelines-in-excel-bonus-2/

      Cheers,
      Victor

  6. Steve
    April 25, 2012 | 10:58 am

    This looks amazing. You’ve done a lot of work here and it shows! Thank you for sharing so generously.

    I’m fairly new to project management and looking for a starter tool that I can start simple with, but can grow as my needs grow. One thing that concerns me is the question of what question to ask. Like, what am I missing? What would a dedicated project management program give me that might be very important to me?

    I need to get something together by next week, and it would be good for others to be able to import it into MS Project, (I think this should do that, yes?).

    What about adding costs?

    Thanks again for sharing so generously!

    ~ S

    • Victor
      April 25, 2012 | 12:01 pm

      Hi Steve

      Thanks for the great feedback on the free Launch Excel Timeline Template.

      My general advice is to use the right tool for your project. At a basic level this means:
      • MS Excel is great for a quick and cheap solution to projects that are relatively simple. The downloadable timeline I present above is fine for some purposes, and you can quickly tweak the layout to suit your organisation’s requirements.
      • MS Project is far more feature rich, and can automatically schedule tasks using entered dates and durations. In MS Project Pro 2010 you can even visualise team resources and connect teams via shared task lists (for new features in 2010 see this Microsoft Article)

      In terms of the question “what question should I ask” you could check out this article:
      http://epmlive.wordpress.com/2008/02/12/excel-vs-project-how-to-convince-managers-that-project-really-is-better-for-managing-projects/

      Some key points that jumped out at me:
      • Project can calculate critical paths automatically
      • Project allows users to manage cost and resources by defining local project resources
      • Project can be used to present project data to different stakeholders (calendar / gantt / network / timeline)
      • Project can quickly set a baseline and automatically track progress against the baseline

      There are probably several other key points which make MS Project superior for managing complex projects – and I’d welcome comments and feedback from others who are more experienced than myself.

      As for importing data from Excel into Project, yes that’s possible and you can read these Microsoft.com resources explaining how:
      Excel 2003
      Excel 2007
      Excel 2010

      And some other resources too:
      http://www.brighthub.com/office/project-management/articles/7638.aspx
      http://msdn.microsoft.com/en-us/library/bb738433(v=office.12).aspx

      Hope that helps!
      Cheers
      Victor

      • Steve
        April 25, 2012 | 5:02 pm

        Thanks, Victor.

        I’m on a Mac and I’m considering Merlin. It’s got very good reviews, but it’s a couple hundred bucks, so I hesitate. I’m going to start with pen and paper just as you suggest and see where that leads.

        Thanks again for the great info.

        Best,

        Steve

  7. Rao
    May 1, 2012 | 9:08 am

    Hi Victor,

    That’s really a good work.

    I have question regarding How to represent a task which is on hold and stared now in “AtRisk” Seried.

    ex : Task1 started on Nov’2011 and completed 30% and its in hold. Again task May start from June’2012.

    How to represent this?

    • Victor
      May 1, 2012 | 11:13 am

      Hi Rao,

      Thanks for the question. If you want to put a task on hold you can do this by adding a new series similar to the “At Risk” series.

      In the screenshot below I added a new series called “On Hold” and formatted the X-error bar to be blue, with end points of a circle and arrow. The original task length is extended so that it reaches beyond the length of the “On Hold” bar, which is why the grey bar in the background is longer than the blue “On Hold” portion.

      Timeline with Task On Hold marked in Blue (oval image was added later for purpose of highlighting)

      If you need any further help we’ll discuss over email.
      Cheers
      Victor

      P.S. If you need to contact me by email, head over to the about page and scroll down to the Contact Form.

      P.P.S You can find instructions on how to add another series on my followup page: http://www.launchexcel.com/timelines-in-excel-bonus-2/ (Idea 1 “Show Project Tasks that are At Risk”)

  8. Mazhar
    May 5, 2012 | 3:59 am

    A good learning site. Can we show two different calendar dates eg.Gregorian & Jewish Calendas on x-axis?
    Regards,

    • Victor
      May 5, 2012 | 6:47 am

      Hi Mazhar thanks for your comment and good question.

      Yes I reckon it’s possible to show 2 different calendar dates on the x-axis. Possible but very fiddly! Excel allows a secondary X-axis only if there is already a series plotted on a secondary Y-axis. And it allows a secondary Y-axis only if there are at least two series on the chart.

      I have not personally created an Excel chart with a secondary x-axis before so cannot provide detailed instructions, however here are some links with material that give a little more guidance:

      http://www.lytebyte.com/2009/02/24/how-to-create-excel-chart-with-2-y-axis-or-x-axis/
      http://www.pcreview.co.uk/forums/two-x-axes-one-y-axis-different-data-x-t2408001.html

      Hope that helps,
      Victor

      (Edit added: 17 May 2012)
      P.S. I originally listed a link to a forum post on MrExcel.com but Google blacklisted MrExcel as containing Malware (odd!) and I have removed the link just to be safe.
      If you want to see the original post you can visit it (but please be cautious) by taking out the spaces below…
      http: // www . mrexcel . com /forum/showthread.php?t=17616

  9. Susan
    May 6, 2012 | 3:02 pm

    Victor:
    Thank you so much for sharing your work. With your template and my lack of excel knowledge it is now possible for me to set up our timeline. The goal in my chart is to make sure we are not taking on too many projects that start/complete overlap each other. The risk would be two or more projects that will if awarded the project overlap with other work already committed to. How do I change my “AT RISK” to alert me when more than two projects fall anywhere within the same time frame of start/completion dates vs at risk to be completed on time?
    Thank you!!!!!
    Susan

    • Victor
      May 7, 2012 | 10:38 am

      Hi Susan

      Thanks for your question about how to automatically show which projects overlap using something similar to the “AT RISK’ series.

      My short answer is that the functionality you’re looking for is beyond my simple template. My suggestion would be to visually line up the projects so that those with similar start and end dates are close to each other, and then use your visual judgement to figure out which projects overlap. I tried working on a set of formulas to build up the desired “overlap” feature but realised that even if I got the formulas to work they’d be difficult to troubleshoot if they broke.

      Much easier and probably safer in this case to rely on visual perception which is pretty powerful. I know that’s probably not what you hoped for, so if another reader has some bright ideas we’d all be happy to hear from you!

      Cheers
      Victor

  10. [...] How to create a Project Timeline Template today in 10 simple steps using Excel 2010 [...]

  11. [...] out Victor Chan’s website, www.launchexcel.com . I particularly like the description of the project timeline as this is a very nice way of putting together a project roadmap that easy for people who are not [...]

  12. Greg
    August 14, 2012 | 3:02 am

    Great timeline. The best I found. I was able to create a timeline from scratch using your step by step instructions in Excel 2007. However, I am experiencing one problem. Whenever I save the Excel spreadsheet and then reopen it, the X axis lines disappear. Checking further, the error bar line color is reset to No Line. Both the Duration and Completion lines disappear. The data points are still visible. I displayed the lines and then saved the spreadsheet as many as 10 times with the same result. Any suggestions on how to fix this problem?

    • Victor
      August 14, 2012 | 8:17 am

      Hi Greg. I’ve not come across that problem before, where the XY scatter chart X-axis error bar disappears after a save due to the error bar line color being reset to No Line. I’ll send you an email so we can try to sort out your issue. It would help if you can send me a copy of your Timeline file (without any confidential information).

      If anyone else has come across this problem and thinks they know the answer please feel free to comment.

      Cheers
      Victor

    • Victor
      December 3, 2012 | 4:27 pm

      OK – Just want to post an update on the issue where X-error bars disappear after saving the spreadsheet.

      It looks like saving as .xls file (2003 compatible) causes this problem. So you must save the timeline spreadsheet as .xlsx file (2007+ compatible) and the X-error bars do not disappear.

      Case closed.

  13. Tom
    August 28, 2012 | 3:04 am

    Victor, how come when I extend my project milestones start date into 2013 i lose all my format on my timeline. I enter the timeline of Jan 4 2013 and all start dates move to one side of the chart.

    Any ideas.

    • Victor
      August 28, 2012 | 2:56 pm

      Tom – thanks for leaving a comment. It sounds like maybe your start dates are moving to one side of the timeline because you have some empty cells or cells with zeros that feed into the chart data range. When this happens, Excel interprets the “zero” date as 1-Jan-1900, as that’s the way Microsoft have built dates to work in Excel. Or it could be that you have dates in early 2012 as well as dates in 2013… due to the way the X-axis is set up you’re going to get a squashed chart like this:

      [Image] Squashed Timeline Chart

      With the current template you will probably have to create 2 or more timeline charts to separate out the 2012 and 2013 tasks/milestones, or you could manually change the X-axis max/min values to show a narrower date range i.e. zoom in.

  14. Onix
    October 4, 2012 | 4:18 pm

    Thank you ill review and get back with my comments but seems great at first appearance.

  15. John
    October 7, 2012 | 6:20 pm

    Victor. First of all thanks for a great instruction by far the best I was able to find. Although I have one question is it possible to replace duration with exact date. Lets say my task starts at 1st of Jan and ends at 4th of Feb instead of me writing the exact amount of days would it be possible to just write the day that the task is ending?

    BR
    John

    • Victor
      October 8, 2012 | 2:40 am

      John – thanks for the compliment about this being the best instruction you can find. I make every effort to keep an open mind about how to explain stuff so it’s easy to understand.

      To answer your question, is it possible to replace duration with end date? Yes, you can do this by adding an extra column to the left of “Duration” and call it “End Date”, and enter the exact end dates in the new column. Then replace the “Duration” column with the calculation “= End Date – Start Date” to give the number of days duration (replace the ‘End date’ and ‘Start date’ with the actual cell references).

      Here are some pointers from Microsoft on how to calculate date differences:
      http://office.microsoft.com/en-us/excel-help/calculate-the-difference-between-two-dates-HP010070467.aspx

      Hope that helps,
      Victor

  16. Craig
    October 15, 2012 | 9:02 pm

    Hello,

    Great template. However, when I try to insert new rows to extend the chart past row 46, the event column text is not ploted, instead the numeric value of the row height (row h) is what shows. Can you please tell me how to fix this? Thanks!

    • Victor
      October 16, 2012 | 3:01 am

      Hi Craig – thanks for your comment. I’d advise inserting rows above row 46, you will still need to manually update the event column text (see step 4) but the data range for the chart should expand. If you add rows below row 46, you need to specify to Excel that the chart data extends below row 46.

      Currently my template does not automatically update the event labels if you change the text in the event column. How many people would be interested in a more sophisticated version of this template? Let me know by posting a comment and if there is enough demand, I will arrange for some extra features to be added to make this even more useful!

      Cheers
      Victor

      P.S. I am currently travelling abroad and will not be able to answer your comments for a couple of weeks. If you have a specific feature request for the timeline template please leave me a comment and I will definitely read every single comment when I’m back.

      P.P.S You now get a copy of the timeline template by subscribing to my email newsletter, so if you don’t use Twitter you don’t have to “Tweet and Get It” to download a copy of the timeline. Use any of the email signup forms on this page, or visit the home page / shortcuts PDT page:

      http://www.launchexcel.com
      http://www.launchexcel.com/shortcuts-pdf-page/

  17. Dustin
    October 24, 2012 | 10:30 pm

    How do you get the X-axis dates to automatically adjust on the chart. For instance when I start my project on 12/21/12, the x-axis still shows 11/3/11 as the first date?

    • Victor
      November 7, 2012 | 7:01 am

      Hi Dustin

      If you format the X-axis and look in Axis options, you should be able to set the minimum and maximum to update automatically.

      Alternatively you can manually choose a “Fixed” number for the min and max, but bear in mind that this is represented by a number rather than a date. To figure out what number you should use, go to a blank cell in Excel and enter your desired date. Then change the format to general number format. The number you see is the one you need.

      Cheers
      Victor

  18. Catherine
    October 29, 2012 | 10:34 am

    Hi, Thank you! your instructions have been a great help to me.
    I am experiencing one problem however.
    When I select C36 – C43 for x-series the selected information doesnot show in my chart. instead each point is Jan-00 and the ‘select data source’ box states ‘data range is too complex to be displayed’
    I don’t see how my data range is any different in complexity to your own

    Can you offer any suggestions?
    Thank you
    CAtherine

    • Victor
      November 7, 2012 | 7:09 am

      Hi Catherine

      Your issue does come up when trying to modify an existing chart, with ‘data range is too complex to be displayed’ being Excel’s standard response to a slightly complex range. I emailed separately asking if you’d like me to take a look at your timeline chart.

      Cheers
      Victor

      P.S. I’ve been in contact with an Excel developer who is writing Timeline functionality into his Excel Gantt chart software (currently available for ~$30) and when I’ve tested this out I plan to write a review and link to his website. That software is automated, so it should eliminate some of the trouble you get with changing chart series and adding new data.

      • Victor
        November 22, 2012 | 4:59 pm

        UPDATE: You can check out Gantt Diva here:

        http://www.launchexcel.com/gantt-diva

        This was developed as a standalone Excel tool, written using VBA (Visual Basic for Applications). There is a 30 free trial so I suggest you give it a go. Feel free to post any feedback here.

        Cheers
        Victor

  19. Eugenia
    December 1, 2012 | 7:30 pm

    hi thank you for this helpful tool. I am trying to adapt it to display grant amounts ($ values) of projects over time. So for example a project that has started in May 2010 and ended in May 2011 worth 1 million USD. I have about 40 projects overall that I want to display over a 3 year time span however, because the duration is counted in days I am having to input number of days instead of months so there is a larger error margin as some months are shorter than others (I know only a day except for February) but is there a way to have the duration be expressed in months instead of days? Thank you so much for your help!

    • Victor
      December 13, 2012 | 3:14 pm

      Hi Eugenia

      You can have a look at using the EDATE() function. In versions prior to Excel 2007, this function was only available when the Analysis ToolPak add-in is installed, but if you have 2007 or higher then you can use the EDATE() function straight off.

      Here is Microsoft’s function reference:
      http://office.microsoft.com/en-001/excel-help/edate-HP005209073.aspx

      This is what EDATE() does:

      “Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.”

      To use it in your situation I suggest you follow these steps:

      1. Insert 2 columns between “Event” and “Duration”
      2. Relabel “Duration” to read “Duration (days)” – you can use ALT+Enter to start a new line inside the same cell if you’d like to put the (days) on a new line
      3. Name the first column you added: “Duration (months)”
      4. Name the second column you added: “EDate” or something similar for the End Date.
      5. In “Duration (months)” specify the number of months that each project lasts.
      6. In “EDate” enter the formula =EDATE(C33,E33) where column C is Start Date, column E is Duration (Months), and you replace 33 with your row number
      7. In “Duration (days)” enter the formula =F33-C33 where column F is EDate, column C is Start Date, and you replace 33 with your row number

      As you are dealing with 40 projects and may need to relabel your chart I strongly suggest you check out Idea 5. install XY Chart Labeler in my bonus article:

      http://www.launchexcel.com/timelines-in-excel-bonus-2/

      Cheers
      Victor

  20. Charles Stoyle
    December 12, 2012 | 4:08 pm

    The timeline chart does not appear with the download. Also, I think the description of the Completion (days) column should include Duration – (Duration x % Complete).

    Could I get a working and editable copy that includes the 5 bonus ideas?

    Thanks, great job!
    Charles

    • Victor
      December 13, 2012 | 3:32 pm

      Hi Charles

      Thanks for your comments.

      The Completion (days) column shows the actual portion that has been done, so I have described the column as Duration x %Complete.

      For instance a task that lasts 10 days and is 10% complete would evaluate to Completion of 10 days x 10% = 1 day.

      Duration – (Duration x %Complete) would give 9 days, or the portion left to be done.

      So I think the current description works ok.

      I don’t have a copy of the Timeline that implements the 5 bonus ideas. I presented those ideas as food for thought, or avenues for further exploration.

      (for others who’d like the link to the Bonus article, here is it:
      http://www.launchexcel.com/timelines-in-excel-bonus-2/)

      Bonus Idea 1 is actually implemented in the Timeline Template v1, available to buy with the Timeline Videos at the following page:
      http://www.launchexcel.com/timeline-videos

      Bonus Idea 2 is very simple to do, simply add another column for milestones and add a new series to the chart, then format the series markers into diamond shapes (select the series then press CTRL + 1 to bring up the format dialog box).

      Bonus Idea 5 is very handy, as I mentioned in my comment to Eugenia above. It’s a free utility you can install from Rob Bovey here:
      http://appspro.com/Utilities/ChartLabeler.htm

      Cheers
      Victor

  21. Charles Stoyle
    December 13, 2012 | 4:07 pm

    Victor,
    Of course, you are right. I thought you meant Completion = days remaining.

    Thank you very much for the file. I am subscribing, and purchasing Power Tips after submitting this comment.

    With best regards,
    Charles

  22. Michael
    December 23, 2012 | 10:57 am

    Sometimes you don’t necessarily need a timeline – but just need a gant. Here’s a free excel project planning template I created along with some easy to follow instructions on how to use it… hope someone finds this useful! http://www.mlynn.org/2012/05/excel-project-planning-spreadsheet

  23. Carlos
    January 16, 2013 | 8:48 pm

    Fantastic template, thank you so much!
    Victor, I’m not very good with Excel. How do I add an extra task to an existing phase? when I do by inserting a line between tasks, the chart above gets all mad at me and I don’t know how to update it. It starts giving me errors about formulas, etc.

    Thanks so much!
    Carlos

    • Michael
      January 18, 2013 | 10:32 am

      Carlos,

      Assuming you’re using the template that you downloaded from mlynn.org, make sure that when you insert tasks (new rows), you follow these simple steps:
      1. Select an existing row. Select the entire row by clicking in the far left row number… this will auto-select ALL columns in that row.
      2. Once you have selected the entire row, hit copy (CTRL+C on Windows, Command+C on Mac)
      3. Right+Click (or Option+Click / Mac) and select “Insert Copied Cells”

      This will ensure that the entire row along with all functions are copied. You should see the new row looking just as pretty as the others. One note – when choosing a row to copy, I don’t like to select the Top or the Bottom row because of the formatting.

      Feel free to drop me an email – or leave a comment on the template post page at mlynn.org

      –Mike

    • Victor
      February 11, 2013 | 3:02 pm

      Hi Carlos

      I suggest you check out Gantt Diva, which was created by Walter Kinio to automate the process of making Gantt Charts in Excel.

      He recently added Timeline functionality to Gantt Diva, and this looks like it will make your life easier than manually editing for extra tasks in my template (which is time consuming).

      Here is the link to Gantt Diva:
      http://www.launchexcel.com/gantt-diva

      Gantt Diva was developed as a standalone Excel tool, and it has very thorough documentation. There is also a 30 day trial, though when I last checked a full license to use Gantt Diva was only US$29, great value in my opinion!

      Cheers
      Victor

  24. Julia
    January 25, 2013 | 5:26 pm

    This is amazing. I ca’t wait to finally get the template.

  25. [...] check out Victor Chan’s website, http://www.launchexcel.com . I particularly like the description of the project timeline as this is a very nice way of putting together a project roadmap that easy for people who are not [...]