Project Management: Show Milestones in a Timeline [Part 3 of 6]

Posted on July 9th, 2009 in Charts and Graphs , Featured , Learn Excel - 35 comments

This is the third installment of project management using excel series.

Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Part 3: Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Issue Trackers & Risk Management
Project Status Reporting – Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress

Why Create Project Timeline Chart?

There are 2 key elements in all the successful projects I have been part of.

  • They had exceptional individuals who are also exceptional team players
  • The communication and collaboration is really good.

While there is little that project management software can do when it comes to first point, the second point can be addressed by using right tools and visualizations. In this installment and the part 5 and 6 of this series, we will learn some excel based visualizations / charts that can help you to communicate about the project status and progress to your team and stake holders.

Project milestones can be shown in a simple time line chart in excel. While the chart doesn’t look complicated, it can provide good amount of information on project progress in a simple and understandable chart.

We will learn to create a project milestone chart like this:
Project Status Reporting - Show Timeline of Milestones

Steps to create a project milestone chart in excel

  1. In order to create a project milestone chart, we need to have the milestone data. The simplest format for milestone data is Date and the milestone. But since our chart requires the milestone to be displayed at a certain height on the chart, we will add the third column – height.
    Project Status Reporting - Show Timeline of Milestones - Data for the chart
    PS: the height column can be easily calculated using formulas. I leave it to your imagination.
  2. Once you have the data in the above format, we will add 2 more helper columns – named DUMMY and Milestone. The Dummy column is used to create the timeline (where Y axis value is zero). The milestone column is a more cleaned up version of milestones (see how it is showing #NA where the milestone is blank.)
    Project Status Reporting - Show Timeline of Milestones - Helper Data for the chart
  3. Now, select the date and dummy columns and insert a line chart.
    Project Status Reporting - Show Timeline of Milestones - Add a line chart
  4. To this chart, we will add one more data series – Height column.
    Project Status Reporting - Show Timeline of Milestones - Add another series
  5. Now select the height data series and change the chart type to a bar chart. Also set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in excel.
    Project Status Reporting - Show Timeline of Milestones - Change data series chart type
  6. We will also set the horizontal / axis labels for the height series as the “milestones”. We need to do this so that when we set the data labels for the height series, we will see the milestone instead of month.
    Project Status Reporting - Show Timeline of Milestones - Change horizontal labels - data series
  7. At this point our chart should look like this:
    Project Status Reporting - Show Timeline of Milestones - Chart looks like this now
  8. Now, we will add data labels to the height series. Set the label type as “category”
  9. We will also add error bars to the height series (the bar chart). We will configure the error bar in such a way that they are shown 100% on the negative side only.
  10. After doing this, the chart should look like this:
    Project Status Reporting - Show Timeline of Milestones - Add error bars and data labels
  11. Finally we will do some formatting like,
    1. Removing fill color / line from height series by setting them to None / transparent.
    2. Changing the error bar color to a dull shade of gray.
    3. Adding chart title and aligning it.
    4. Removing vertical axes and gridlines.
    5. Formatting horizontal axis – changing label orientation, removing tick marks.

    After all this is done, our project milestone time line chart should look like this:
    Project Status Reporting - Show Timeline of Milestones

  12. That is all, we now have a cool looking project milestone chart ready. Now go and achieve a milestone.

Download the Project Milestones Time Line chart template:

I am sure you are overwhelmed reading the above tutorial. You are probably thinking if it is easier to work towards the project milestones than creating this chart. Well, don’t worry. You can download the time line chart template [download .zip version] and play with it to suit your needs.

Download 24 Project Management Templates for Excel

What next?

Project timelines are a great way to tell the story of project to strangers and new people joining your project. They are a good addition to project status meetings and reports.

In the next installment of this series, we will learn how to use Excel to manage timesheets and resources.

If you are new, please read the first 2 parts of this series: Project planning using gantt charts, Tracking day to day project progress with team todo lists.

Your thoughts and suggestions?

What are your ideas on communicating project progress to stakeholders and new comers? What do you think about this tutorial? Please share through comments.

Project Management Templates for Excel

Subscribe to Chandoo.org Email updates and get a free excel e-book with 95 tips & tricks

Comments
Invisalign July 9, 2009

First Thanks For the Template and the Guide Too,
You saved Some of my Time,
its Really Good To visti Your Blog to learn these shortcuts and ideas.

zaxl July 9, 2009

Interesting, I would try using it for a life-line project.

Oliver Montero July 10, 2009

Chandoo as always I learn something easy and very usefull from your site. In fact I’ll be using this chart tomorrow for a presentation to my boss.

Thanks and keep excelent stuff like this comming!!!!

Sandra July 11, 2009

Great example, very clear explanation. I’m training a class on advanced charting techniques, can I use your examples in my notes and also direct the students to this web site for further reference?

koushik July 11, 2009

This is an extremely useful series / post. I enjoyed reading this and the 2 earlier posts. Looking forward to part 5 and 6. And, thanks for the downloadable templates – you have saved me some time.

Chandoo July 11, 2009

@Invisalign: Thank you.

@Zaxl: Cool. Thanks.

@Oliver: That is good to know. Let me know what your boss thinks.

@Sandra: Thank you. You can use this material on your training class as long as you acknowledge PHD. All the best.

@Koushik: You are welcome. I have learned quite a few techniques myself writing the series. I am happy you liked it.

Steve July 12, 2009

Chandoo
When you run a series and start naming them with the the convention 1 of 6, 2 of 6, how about you maintain the naming convention for the whole series. Some of us save up series articles to read them all in one session – consider your wrist slapped

Steve July 12, 2009

Chandoo
When you run a series and start naming them with the the convention 1 of 6, 2 of 6, how about you maintain the naming convention for the whole series. Some of us save up series articles in our reader and read the lot in one session – consider your wrist slapped

Chandoo July 12, 2009

@Steve: thanks for reminding. I have updated the post title. Next time you visit reader, the title should be changed.

Phill July 13, 2009

Hi Chandoo,

I’m learning so much from you posts, they are very creative and makes me redesigning a lot of my reports for better and clear presentation !

I have a little question ( with big impact I suppose ) …
Is there a way to create this chart in portrait ?
I would like to include the milestones into your Gantt chart but for presentation I think it would be better if the milestones are in portrait.

I can’t find a way however to put a line chart in portrait ….
You have any ideas ?

Thanks in advance

Miguel July 13, 2009

Phill, use the camera tool to capture the chart. This way, you can rotate the chart on demand.

Phill July 13, 2009

Hi Miguel,

Thanks for the tip !
Unfortunately with the camera tool the text ( milestone description ) is not presented correctly and you have to turn the page to read it.

So unless I’m doing something wrong the camera tool won’t help me here.

Jeff Weir July 13, 2009

Phil – you can rotate the milestone descriptions, so that when you rotate the camera object picture of them, they appear the right way. Right click on them, select Format Data Labels, then click on the Alignment tab, then select ‘Rotate all text 270 degrees’ from the Text Direction dropdown. That should do it, if I’ve understood your question correctly.

Or you could probably do the chart as a bar chart.

Phill July 14, 2009

Noob me, why did’nt I think of that one myself … thanks Jeff !

Abhi July 14, 2009

Great tip !
Very well explained too … thanks :)

