5 bonus ideas that will make your Project Timeline Template even more useful

Last updated on February 6, 2012 By

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:

1.Show Project Tasks that are At Risk

2. Add Project Milestone Markers

3. Tidy up date formatting on the X-axis

4. Use VBA to automate event label updates

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

Add Chart Legend using textboxes linked to cell text

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

This image shows the finished timeline

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:

1. To get the project team’s attention so they start making up lost ground

2. To highlight the risk so you can plan around the over-run

3. To figure out whether any dependent tasks are going to be impacted

4. To figure out whether any dependent projects are going to be impacted

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

Extend the Timeline Data Table for the new Series "At Risk"

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:

This is what happens if you use "" or zero instead of NA() for your x-value

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”

1. Right click the Timeline chart and click on Select Data

2. Add a data series by clicking on the Add button

3. For series name select K30 (“At Risk”)

4. For Series X values select J33:J46 (“Start Date”)

5. For Series Y values select L33:L46 (“Height”)

6. Click on OK to close the Add data series dialog

7. Click on OK to close the Select Data dialog

Step 2.B – Format the event markers to green diamonds

1. Click on a data point to select the Series “At Risk”

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

You should also remove the data labels from this series. With the chart Series “At Risk” still selected:

1. Click on Chart Tools

2. Click on Layout

3. Select Data Labels and choose “None”

Then remove the marker outline:

1. With the Series “At Risk” selected click on Format Data Series

2. Select Marker Line Color and set the value to No Line

3. Click on OK to close the Format Data Series dialog

Step 2.C – Add the “At Risk” Error Bars

1. Select the Timeline 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 “At Risk” 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 “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.

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 K33:K46 and click OK

10. Note: Keep the Format Error Bars dialog box open

Now apply orange formatting:

1. Click on Line Color. Change the line color to Solid Line, and choose an orange color.

2. Click on Line Style. Change the width to 4pt.

3. Note: Keep the Format Error Bars dialog box open

Finally remove the Y Error Bars as they aren’t needed:

1. Make sure the 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. For Error Amount select Fixed Value

5. Set this to 0.0

6. Close the Format Error Bars dialog box


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:

1. Add XY Chart Labels – Adds labels to the points on your XY Chart data series based on any range of cells in the workbook.

2. Move XY Chart Labels – Moves the entire set of data labels or individual labels in any direction and in the increment of your choosing.

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

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


Related Posts:


Connect on YouTube, LinkedIn, Twitter.

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.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.