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

Posted on July 9th, 2009 in Charts and Graphs , Featured , Learn Excel - 76 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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

76 Responses to “Project Management: Show Milestones in a Timeline [Part 3 of 6]”

  1. [...] & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Show Project Milestones in a Time Line Chart Part 4: Time sheets and Resource management [upcoming] Part 5: Tracking issues and risks [upcoming] [...]

  2. zaxl says:

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

  3. Oliver Montero says:

    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!!!!

  4. Sandra says:

    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?

  5. koushik says:

    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.

  6. Chandoo says:

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

  7. Steve says:

    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

  8. Steve says:

    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

  9. Chandoo says:

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

  10. Phill says:

    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

  11. Miguel says:

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

  12. Phill says:

    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.

  13. Jeff Weir says:

    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.

  14. Phill says:

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

  15. Abhi says:

    Great tip !
    Very well explained too … thanks :)

  16. Jeff weir says:

    No problem. You can rotate the axis entries also.

  17. Chandoo says:

    @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…

  18. jeff weir says:

    @ 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….

  19. [...] & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Show Project Milestones in a Time Line Chart Part 4: Time sheets and Resource management [upcoming] Part 5: Tracking issues and risks [upcoming] [...]

  20. [...] 3 parts of the series – Making gantt charts [project planning], team todo lists [project tracking], project time lines chart [reporting]. Also check out the budget vs. actual charting alternatives post to get more [...]

  21. Hatman says:

    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?

  22. Hatman says:

    Chandoo,

    Nevermind…figured it out for myself. Works great.

  23. Chandoo says:

    @Hatman: Very cool. You are welcome

  24. [...] & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Part 4: Time sheets and Resource management Part 5: Tracking issues and risks [upcoming] Part 6: [...]

  25. Richard says:

    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.

  26. [...] & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Part 5: Issue Trackers & Risk Management Project Status [...]

  27. Chandoo says:

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

  28. [...] & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Part 6: Project Status [...]

  29. Dom says:

    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 says:

      @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).

  30. Joe Fahim says:

    this is very useful!!

  31. [...] & tracking a project plan using Gantt Charts Part2: Team To Do Lists – Project Tracking Tools Part3: Project Status Reporting – Create a Timeline to display milestones Part4: Time sheets and Resource management Part5: Issue Trackers & Risk Management Part6: [...]

  32. [...] basic time line construction is similar to the one shown in project timeline chart [...]

  33. rikky says:

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

  34. Emma says:

    I made a timeline using this guide, it worked perfectly. I was even able to add a “today()” line, a horizontal buffer-line that moves with the appropriate milestone and an indicator to show how much of the buffer is consumed.

    But now I’m testing in excel 2010 and it stopped working. Opening the old 2003 files in 2010, the milestones-lines are in the correct places, and the names are still in the labels, but the names are no longer attached to the correct milestone. Attempting to recreate the milestone chart in excel 2010, I can’t seem to get the milestone-names in the labels. Seeing how I skipped 2007 all together the ribbon layout is to much of a challange for me to figure this out, so any help would be greatly appreciated!

  35. CLARK says:

    This is awesome information.

  36. Isaac says:

    using Excel 2010.

    having difficulty with the height series…the to calculate heights, I set up a range with the heights I wanted (1,2,3,4,3,2) and using the MOD function to iterate between them when appropriate entry was provided in the milestone column.

    when I add this series to my chart, it assumes a “0″ (zero) value for the cells where none is provided…so when I add data labels, I have zeros appearing at every month that has no milestone. is there any way to do this differently so that I don’t have to turn the data label on or off for every data point?

    thanks. I really appreciate the creative stimulation and guidance I find on this site.

  37. Isaac says:

    excellent. thank you.

  38. Shellie says:

    I was using this method to create a timeline (thanks btw!) but have found a much easier way to get a similar result. By using a “Line Chart with Markers” and changing the ‘Shape Outline’ of the line to ‘No fill’ you can get more accurate plotting points. I used drop lines in the chart to connect them to the time line at the bottom and format the axis to show increments of 1 month. I set similar values to each date that you had (1,2,3 etc..) to vary the height of the points and created custom data labels which I assigned to each point (eg Project Start, Release 1 etc…). This way you can create a timeline just using the relevant dates, with no need to create a dummy series. Note: You have to set the axis Minimum to the 1st of the month as otherwise it can appear as though the dates are plotted inaccuratly on the axis if you display only the month name in the axis. I can email my example if you like.

  39. shae says:

    First thanks for this timeline chart. It looks so much nicer than the big box charts that we are used to create for timeline. I can get the month chart to work fine but have trouble doing the same timeline chart at the day level. I have milestones that are a few days apart and I tried the same concept to apply to it but the timeline on the X axis and the bars on the secondary Y axis don’t always coincide. Has anyone have any helpful tips or workarounds to make it working. My X axis base is set at Days and majoor unit is at 7 days. I am using Excel 2007.

    any help or insight is appreciated.
    thanks
    shae

  40. KJ says:

    I would really appreciate it if someone shared their Today() functionality within this timeline (a moving vertical line). Thanks in advance!

  41. Phil Hair says:

    You need to give better instructions (more detail and screen shots). The timeline works differently in Excel 2007. A bar chart comes out horizontally not vertically.

  42. Khy Phanna says:

    I like it too much

  43. Tim says:

    this looks like a simple and useful approach, but I have found it infuriating.

    Crash after crash and failure to manage to get a simple timline into anything useful. I don’t want to be a troll, but I cannot recommend this approach at all on the basis of my experience –

    like trying to nail jelly to a wall, but corrosive, noxious boiling jelly… deeply uncomfortable and frustrating

  44. Hui... says:

    @Tim
    I’ve just tried this in Excel 2010 and it works perfectly
    Can you be more explicit in regards after which step it goes astray ?
    Hui…

  45. Tim says:

    I worked off the downloaded template
    Adjusted the tables to the data I need to present
    The graph disappeared (initially just a white background, then nothing at all)
    Not responding when I try to edit the layout of the template
    tried from several different angles, always managing to edit some of the items into the shape I need (hence retaining interest), but without exception ending in “not responding” crash and graph disappearance, leaving tables.
    Try to rebuild graph from tables, only for repeated crash falilures >:[
    waste of a morning

  46. Hui... says:

    Tim,
    Do you want to email me the data you have
    Click my name for email address (at bottom of page)

  47. Andie says:

    Hi Chandoo, would you mind showing me how to calculate the height automatically, provided the height will be skipped if event is empty? I haven’t figured it out yet :( many thanks!

  48. layal says:

    Hi Chandoo,

    thank you so much for this wonderful tutorial

    would you mind showing me how to calculate the height ?I haven’t figured it out yet

  49. John says:

    This chart worked fine for me until a the dates in the referenced column are not in chronological order. How do you get this chart to show the data correctly if the dates listed are not in chronological order, i.e. the first date 1/2/2012, the second date is 2/2/2012 but the third date is 1/15/2012.

  50. Jonathan says:

    Just wanted to register my thanks for this great tool and your instructions!! I have used it several times now. It is much easier to use than a full blown project plan in MS Project!

  51. Luis says:

    Here is an improve:

    You can add a column named Type. If you apply a filter on that colunn the timeline only show the selected events.

    luiscaballero@terra.es

  52. William says:

    Chandoo,
    Many thanks for this, an excellent idea. I’d like to show a baseline project timeline but I’d also like to add another series of data. This additional series of data would show the impact of an event on the timeline (i.e. if X risk occurs then the timeline will look like the this). I assumed that I’d be able to do this as the bars are clustered so I could just add another series, however I can’t find a way to add different names to the additional bars. I could of course just set up two different timelines and use a mouse-over to switch between them as you show elsewhere on this site, but I’d like to be able to show the two timelines in the same chart. Any suggestions would be greatly appreciated! I’m using excel 2010.
    Thanks.

  53. Macarius says:

    Love it…. I want to share my version of your dashboard.

  54. Pmk11 says:

    Is there anyway to make this a vertical timeline?

  55. dickson swegenyi says:

    i would like to make flow diagrams,performance pie-charts,graphs(all types) for fleet management and maintenance/ how do you help me on this? do you have specific templates?
    Please help!!! My boss is on my neck!!!
    DICKSON

  56. BudB says:

    Thanks for this information. I am in the process of really learning how to make use of these resources. I know that many people are visual learners, so project timelines are a must at my next presentation.

  57. [...] Project Management: Show Milestones in a Timeline [Excel – ChandooOffice Timeline a free timeline maker for Microsoft PowerPoint that allows you to easily create and share beautiful timelines, gantt charts, and project schedules. [...]

  58. Steven says:

    This is awesome and very close to what I am looking for.  I need to be able to have the dates on a daily range instead of monthly, and would also like the date range to be dynamic based on start and end dates provided by a user.  I am using Excel 2010 to try to accomplish this. Does anyone know how to modify this examle to do as I describe?

  59. Kenny says:

    Hello
     
    This is my first post as I’m new to the Chandoo website but so far have found everything I’ve seen on the site absolutely brilliant!
    I’m stuck however on the formula to autocalculate the row height, I’m trying to use a combination of INT, MOD and arrays but failing badly! Can anyone help please?
     
    Thanks
    Kenny

    • Isaac says:

      Kenny,

      In order to make it work for me, I set up a helper range that specified the heights I wanted in the sequence that I wanted to use them (1,2,3,4,3,2) — Then I set up a helper column beside my milestones with a MOD formula:

      =IF(D7=”",NA(), INDEX(milestoneheight,(MOD(COUNT($E$4:$E6),6)+1)))

      If the milestone column is blank for a given month, it returns NA, and leaves that month on the milestone chart blank.  If the milestone column is not blank, it takes the next value from the “milestone height” range and uses this value as the height of the milestone marker on the milestone chart.

  60. Robert Poddar says:

    Hi
    Thanks for the useful template in tracking milstones. I would like to show in my report to the top management the baseline milestone and against it, the trending milestones (in 1 graph), so that management is aware whether we are on track or not.

    Any help in this regard would be helpful..

    thanks
    Robert

    • Isaac says:

      Robert — I’ve not tested this suggestion yet, but what about creating a second data series (with negative values rather than positive values) that shows the baseline milestones — then plot both series on the same x-axis, with columns above the axis showing actual milestones and columns below illustrating your baseline.

      Isaac 

      • Robert Poddar says:

        Hi Isaac,

        I was able to get a line graph wherein i have 1 line for the baseline milstone and another line for the trending milestones.
        You could provide me your email-id and i can send the same to you (i guess there isn’t any option in this website to upload files).

        Now i am trying to search the web, to see whether thru the MS Project 2007, i can generate the same graph. This option would be the best, so i needn’t maintain a seperate XLS document to generate this graph.

        thanks
        Robert.

  61. isabella says:

    your coooooooooooooooooooooooooool

  62. brad says:

    I am a novice with excel. It would be helpful if you actually gave STEP by STEP and not assume people know what thell they are doing with Excel. I really need this template, but couldn’t figure how to use the damn thing or to even create on from the instructions given

  63. [...] Project Milestone Chart – Free Template & Tutorial 1.31% [...]

  64. Jeff says:

    Chandoo,

    I love your milestone chart, but am having some problems with it. I read through all the comments to see if others were having the same issue and how you addressed it. I only found one comment, from Emma, similar to my problem; however, you didn’t address her issue.

    From Emma: “I’m testing in excel 2010 and it stopped working. Opening the old 2003 files in 2010, the milestones-lines are in the correct places, and the names are still in the labels, but the names are no longer attached to the correct milestone. Attempting to recreate the milestone chart in excel 2010, I can’t seem to get the milestone-names in the labels. Seeing how I skipped 2007 all together the ribbon layout is to much of a challange for me to figure this out, so any help would be greatly appreciated!”

    I am having the same issue. It all seems to work fine if I just load your example. The minute I add a row or change something, the labels start moving around and are no longer associated to their actual data from the tables. Please help.

    • Alex says:

      Hi Chandoo,

      I used this method and got fantastic results with it – basically I started using your template which worked, then rebuilt from scratch following your instructions, which worked. It looked awesome.

      However I am encountering similar problems to Emma and Jeff – I can make a lovely looking timeline, once. Huge kudos at work. Then I get asked to make a last minute change to something very simple – adding in an extra line, or even as simple as changing the date of a milestone, and all the formatting goes completely wrong.

      The column charts themselves work OK – but the labels are wrong. They change colour and may not show the correct text. I have tried experimentally adding and removing rows, and it seems like wherever the information about the labels is stored in Excel, it is getting out of sync with the data point it was originally written for. Only re-building the graph from scratch will fix the problem.

      It seems not to be possible in Excel to conditionally format the data point labels which would be great to distinguish which group each point on the timeline belongs to.

      I thought I could get around this easily by sorting the data out into different sets for each group, and then plotting on the chart a new data series for each, overlaid on the same X axis. But when Excel says ‘ plot on secondary axis’ it really seems to mean 2 of a maximum of 2. This is very frustrating – is there a way around it?

      You can find this limitation by adding another data series, which works, and shows a third and different set of category labels in the right hand side. However when trying to add in the category labels from your table, they also change the labels of one of the previous data series :( This happens whether or not you choose to plot on the secondary axis – it just changes which of the data series you impact with the change!

      These feel like bugs within Excel itself (I am on V2010) and ruin the usability of this superb use case you have kindly shared with us. I wonder if you or someone knows how to fix in VBA, or maybe someone from Microsoft can chip in and help please?

      Thanks!

    • Alex Gibson says:

      Jeff,
      Your description matches my issue also. I now have a copy of Excel 2013 and will be testing this ASAP… it feels like half-finished functionality. Any fix for 2010 might need to involve running a macro to re-apply formatting?

      Cheers
      Alex

  65. Paul says:

    Another great chart, thank you. Could you guide the solution to having another helper column B RAG with black for milestone not reached. The chart legend text to be individually conditionally formatted, black, red, amber or green (Green for achieved, red for missed etc.)

  66. Rodrigo says:

    Same issue as Emma, Jeff and Alex. Any additional change to something very simple – adding in an extra line, or even as simple as changing the date of a milestone, and all the formatting goes completely wrong, the labels are not correct.

    Chandoo or Hui, some help here please.

Leave a Reply