Jeff weir July 14, 2009

No problem. You can rotate the axis entries also.

Chandoo July 15, 2009

@Phill… As Jeff suggested, you may want to try a bar chart instead of column chart based approach and make the dummy as the Y values instead of X values. This will rotate the whole thing while retaining the ability to scale etc.

@Abhi.. you are welcome :)

@Miguel and Jeff: Donuts for you for helping out Phill.. come and claim, any day in copenhagen…

jeff weir July 15, 2009

@ Chandoo… and Miguel: I’ve got my donut. It was at http://flowingdata.com/2009/07/14/how-does-the-average-consumer-spend-his-money/

It tasted as good as it looks….

Hatman August 2, 2009

Chandoo,

Very nice! Say, how can I add a visual indicator for today’s (current date)? This will give my boss a better perspective. Times marches on and he sometimes forgets (”oh crap…it’s August already?”) Is =TODAY() doable?

Hatman August 2, 2009

Chandoo,

Nevermind…figured it out for myself. Works great.

Chandoo August 3, 2009

@Hatman: Very cool. You are welcome

Richard September 15, 2009

I could use some help on tweaking the graph to show more detail and multiple milestones within a month. I noticed the graph works because the horizontal date axis uses increments of one (e.g. time period = 1 month), but how to configure it to include a milestone on 1 Sep ‘09 and 15 Sep ‘09? Many thanks.

Chandoo October 24, 2009

@Richard.. sorry for such a late reply. I havent noticed your comment till now. You can change the horizontal axis major-tick-marks from 1 month to say 2 weeks or 15 days. Or if you prefer having arbitrary time points, make a helper series and use data labels. Align the labels to the bottom and remove axis labels. If you are familiar with chart formatting, then this should be cake walk.

Dom November 30, 2009

I tried using this idea, and i have found a few ways to improve it and make it more user friendly to the viewer. First off, change the text direction so it reads vertically. Then keep all entries at the same height. Now make sure your entire period is their for me this was the november->december months, i filled the milestones in on the correct day(s). This prints well and is very easy to read. Just a thought :)

To make it even easier to read you can use some nice formatting to highlight the weekends… have one cell with the start date and use this in each cell for the days in your timeline.

=IF(OR(WEEKDAY($B$36+ROW()-(ROW($B$36)+2))=7;WEEKDAY($B$36+ROW()-(ROW($B$36)+2))=1);TEXT(+$B$36+ROW()-(ROW($B$36)+2);”ddd mmm-dd”);TEXT(+$B$36+ROW()-(ROW($B$36)+2);”dd”))

Chandoo November 30, 2009

@Dom.. thank you for sharing such a valuable trick… Vertically aligned texts are sometimes difficult to read. I generally avoid them for onscreen charts, print is ok I guess. (Also, it is kind of annoying that excel doesnt anti-alias properly when you vertically align texts).

Joe Fahim December 3, 2009

this is very useful!!

rikky August 5, 2010

simple and cool .. just like i need it, thanks mate

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL