A long time ago, I offhandedly wrote that I used to make Gantt Charts in Excel to help visualize project flow. I’ve never uploaded these files because I didn’t think they were that exciting. I’m basically just using Excel like graph paper, and there is absolutely no automatic calculation at all. On the other hand, it’s probably a lot EASIER to keep up to date, through copying/pasting and inline annotation, than actual software like Microsoft Project.
Enough people have asked, however, that I’m finally releasing a couple of examples of how I put these Excel-based Gantt charts together.
Version 2 (2009)
This is a version that will calculate the shading for you automatically and mark the months based on the input of a starting date. This version is also set up to print gridlines, which makes it useful for printing out “gantt paper” to sketch on.
Download the dseah-gantt-excel2.zip archive to your computer and uncompress it. The resulting folder will contain two files:
- The
.xlsxfile, for Microsoft Office 2007, with nice shading. - The
.xlsfile, for older versions of Excel
There are instructions on its use on the first worksheet. Note that there is an example worksheet included; check the document tabs at the bottom.
Version 1 (2007)
This is the older version, which does not automatically shade the weekends or recalculate based on the input date.
When you download and uncompress the dseah-gantt-excel.zip archive, you’ll see two files:
- The
.xlsxfile, for Microsoft Office 2007, with fancy shading. - The regular old
.xlsfile, for older versions of Excel.
The color schemes are slightly different in each file, but you should get the idea. Again, you will not find anything earth-shattering in here, but you might have fun playing around with the formatting.
If you are doing rough scheduling at a meeting, you can use the Compact Calendar, which is helpful for visualizing future time as one resource block. Unlike a Gantt chart, however, it does not show dependencies between multiple project sub-tasks.
Enjoy!
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,
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.