Manual Gantt Charting in Excel (Discussion)
Posted on August 13, 2007 in Productivity
(last edited on April 5, 2019 at 1:50 pm)
(last edited on April 5, 2019 at 1:50 pm)
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
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,
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
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.
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.
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.
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?
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.
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? ;)
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!
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!
Dave/Mark:
Can you please email me the automated excel sheet with formula’s as well?
Appreciate your help
regards
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.
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.
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.
Hi Guys Someone please also e-mail me that fantastic automated Gantt chart spreadsheet Mark made?
Thanks in Advance!
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.)
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.
Kent: Awesome! Thanks for doing that! Looks great!
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?
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.
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!
Thanks Mark.
I will analyse this today evening from home :)
Appreciate your help
Neal
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!
Mark: Thank you for posting! That is definitely a good starting point.
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.
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/
And here it is the U.S. version:
http://unprojectmanager.wordpress.com/2007/12/10/gantt-year-2008-using-excel-2003/
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
There’s one web based and free gantt chart application I really liked http://www.yutiti.com
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. :)
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
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!
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!!
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/
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…
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
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!
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
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.
I simply love your work. It’s a great source of inspiration. Keep hard working. Thanks from spain.
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.
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.
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!!
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?
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!
How does the sheet automatically generate shading as at Lines 8 and 11?
Apparently I’m the only one with this problem. Thanks.
For all those looking at removing the weekend shading (in Excel 2003):
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.
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
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.
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?
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.
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)
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
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
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
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.
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 :)
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!
Thelma: Glad you like it! Watch the video; it shows you how to extend the columns.
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!
:-)
Watch the video! It will show you how to extend the range to whatever you want :)
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
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!
Thank you so much Dave, you’re a star! AK
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!
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.
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.
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!
I’ve just uploaded v4, which I think will fix the problem!
Please provide a monthly version too.
Thanks
Anil
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!
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!
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!
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).
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?
I have been struggling with other programmes to organise my self but honestly this is a good tool. Thanks Dave
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
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.
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.
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?
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.
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!
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?
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.
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!
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.
Sorry, I meant the daily gantt chart!