Manual Gantt Charting in Excel (Discussion)

Manual Gantt Charting in Excel (Discussion)

This page is for questions and answers related to using the spreadsheet. For the files, see this page.

If you have questions, this video may answer your questions. It describes how to change the date ranges and goes through the process of creating a chart from scratch. It demonstrates Excel techniques you may not be aware of.

Enjoy!

87 Comments

  1. Jianmin 17 years ago

    We all know there’re lots of open source project management tool, why don’t you try some of them. Eg. Gantt Project (http://ganttproject.biz/)

    Anyway, creating gantt chart in excel is not something interesting.

    Cheers,

    • Dave Smith 13 years ago

      Jianmin,

      That was a great link! I was looking for something lightweight to manage some projects at the office, and while I’ve benefited from Mr. Seah’s work before, I was looking for an application versus loads of Excel files. This is perfect for me to use, I’ll send it up to my manager to see if we can’t possibly include this as an option for our end-users who want some sort of project management suite!

      -D

  2. Gerard 17 years ago

    This is really useful – it can be surprisingly tricky to produce something this simple using project management software. This is as quick and intuitive as back-of-the-envelope planning, but gives a presentable result that’s easily pasted into a report, fax or email. Thanks for posting.

  3. Dave Seah 17 years ago

    Jianmin: That’s what I thought too, that creating Gantt charts by hand just wasn’t that interesting, especially since it just sits there and doesn’t do much automatically. On the other hand, several people have asked about it over the years, and last night I finally thought, “I should post this”. I think people may find the visualization aspect useful, and maybe this would be an example of something they never thought of doing in Excel. A lot of people use Excel as graph paper or list software, and showing another use of it can give people the insight they need to find the solution they want. Or maybe rule one out.

    Gerard: Eloquently and concisely put! I’ve tended to do my project planning using the method of listing all my asset building requirements on the left-hand side, and then plugging in delivery dates, and then working backwards from that. Trying to do this in Project Management Software is incredibly tedious because they’re focused on generating reports rather than flexible planning and resource allocation. Though I haven’t tried all of them, just MS Project and an app called Timeline a few years back. They both sucked for software development.

  4. Mark 17 years ago

    Dave: I was once charged with streamlining the Excel Gantt chart process and worked up some formulas in Excel to fill in the timelines based on start/end dates, skipping weekends and holidays that were entered in a hidden column. I’ll email it to you.

  5. Corrie 17 years ago

    Cool! Before I switched to Basecamp, I had something similar but much less good-looking. :-)

    I don’t know much beyond making basic formulas in Excel—can you explain what’s happening in columns 1 and 2? Or is that an Office 2007 thing?

  6. Dave Seah 17 years ago

    Mark: Sounds great! Looking forward to seeing that!

    Corrie: That’s the row grouping stuff in Excel. If you collapse the row, just the main timeline shows, which is useful if you’re just trying to get an overall sense of the project’s shape. It’s been in Excel for a long time, though the column 1/2 thing that’s going on is something I have only noticed in Office 2007.

  7. Michael Doan 17 years ago

    David, this looks very nice.  I’ll use it next time as a starting point.  I’ve created similar Gantt charts in Excel, but one trick I implement is to use conditional formatting to fill in the cell shading.  For example, when I type “.” in a cell it will automatically filling in the cell with, say, orange.  Copy and paste works too, but where’s the fun in that? ;)

  8. Andrew Terry 17 years ago

    The Excel model is great for encouraging “non project manager” types to provide project plans. In my experience, asking someone (usually a technician, responsible for the delivery of a sub-element of a solution) to provide a project plan just freaks them out; but show them an Excel spreadsheet, and ask them to provide a “activity list”, doesn’t evoke the same response.

    What my own version has been lacking is the graphical presentation… I’ll be using your version from now on, so, many thanks!

  9. Dave Seah 17 years ago

    Michael: That’s a nice trick! Maybe I could make it so . was one color, : was a deliverable.

    Andrew: That’s a great observation…thanks for sharing that! I’ve previously tended to keep all the PM to myself because of the freak-out experience, but framing it like you said is something that really could work. That’s awesome!

  10. Neal 17 years ago

    Dave/Mark:

    Can you please email me the automated excel sheet with formula’s as well?

    Appreciate your help
    regards

  11. David Noble 17 years ago

    This imports very cleanly into OpenOffice 2.2, including the row grouping. That’s probably an unintended side effect of keeping it simple.

    I don’t have an immediate use for this, but it looks like something good to have in my bag of tricks. One thought is that it might make an interesting export format for custom schedule-generating tools.

  12. Matt 17 years ago

    Dave/Mark: Ditto what Neal said!  I spent some serious (-ly inept) time trying to figure that same thing out.  I would really appreciate the help.

  13. Maia 17 years ago

    Mark or Dave: I’d love a copy of the automated sheet containing formulas as well.

    Dave: this is really useful, thanks for posting. I hadn’t noticed the row collapsing option previously either.

  14. Kenneth 17 years ago

    Hi Guys Someone please also e-mail me that fantastic automated Gantt chart spreadsheet Mark made?

    Thanks in Advance!

  15. Kent Larsson 17 years ago

    Nice initiative! As neither of the two versions wanted to upload into Google Spreadsheets (GS), I took the liberty to convert your xls-version and share it with for the folks who prefer GS.

    Here is the version, you can just make a copy of it into your own GS account. Link: http://spreadsheets.google.com/ccc?key=pfQVD8JhsQziLZiRLt6qyTA&hl=en

    If you want to do it manually, this is what I did. It was done by importing it into Open Office Calc (GOC). Merging the cells together for the project tasks and saving it as xls version 97. (The only Excel version GS wanted to accept when saving this particular document in GOC.)

  16. Dave Seah 17 years ago

    Neal, Matt, Maia, Kenneth: I’ll try to check out and upload Mark’s spreadsheet as soon as possible…I’m facing a few big deadlines at the moment. Perhaps Mark could post his version over on his website in the meantime.

  17. Dave Seah 17 years ago

    Kent: Awesome! Thanks for doing that! Looks great!

  18. Douglas 17 years ago

    Maybe this is too sad to mention, but I have NEVER figured out how to get the ‘twisties’ on the left-hand side.  I haven’t even figured out how to ask for help in Help.

    How do you do that?

  19. RobotII 17 years ago

    Has anyone had a look at http://openproj.org/

    It seems to aim to be a free replacement for MS Project. BTW, I really like what has been done here.

  20. Mark 17 years ago

    The Dynagantt version I had sent Dave is posted on my site at:

    http://www.c77studios.com/post/automate_your_manual_gantt_chart_in_excel/

    It’s basic, but the formulas and thinking are in it to automated duration and charting through formulas and conditional formatting. Some loose documentation is posted too.

    Enjoy!

  21. Neal 17 years ago

    Thanks Mark.
    I will analyse this today evening from home :)
    Appreciate your help

    Neal

  22. Manish Anandani 17 years ago

    I have been using this for sometime but it is a little more simple (crud?). I have used MS project as well but I guess if you want to be more agile , this is the way to go!

  23. Matt 17 years ago

    Mark: Thank you for posting!  That is definitely a good starting point.

  24. Noel 17 years ago

    My boss just asked me today right before 5:00 if would create a project management spreadsheet for him. I’m a little curious about the zeros, ones and twos at the top of the page. Did you place the ones twos and threes there just for this example or do they actually serve a purpose. I can’t seem to really figure it out. They do not appear to fit into any hidden formulas.

  25. Paolo 17 years ago

    Hi David,
    I created an Italian version including the whole year 2008 splitted in two sheets.

    This version includes holidays and week numbers.
    Direct link to the Excel 2003 version here:
    http://paolo.ciarrocchi.googlepages.com/EsempioGANT2008.xls

    Post in my blog (sorry, it’s in Italian):
    http://unprojectmanager.wordpress.com/2007/12/05/gant-con-excel/

  26. Greg 17 years ago

    Hi Dave,

    I saw this thread and loved the examples.  I also made a dynamic gantt chart generator in Excel some time ago which I would be happy to email to you.

    It is similar to Mark’s in that it will create the gantt chart using conditional formatting and has a couple of additional features which help the user track progress better (i.e. highlights the column corresponding to the current day, auto-filters so that you can view Overdue Tasks or Today’s Tasks including macro to clear filtering to go back to full list, etc.)

    Let me know if this is something you are still interested in.

    Regards,

    Greg

  27. Indrek 17 years ago

    There’s one web based and free gantt chart application I really liked http://www.yutiti.com

  28. Lester 17 years ago

    Being a Project Manager Contractor I must be flexible as possible going from one contract to another and, of course, one paradigm to another.  There are many project tools large companies have invested throughout the last few years; however, they all own Excel.  I have found, like my current contract, all project team members may not have access to the PMO tools, like Clarity.  As a result I find myself creating and maintaining project plans in Excel in order to share with all members.  Also, most of my technical Architects cannot stomach the look and clumsy mechanical feel of Workbench.  I find your spreadsheet valuable and will share it with my fellow PM’s.  Your page has been bookmarked and I will visit often as I share your desire to be as productive as possible….eventho, I type way too much.  :)

  29. Elizabeth 16 years ago

    Dave:
    Although I am very green to project management/scheduling, it appears simple enough that I can make it work with tracking “job progress through to delivery”  I do need to skip weekends, and notice you mentioned you have formulas for that.  It’s very cool, and very kind that you share this with the world.
    Thanks

  30. Catus 16 years ago

    Dave:

    For years I’ve been using similar Excel templates to help small business project teams to “visualize” project timelines and activities without bothering them with those terms like “Gantt”, “WBS’, etc. I think the key is knowing what you’re after. I use it as a communication tool to facilitate collaboration within and outside the project teams.
    The Excel solution may not be sufficient for serious project monitoring and control. But for most of the time, it is already a good enough and easily digestible solution for most people.
    Thanks for sharing the good looking template!

  31. natezenmaster 16 years ago

    Could someone please educate me on how to create the controls that allow the expand and collapse function that is provided in this worksheet? Its the panel or pane on the leftmost as though it were columns. I’ve come to see that it just hides the lines encompassed but it doesn’t work by VBA as I checked…

    Thanks in Advance!!

  32. Chandoo 15 years ago

    Hi…

    Gantt charts are very good way to manage project activities and understand the flow. They are easy to interpret. I have built an excel sheet based gantt chart that automatically highlights based on formulas and conditional formatting etc.

    You can find that and more topics on project management using excel here: http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/

  33. Wendy 15 years ago

    I’ve worked to make something like this a dozen times and gave up each time (ending up manually creating a new sheet). I am thrilled to have this! Thank you…I know it will be useful for many projects to come…

  34. Robert 15 years ago

    There’s few typos on the sample sheet in the .xls version at line 39.

    To print, make a selection of hat part you’d lik to see pirnted, and when you go o the

    Should be…

    To print, make a selection of that part you’d like to see printed, and when you go to the

  35. Monique 15 years ago

    Thanks so much for making this available.  i am a newbie to Project Management and this should help me not only manage my tasks but also create a project for an upcoming company move. 

    Thanks again Dave and to all who contributed info and links!

  36. Michael Küttner 15 years ago

    Dave,

    thanks for this easy-to-use tool. But when I try to change the starting dates,it does not work, neither by entering the new starting point nor by using date function. What do I wrong?

    Thanks in advance

    Michael

  37. Greg Hendrickson 14 years ago

    Today I found your Gantt Graph Paper for Manual Gantt Charting in Excel.

    Very pragmatic and useful tool for pulling projects together, the easiest I’ve found so far.  Thanks for putting an easy and well-thought out tool together.  I’m looking forward to checking out the rest of your work.

    Thanks again, Dave.

  38. enrique 14 years ago

    I simply love your work. It’s a great source of inspiration. Keep hard working. Thanks from spain.

  39. Kristi Mendez 14 years ago

    David –

    I just downloaded your template. OUTSTANDING! Thank you so much. I’m already incredibly more organized. Thanks again for sharing with all of us.

  40. dr krahn 14 years ago

    Our industry works 24/7, so need to use weekends. I can’t seem to unlock the Sat/Sun cells so I can fill them with the appropriate color. How do I do that? Otherwise, looks very good for my purposes.

  41. MrH 14 years ago

    I would also like to unlock the weekend cells but my lack of excel knowledge is hampering me.

    Thanks for all the resources you have made available!!

  42. Catherine 14 years ago

    Hi Dave

    Three years on and this is still a very useful tool. There is no project manager software at the small business I’ve just started working at, and I needed something to do the yearly planning on. This will work great for that, as well as individual projects – and it something everyone on the team can easily access, without the business having to purchase expensive software then train people on how to use effectively.

    ….don’t suppose you have anything like Visio under your hat for the process maps I want do there?

  43. Patrick 14 years ago

    The legs on this post just go on and on! Dave, I just found your site by looking for GANTT help. I like the way you think and express yourself. Keep up the excellent work!

  44. Patrick 14 years ago

    How does the sheet automatically generate shading as at Lines 8 and 11?

    Apparently I’m the only one with this problem. Thanks.

  45. Mark 14 years ago

    For all those looking at removing the weekend shading (in Excel 2003):

    1. Highlight the weekend rows
    2. Format > Conditional Formatting
    3. Delete the condition (condition 1) and Ok

    If you simply reset the conditioning to another color, you will still be unable to change it with the color palette, this way gives a lot more flexibility.

  46. Barbara 14 years ago

    Hello,

    I love this! it appeals to my visually oriented mind and is so much better for working than just making lists.

    One question: when I change the start date in the downloaded spreadsheet, the table doesn’t change (I’m working in Apple’s “Numbers”). How can I fix it?

    Thanks! Barbara

  47. Author
    Dave Seah 14 years ago

    Mark: Thanks for posting the instructions!

    Barbara: I haven’t looked at this in Numbers, as I don’t use a Mac very often. I’m afraid for now, this is a Microsoft Excel-only spreadsheet. You might try using one of the web-based services instead that provide Gantt chart planning to get the same visual benefit! Several people above have commented (or promoted) their own solutions.

  48. Oleanda 14 years ago

    I don’t understand how the dates in row six work, as they don’t actually correspond to the actual dates/days in the month. Any help, please?

  49. Author
    Dave Seah 14 years ago

    Oleanda: If you’re looking at the screenshot at the top of this post, the dates on row six as Monday the 27, Tuesday the 28th, Wednesday the 30th, Friday the 31st, then a new month AUGUST begins, Saturday the 1st, and so forth.

  50. Oleanda 14 years ago

    Hi David,

    Thank you for replying. I think I was having a bit of a ‘duh’ moment, as I can see that the date uses rows 5 and 6 – vertically! I really didn’t see this before!

    Thanks again :o)

  51. Carlos 13 years ago

    Hi,

    Thanks for sharing this. Regarding the video, what application did you use to capture your computer screen? I really like how you utilized the zoom to focus on key areas of the screen.

    Cheers,

    C

  52. cathy 13 years ago

    Hey David,

    Used this gantt chart for an assignment, and it was so easy and great to use. I’m using it again on another assignment, but I need to block the dates into week 1, week 2 etc rather than single days. Can you offer any help? I’m a dunce at excel, so that doesn’t help. I’ll try and work it out, but if there’s an easy solution, that’ll be great if you can help?

    Thanks again for the template,

    c

  53. Joshua Roe 13 years ago

    Dave, Same as Cathy on 15 May, I’m trying to figure out how to manipulate your .xls file to display weeks instead of days. Help with this please? Many thanks for this great template! Joshua

  54. Author
    Dave Seah 13 years ago

    Carlos: Thanks! I’m using Camtasia Studio 7 on Windows to do the screen capture and zooming. It’s a pretty decent package, though a little pricey.

    Cathy, Joshua: I’ll see what I can do; I did something similar recently for another spreadsheet.

  55. Author
    Dave Seah 13 years ago

    Ok, I just updated the post with the new downloadable Weekly version. Check at the very bottom of the page! Feel free to leave a donation if it’s helped you out :)

  56. Thelma 13 years ago

    Hi Dave, Love this straightforward and simple Gantt Chart. Need help figuring out how to extend the columns though. Been trying to figure out how to but for the life of me I wanna bang my head against the wall in frustration! LOL. Help? Thank you so much!

  57. Author
    Dave Seah 13 years ago

    Thelma: Glad you like it! Watch the video; it shows you how to extend the columns.

  58. Steve 13 years ago

    Awesome spreadsheet.

    I would find it really handy if there was a 2 year version.

    Will play with your formulas and see what I can do.

    The automatic dates are nice!

    :-)

    • Author
      Dave Seah 13 years ago

      Watch the video! It will show you how to extend the range to whatever you want :)

  59. AK Helmer 13 years ago

    Hi David, great great gantt but please help me with row 5 and 6 – what are the numbers? Can I get them to show dates? Thanks so much

    • Author
      Dave Seah 13 years ago

      AK Helmer: Lines 5-6 are the date, stacked vertically. First day in the graphic is Monday the 27th. You’re not the only person who’s asked, so I’ve added this tip to the article as well to help!

  60. AK Helmer 13 years ago

    Thank you so much Dave, you’re a star! AK

  61. Elaine 13 years ago

    This looks great! However, whenever I try to change the date the numbers don’t update and I just get rows and rows of ‘d’s…anyone know why this is and how to solve it? Thank you!

    • Author
      Dave Seah 13 years ago

      Elaine: I have heard of a similar case with a different spreadsheet (the compact calendar) when the user was using a Turkish version of Windows. There was something about that particular version of Excel that didn’t calculate the text functions correctly. I haven’t heard of this for the Gantt chart, though. Tell me what version of Excel and Windows you’re using, and if you are using anything different from the English settings on Windows.

    • Elaine 13 years ago

      Wow…quick response! I’m using Windows XP and Excel 2007 – the ‘Turkish’ aspect is interesting since I live in Italy at the moment so some of the software is configured to be Italian language (although the drop downs, etc. in Excel display in English, the date displays in Italian language).

      Love all your productivity stuff btw! I was looking for a ‘visual’ diary that showed my deadlines kind of like a ‘BeeDocs’ timeline – your Excel sheet looks perfect.

    • Pavel 13 years ago

      Hi, David.

      I have a same problem. Mac OS 10.6.8 Format region – Russia Microsoft Office for Mac 2011 v.14.1.2

      When I change format region to United States. Everything is working.

      Could you please advice? I wanna use your great product with Russia region. Thanks!

    • Author
      Dave Seah 13 years ago

      I’ve just uploaded v4, which I think will fix the problem!

  62. Anil 13 years ago

    Please provide a monthly version too.

    Thanks

    Anil

  63. Missy 13 years ago

    Hi Dave,

    I love this tool! I started using the weekly version to track HR projects. I am having trouble adding columns to the right. I tried the method in your video (highlighting the whole column and dragging the plus sign to the right) and it just copies over the same dates. I am using Excel 2007. Any ideas?

    Thanks!

  64. Author
    Dave Seah 13 years ago

    Missy:

    Yay! Glad you like it! I’m using Excel 2007 as well, and it’s working for me, so I suspect you didn’t highlight the entire column. Try clicking the column label (it is column “BE” on my sheet), which will highlight the entire column at once. If you are dragging a selection, you may have missed the blank cell above (there is actually an invisible date in there).

    Let me know if it works. Good luck!

    • Missy 13 years ago

      Thank you. I got it to work. For some reason, I had the spreadsheet set to manually calculate so once I fixed that, it worked. Thanks again!

  65. Jeffrey 12 years ago

    Hi Dave, thank you very much for this wonderful work! I struggled for a long time with Microsoft Project and similar app, but had no time to figure out how to do this in Excel. And really appreciated your effort to put up all those explanations too (one of those boring documentation jobs).

  66. Chris Williams 12 years ago

    Hi Dave –

    I just discovered your site here, and find your calendar mapping tools very helpful…thanks for sharing them with all of us!

    I have a question related to the Gannt Weekly Chart. I’m trying to use it for both 2012 and 2013, but can’t figure out the formula that would allow your 2012 sample chart to extend seamlessly into 2013. Can you provide me some advice on how to do this?

    • Chris
  67. Oyunu Owilli 12 years ago

    I have been struggling with other programmes to organise my self but honestly this is a good tool. Thanks Dave

  68. Dennis Krahn 12 years ago

    David Great package. Our industry runs 24/7. How can I modify your progamme to show Sat/Sun as work days same as M-F? Yours, Dennis

  69. Author
    Dave Seah 12 years ago

    Chris: You just need to extend the range. Watch the video.

    Dennis: You need to disable the conditional formatting for the highlighting of the cells. The video should explain how to do that.

  70. Author
    Dave Seah 12 years ago

    Ooops, I actually put the conditional formatting instructions in the actual blog post, not the video. This got added a few months ago when someone else asked me the same question.

  71. Cindy 12 years ago

    Hi: I tried extending the date range, but was only partially successful. I was able to extend it all the way out to IV, but it doesn’t allow me to go further. Is there a limit to how far out I can extend the date range?

  72. Tobywan 12 years ago

    Great spreadsheet – thanks!

    Any suggestions on tracking whether tasks have been completed or not? I was thinking of perhaps marking them through with an x if they are completed. If they aren’t then perhaps highlighting them in a different colour or just moving them to the new date.

  73. Dara 12 years ago

    Hi Dave,

    I was wondering if you could tell me how to change the time line on your spreadsheet to Years/Months from month/weeks? It would be greatly useful for a project I am working on!

  74. Richard 10 years ago

    David

    Thank you so much for the Manual Gantt Charting in Excel spreadsheets which are very useful. I am not an Excel wizard but use the spreadsheets a tools for a PM job and as a replacement for MS Project on a corporate system where that tool is not available. As with DARA’s comments above I would like to know how to change the timeline to to Years/Months from Month/Weeks. Please can you advise?

  75. Author
    Dave Seah 10 years ago

    Extending the Date Range: There is a limit in size to the Excel Spreadsheet itself. You probably want to duplicate the worksheet (see the tabs at the bottom) and have a different range of dates on each one.

    Task Completion: Adding a column on the left with an X might work well, or just striking them out. You might also want to “freeze panes” by selecting the first column with dates (column F) then going to the VIEW menu and selecting FREEZE PANES. That way, when you scroll, you can always see the list of tasks.

  76. Author
    Dave Seah 10 years ago

    Dara, Richard

    The download archive has a file called “Sample Gantt Weekly” which is the month/week version of the spreadsheet.

    There is no way to change the formatting on-the-fly, though, on the same data. You will have to use actual project management software if you want that kind of flexibility, I’m afraid!

  77. alex 9 years ago

    Hi Dave, the weekly Manual Gantt Charting in Excel is excellent. How can I get a version of this running vertical rather then horizontal – I’ve tried to transpose it but lose the shaded weekends which is the feature I really want to keep.

  78. alex 9 years ago

    Sorry, I meant the daily gantt chart!