There 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 out:
- Show Project Tasks that are At Risk
- Add Project Milestone Markers
- Tidy up date formatting on the X-axis
- Use VBA to automate event label updates
- Install XY Chart Labeler (free utility)
If you want to follow along I suggest you first download the Timeline Template which is compatible with Excel 2007 and Excel 2010.
Idea 1. Show Project Tasks that are At Risk
Previously we looked at 10 steps to create the basic Project Timeline chart. If you followed along you would have ended up with gray duration bars, green completion bars and a red today line.
The screenshot above shows where we ended up in the previous post at step 10.
Let’s enhance the timeline to show tasks that are at risk of over-run. We add a fourth data series called “At Risk”, and format that orange. The end result will look like this:
You can download a copy of the template from this page.
There are several reasons why you might want to show tasks or phases at risk of over-run:
- To get the project team’s attention so they start making up lost ground
- To highlight the risk so you can plan around the over-run
- To figure out whether any dependent tasks are going to be impacted
- To figure out whether any dependent projects are going to be impacted
- Just do something about it already!
I know from previous project experience that projects often over-run their original task estimates – it seems to be a fact of life. At least you now have a simple way to visualize this in the enhanced Excel timeline!
Step 1 – Extend your Project Events Data Table
Step 1.A – Add % Delay data
First thing to do is add your “% Delay” for each task or phase that is at risk of delay.
In my downloadable spreadsheet column “I” is for % Delay. If a task or phase is expected to over-run by 50% then you would put 50% in column “I”.
Next add formulas in columns J, K and L. These are conditional formulas so we can choose to show or not show data on the Timeline chart.
=IF(I38>0,H38,"") Formula in Cell L33
=IF(I33>0,E33*I33,"") Formula in Cell K33
=IF(I33>0,C33+E33,NA()) Formula in Cell J33
Step 1.B – Stop zero values from appearing in chart
When Excel creates a chart from a data series it plots all the values given to it even if those values are zero.
To stop zero values from appearing in the chart use NA() to return an error that does not get plotted on the chart:
Cell J33 = IF ( I33 > 0, C33 + E33, NA())
Note that if you enter either of these formulas…
Cell J33 = IF ( I33 > 0, C33 + E33, 0)
Cell J33 = IF ( I33 > 0, C33 + E33, "")
… then you will get this result where the data point X value is zero, so Excel plots the date as Jan-00-1900 and this squashes the present day 2012 project events so they appear on the red today line:
Step 2 – Follow these steps to add your orange “At Risk” bars
Now that you have your “At Risk” data, it’s time to add this to the Timeline chart.
Step 2.A – Add the data series “At Risk”
- Right click the Timeline chart and click on Select Data
- Add a data series by clicking on the Add button
- For series name select K30 (“At Risk”)
- For Series X values select J33:J46 (“Start Date”)
- For Series Y values select L33:L46 (“Height”)
- Click on OK to close the Add data series dialog
- Click on OK to close the Select Data dialog
Step 2.B – Format the event markers to green diamonds
- Click on a data point to select the Series “At Risk”
- Right click on any data point selected, and select Format Data Series
- Click on Marker Options, and select the Built-in marker type diamond
- Change the size to 10 pt
- Click on Marker Fill, and select Solid fill
- Change the Fill Color to green
You should also remove the data labels from this series. With the chart Series “At Risk” still selected:
- Click on Chart Tools
- Click on Layout
- Select Data Labels and choose “None”
Then remove the marker outline:
- With the Series “At Risk” selected click on Format Data Series
- Select Marker Line Color and set the value to No Line
- Click on OK to close the Format Data Series dialog
Step 2.C – Add the “At Risk” Error Bars
- Select the Timeline chart
- In the Chart Tools menu group in the Ribbon, click on the Layout menu
- In the Current Selection group, select the Series “At Risk” from the drop down
- In the Analysis group, click on Error Bars, then click on More Error bar options. This brings up the Format Error Bars dialog box
- Back on the Current Selection group, make sure the Series “At Risk” X Error Bars is selected. Excel may bring up the Y Error Bars instead, but we want the X Error Bars for this step.
- Look at the Format Error Bars Dialog box and make sure it says “Horizontal Error Bars”
- Set the options: Direction = Plus, End Style = No Cap
- For Error Amount, click on the radio button labelled “Custom“, then the click on the button “Specify Value“
- For Positive Error Value, select the range K33:K46 and click OK
- Note: Keep the Format Error Bars dialog box open
Now apply orange formatting:
- Click on Line Color. Change the line color to Solid Line, and choose an orange color.
- Click on Line Style. Change the width to 4pt.
- Note: Keep the Format Error Bars dialog box open
Finally remove the Y Error Bars as they aren’t needed:
- Make sure the chart is still selected and you can see the Chart Tools layout menu (Excel 2010)
- In the Current Selection group, select the Series “Duration” Y Error Bars from the drop down
- Look back in the Format Error Bars dialog box to check that it says “Vertical Error Bars“
- For Error Amount select Fixed Value
- Set this to 0.0
- Close the Format Error Bars dialog box
Idea 2. Add Project Milestone Markers
To show project milestones rather than Phases and Tasks, you can add a new data series where each item has 0 days duration. And you can also format the item marker a different shape and/or color.
This is useful to show items such as:
- Project Proposal Due
- Project Funding Granted
- Project Stage Review
- Project Deliverable Due
Idea 3. Tidy up date formatting on the X-axis
In my downloadable template I allow Excel to automatically calculate the X-axis min, max and step values. That’s because it’s fiddly to get the dates lined up.
If you want to fix the X-axis dates to look more natural you may need to select more meaningful axis scales. I recommend you check out a neat page on Vertex42’s website that was the inspiration for my Timeline Chart template.
On that page you’ll see an explanation of how to create a timeline in Excel (written by Jon Wittwer in 2005), and towards the bottom of the page are instructions on how to use a date format in the timeline axis, and also how to use month names in the timeline.
If you want to refer to the instructions there, visit this page (note: clicking this link takes you to a 3rd party external website and I am not responsible for any content on that website or any other 3rd party external website).
Idea 4. Use VBA to automate event label updates
If you remember adding event labels while following my Timeline Chart tutorial at step 4 “The Basic Formatting”, you may remember how difficult and time-consuming this step was. Also if you change the order of events you may need to reorder the labels, which is an inconvenient and time-consuming job.
What if you could do this with the click of a button? Perhaps you can write some VBA code to do just that. Or you can check out the Timeline Template created by Jon Wittwer of Vertex42.
Jon Wittwer’s Timeline Template contains some VBA code that reads all the event names and updates all the labels automatically. So by clicking on the “Update Labels” button you can update all labels automatically.
Jon’s download has two versions of the Timeline Chart – one for historical events that take place over years and the other for shorter projects that take place over months or days.
There is a free copy (without VBA) and a $14.95 copy (with VBA) which you need to buy from Jon’s website to examine and use the VBA.
Idea 5. Install XY Chart Labeler (free utility)
There is a brilliant free utility written by Excel MVP Rob Bovey called the “XY Chart Labeler”.
This enables you to do these neat things:
- Add XY Chart Labels – Adds labels to the points on your XY Chart data series based on any range of cells in the workbook.
- Move XY Chart Labels – Moves the entire set of data labels or individual labels in any direction and in the increment of your choosing.
- Manual Labeler – When you don’t want to label an entire data series, but rather use data labels to highlight specific data points, the manual labeler allows you to do this.
- Delete Chart Labels – Allows you to quickly delete data labels from a single chart series or all series on a chart.
By installing the utility you can do these things for any XY chart in any workbook you use. In fact Rob states that you can use the utility to label any type of Excel chart series that will accept data labels.
As of the time of writing, the XY Chart Labeler Utility is in Version 7.1 and is compatible up to 64-bit Excel 2010.
Although using the utility involves more clicks than the Vertex42 VBA solution, the XY Chart Labeler is free and can be used in any of your workbooks without transferring any code. You only need to install the utility once.
Check out the XY Chart Labeler here.
Rob has written instructions on how to use the XY Chart Labeler on his website.
Get free updates from Launch Excel
If you’re interested in receiving updates about future articles from Launch Excel, take this chance to sign up for my RSS feed. You can also follow me on Twitter by clicking here. And it would be great if you could share this article with your colleagues and friends using the share buttons below.
Do you have any other ideas to further enhance the Timeline Template? Use the comments section below and send in your comments.