How to create a Project Timeline Template today in 10 simple steps using Excel 2010
Last updated on January 30, 2012 By Victor Chan
If 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.
[tubepress video=”ZtqbIDOl3w0″ showRelated=”false”]
Watch for the following features:
- How to work with Data Validation in Excel
- How to create Drop Down menus in Excel
- How to format your worksheets like a pro
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.
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.
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
Click image to buy timeline template
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.
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”
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 5. ALT button to snap the edges to cell borders for precision alignment)
6. Right click the empty chart and click on Select Data
7. Add a data series by clicking on the Add button
8. For series name select E30 (“Duration”)
9. For Series X values select C33:C46 (“Start Date”)
10. For Series Y values select H33:H46 (“Height”)
11. Click on OK to close the Add data series dialog
12. 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
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
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
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
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.
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”
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.
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.
Related Posts:
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.