Gantt Chart
Posted on July 25, 2009
Filed Under Other |
Gantt chart is a graphical representation of the duration of the tasks against the progression of time where it usually represent a project schedule and achievement. Some people are creating Gantt chart in Microsoft Excel to simplify their project, but with lack of ready-made duration calculation, the Gantt chart created in Excel usually only being used to just show time progress only. That is one reason I created my own Gantt chart. I need a Gantt chart that can be adjusted to represent a project with 5,6 or 7 days duration per week. And it is flexible enough to adjust the duration by entering end date information or just week duration. And I didn’t found that kind of Excel Gantt chart in internet. People just suggested to use Microsoft Office Project for that purposes.
I finished this Gantt chart a month ago, but I don’t feel that this Gantt chart is finished. I think I will still do some refinement in the next version.
It is a single worksheet template with guidance to use it are as follows :
1. Fill your project name in project name row
2. Fill your project description in project description row
3. Fill your project start date in start date row
4. Select your project period reference by ticking end date or number of weeks row. And fill the needed information in the row where you tick your reference. The blue cell is shown up to remind you that this cell doesn’t need to be filled because it contain formula that will calculate the projected end date or number of weeks based on your input.
5. Select your working days. There are three options available there, Monday - Friday (5 days), Monday - Saturday (6 days) and Monday - Sunday (7 days). This information needed to calculate your project week duration (WD), days completed (DC) and days remain (DR). And it will be used also to give you the correct holidays mark inside the chart.
6. Activate/Deactivate today’s marker by selecting yes or no in the today’s marker cell. If you activate today’s marker, you will see a blue color in today’s date cell inside the chart.
7. Activate/Deactivate holiday’s marker by selecting yes or no in the holiday’s marker cell. This is not a national holiday marker, this is just holiday marker based on your working days preference.
After finishing step 1 to 7, basically you already setup your Gantt chart. The next step is filled the chart with your project detail. In the chart, just fill Level, Task, PIC, Start Date and Finish Date column. The remaining column will be filled by itself. I put sample project in the template to ease you understand my Gantt chart. And this Gantt chart is full of conditional formatting format to adjust the layout automatically. Feel free to do some experiment with this template to see the result based on your input. There are three type of Gantt chart I created for you to choose.
Gantt Chart with date information. You will see that there is a date on top of each column inside the Gantt chart. You can download the file here.
Gantt Chart Weekly. This Gantt chart hides the date information, so the chart will be shorter. You can download the file here.
Excel 2007 Gantt Chart. I add more conditional formatting format in this template to make it more nice. I created this version because there are limitation in excel version before 2007 to only can have maximum 3 conditional format. You can download the file here.
So, it’s all up to you to choose and modify. And please give me feedback if you found some bugs or need additional features inside.
You can read and download my other templates below :
- Biorythm Chart
- Travel Planner
- Shift Schedules
- Grocery List
- Home Inventory
- Latitude Longitude Calculator
Comments
23 Responses to “Gantt Chart”
Leave a Reply
I downloaded your Gantt chart, and I just want to say thank you! I just populated my information, and it looks great!
One tiny recommendation is if there was a way for the Task Start/End date to be driven off the subtasks, that would help the template — but I certainly don’t mind filling it in manually. Just thought I would share.
Thanks again for sharing all the time you put into this. We certainly do appreciate it!
Came across your site as I was needing a gantt chart just like this one. This thing is awesome! I’ve been trying to follow the formulas and find a few querks. couple things if you don’t mind. First, I have a hard time conditionally formatting (want to change the color scheme) by copying cells and going through the format tab… I also couldn’t figure out the number of weeks option. No matter the number I input, it pushes the weeks past my end date by the exact amount of weeks input. I’m going to play around with a little more today. I am amazed that you did not use any visual basic to accomplish. I’ve been racking my brain on developing code. Thanks. Jonathan Hill
Can you send me an unlocked version of the weekly gantt chart? I need to delete some rows and was unable to since it was protected
Hi,
I downloaded your weekly Gantt chart tool and I loved it. However, the WD, DC and DR don’t seem to auto-populate. Any idea why?
Thanks so much!
CC
This was an excellent tool for me to quickly organize a fairly sizable project. Being a new manager, it allowed me to save some significant design time and provide exactly what I and my team needed.
Excellent work!!!
Thanks!
CJ
How can I edit chart to accommodate 12 or 24 month time period? Drop down only allows 32 weeks. Please advise, thanks!
Greg
Thanks for this template! This is really helpful.
Great tool, Can you provide me with unprotect password? I need to change the gray cells to other colors to highlight different projects.
Also is there anyway to extend the time period beyond 32 weeks to 12 months or 18 months?
A quick response would be greatly appreciated.
Daron
Hi and thx for this great chart, using the weekly chart.
however it seems not to work on my side with the starting date of the week once i enter a new start date it shows only 0/d/yy, the rest works great but i really need it to show the start date. i guess it has to do with the different date-formats you use and my excel is set up (regional settings?).
any help would be greatly appreciated.
Gantt chart is great. However WD, DC and DR don’t seem to auto-populate and have a #NAME error in Excel 2003.
Any idea why?
As per above
“Gantt chart is great. However WD, DC and DR don’t seem to auto-populate and have a #NAME error in Excel 2003.”
The error is only evident when working days is Monday-Friday or Monday-Saturday.
Monday-Sunday, the WD, DC and DR calculate correctly.
Any idea why?
I have to agree with Matt (on January 18th, 2010 12:12 am). Only the Monday-Sunday option produces correct values for WD, DC and DR; the two other options produce a “#NAME?” error.
Apart from that, it is an excellent tool.
How is the tool to be used for projects lasting (for example), one year?
Thanks.
@Daron : You can unprotect it without password.
@Greg : This gantt chart design for maximum 32 weeks because of excel 2003 column limitation, and I haven’t add new week columns in excel 2007 version. I will add it later.
@Matt and Martin : You should activate analysis toolpak add-ins. Go to excel menu > tools > add-ins and tick the analysis toolpak. And all function should work correctly. If you still have a problem after following these steps, just close your excel and open it again.
Regards
@R. Musadya
Thanks a lot, that works.
What is the best way to insert additional fields and project headings without ruining the formulas?
Great chart, two things that for me inprove it visually. The weekly columns are offset by one row throwing off the gantt chart markers. Plus I would love to change the marker color for different vendors on a project, how can I do that?
Thanks again great chart.
Ron
Great chart but need more than 32 weeks. Can you advise how to do that? Also what does WD, DC and DR stand for? Thankyou!
Thank you for the great chart!
I downloaded the Excel 2001 file. It unzipped into a few file folders. What do I do now? How do I get to use it?
Great chart, Is there any way I can change the colours for each person on the chart? Also I cannot see how to put in holidays even though it has a tab for show holidays.
Thanks again great chart.
Good afternoon, I have a little question about the acronym “PIC”. What does it stand for? Sorry for the language barrier.
Person in command?
its like the OPI
A perfect solution for me. Ammended to fit corporate colours, now it’s very easy to amend and drop into powerpoint for presentations. Thanks a million. Great work.
your a life saver! the down loaded version has got me out of a biind, but i would be greatful if you could email me a copy of a chart that accomidates 12 or 24 month time period? Drop down only allows 32 weeks. thanks!
thanks very helpfull…