Excel Templates | Excel Spreadsheets | Gantt Chart


Free Excel Templates

Gantt Chart

Posted on July 25, 2009
Filed Under Other | 70 Comments



Share

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.

Gantt Chart

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 Weekly

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.

Excel 207 Gantt Chart

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 :

Tags: , , , , , , ,

Comments

70 Responses to “Gantt Chart”

  1. Heather Weast on August 11th, 2009 8:54 pm

    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!

  2. Jonathan Hill on September 18th, 2009 9:42 pm

    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

  3. Guarino on November 11th, 2009 8:15 pm

    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

  4. BK on November 12th, 2009 12:29 am

    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

  5. Carlton Johnson on December 24th, 2009 4:45 pm

    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

  6. Greg on January 4th, 2010 10:31 pm

    How can I edit chart to accommodate 12 or 24 month time period? Drop down only allows 32 weeks. Please advise, thanks!

    Greg

  7. joy on January 5th, 2010 1:57 am

    Thanks for this template! This is really helpful.

  8. daron on January 6th, 2010 3:59 pm

    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

  9. Gianluca on January 7th, 2010 10:36 am

    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.

  10. Matt on January 17th, 2010 11:17 pm

    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?

  11. Matt on January 18th, 2010 12:12 am

    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?

  12. Martin on January 18th, 2010 3:24 pm

    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.

  13. R. Musadya on January 19th, 2010 3:20 am

    @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

  14. Matt on January 20th, 2010 1:59 am

    @R. Musadya

    Thanks a lot, that works.
    What is the best way to insert additional fields and project headings without ruining the formulas?

  15. Ron on January 20th, 2010 6:56 pm

    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

  16. Michelle on January 31st, 2010 11:15 am

    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!

  17. Ari on February 1st, 2010 10:05 pm

    Thank you for the great chart!

  18. JGG on February 25th, 2010 10:11 pm

    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?

  19. Claire on March 2nd, 2010 10:28 am

    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.

  20. martin on March 2nd, 2010 8:22 pm

    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

  21. Patrick on March 9th, 2010 10:52 am

    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.

  22. Trent Baxter on March 9th, 2010 11:47 am

    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!

  23. Amar on March 12th, 2010 11:59 am

    thanks very helpfull…

  24. Iain Munro on March 19th, 2010 1:11 am

    Hi There

    Great tool.

    It would be good to be able to do at least 52 weeks. In addition to that, the start and end date are ok, but generally you have two things with a project – the end date that the customer wants the project and the other is the hours to complete the project which could be more than the allotted time, in which case you have to hire more people.

    Is there anyway to accomodate this into your spreadsheet?

    Iain

  25. Ajay on March 20th, 2010 6:32 pm

    THanks a lot your template was educational and saved lots of time. God bless.

  26. Ed. on March 25th, 2010 8:30 pm

    Great stuff! Loved the Gantt. Can’t wait to try the 2007 version. Keep it up and God Bless You!

  27. Moksh on March 31st, 2010 10:30 am

    Very Nice Template!
    Thank you so much!

  28. WL on April 6th, 2010 10:29 am

    great tool… but I would like to change the colors for holidays, today and the project time-line. How is this done?

  29. Bjorn on April 6th, 2010 11:05 pm

    Great template and very useful.

    To anyone asking for what WD, DC and DR stand for, I suggest to test the template yourself especially for the start and end dates for each task level. You will understand quickly

    WD = Working Days
    DC = Days Completed
    DR = Days Remaining

    Cheers!

  30. Ahmed on April 13th, 2010 1:18 pm

    Very good template. I would like to know how to change Working Days from Sat to Thu?

  31. Pitso on April 14th, 2010 3:01 pm

    Hi Great template, Covers exactly everything i was looking for and its easy to follow.

    Is there any way I can change the colours for each person on the chart?

    Plus I need to change the gray cells to other colors to highlight different projects.

    Thanks again great chart.

  32. Steve Hateley on April 19th, 2010 5:56 pm

    Just downloaded and unzipped… so how do you import/use the template? Which file do I point Excel at ?

    Many thanks

    Steve

  33. Steve Hateley on April 19th, 2010 5:56 pm

    …sorry..that was the Gantt chart zip…

  34. G.Srikanth on April 27th, 2010 10:18 pm

    Hi Great template, Covers exactly everything i was looking for and its easy to follow.

    Is there any way I can change the colours for each person on the chart? Plus I would like to change the color of the Cells to other colors to highlight different projects.

    Thanks again great chart.

  35. Danito on June 5th, 2010 10:10 pm

    Thanks for the template – saved me hours of time.

    I cannot seem to unprotect the worksheet without a password. Please tell me how to do this or send me the password.

    Thanks

    Danito

  36. Farzan on June 8th, 2010 6:54 am

    Thanks a lot! I’m rally enjoying this template.

  37. AMy on June 8th, 2010 3:40 pm

    The link for the 2007 chart appears to be broken

  38. Dwight on July 6th, 2010 10:43 pm

    I love the chart, thanks very much. If I were to add anything, it would be a “%complete” column for each task. But again, great job.

  39. T. Ski on October 26th, 2010 6:16 pm

    Hi, Can you please send me the excel template with the 52 week option? I really love this sheet, and i was trying to modify it, but I’m coming up empty. Thanks so much and great work!

    Todd

  40. Tim on October 27th, 2010 2:18 am

    Thanks for this excellent chart – I have found it very useful. One issue, as mentioned by others – how to extend it to 52 weeks? If you have an updated template that is designed for this, I’d be very grateful to have it.

    Well done and thanks for sharing!

    Tim D. (Dublin Ireland)

  41. Peter on October 29th, 2010 1:16 am

    This is a Terrific worksheet. It’s very easy to use and offers useful options (such as work days in workweek)that are absent from other templates. The only limitation is not being able to schedule more than 32 weeks. 52 week would be great, 2, 3 or 4 yrs would be better. I know you responded by saying that it is coming soon but I just wanted to get my feedback out to you. Another improvement would be to add the ability to create a vacation list so that the days are not counted as work days.
    Thanks for sharing.

  42. Greg on November 1st, 2010 6:19 pm

    Thanks for the template it is helping tremedously. I’m having trouble adjusting the “END DATE” When I go to change it from 12/15/2010 to 12/30/2010 it says “The valu to be entered must be a date between =#REF! and =#REF!+224″ Any suggestions?

    Thanks for your time!

  43. Yuri on November 16th, 2010 1:08 pm

    Hi,
    Great Gantt template, first of all. I have searched the net for quite some time and yours is the most efficient one I find.
    I have a few suggestions though (as mentioned by other users):
    - The end date does cannot take date ‘older’ than 29-may-2011. It would be nice if any date would be possible (e.g. start date 16-11-2010 end date 16-11-2012). The funny thing is that when you select weeks and you select 32 weeks, then the end date automatically set the END DATE to 30-may-2011!

    -The amount of weeks should be more than 32. I think with the potential of your chart people will easily use it for project/tasks that takes more than 32 weeks to complete.

    -The Finish date does not accept dates ‘older’ than 32 weeks (I guess). So 29-may-2011 is the oldest. It looks like everything is pretty much limited to 32 weeks, so you probably have a variable defining this limit?

    Do you have suggestions how to (maybe) quickly modify this limit?

    Again, great Gantt chart!

  44. Jenna S. on November 18th, 2010 4:36 pm

    Hi!
    I would like to begin by expressing my appreciation for all of the hard work that you’ve put into making this! This chart is phenomenal!

    However, I would too like to know how to modify the length of the chart so that it may run longer than 32 weeks. Is there a simple way for Excel dummies to do this?

    I thank you again for this amazing template!! I would be lost without you!

  45. roddy on December 4th, 2010 1:37 pm

    I donwload the version v1.1.xls as a zip file, but when unzipped these files are not the files in xls format.

    Please let me know how to open these file in Excel format

  46. Tonya on December 28th, 2010 4:18 pm

    Is there any timeframe for putting out a 32+week version of this chart?

  47. Norman on December 30th, 2010 8:44 am

    Hi, thanks for that template, I hope we could use half worked days. Is it possible to implement ?

    How can i do this ?

  48. Theresa on January 13th, 2011 2:19 pm

    I would love if you could email me your latest version in excel 2003 for weekly only and have a maximum of more than 32 weeks, up to 52. Thanks so much!

  49. Sdavis on February 4th, 2011 9:36 pm

    Could you open the file in open office and use more than 32 weeks. I could not find how to change the setting but it appears that Open Office Calc will allow more.

  50. ARos on February 10th, 2011 11:30 am

    Great template.

    Need direction to use the 2007 zipped file.

    Please could you email me your latest version?

  51. Abhishek on February 10th, 2011 12:07 pm

    thanks :)

  52. Eric on February 11th, 2011 6:33 pm

    I tryied to download your 2007 version but all I got was a bunch of files. I didn’t get the excel version of your gantt chart. What do I do?

  53. Agatha on February 15th, 2011 12:59 pm

    Hi – thanks for this, great work! My problem seems to be – when I copy and paste from MS Project to Excel the date, 1 day disapears from teh front in the Excel spreadsheet.

    i.e. in my schedule Task A starts on the 13th Feburary. In my Excel gantt it shows a start on the 14th Feburary.

    Can you please advise? Thank you for your time.
    Agatha

  54. Yinn Lai on February 17th, 2011 3:39 pm

    You are a legend!!!!

    Covers exactly everything i was looking for and its easy to follow.

    Is there any way I can change the colours for each person on the chart?

    Plus I need to change the gray cells to other colors to highlight different projects.

    Please teach me!!!

    Thanks!

  55. Mark on March 4th, 2011 8:34 pm

    Thank you…this is incredible!!!

    I have a question, and perhpas it is there and I cannot see it. Once a task is completed, how do you identify this on the chart?

    Thanks again!

  56. Rachel on March 9th, 2011 12:05 pm

    This template is great, but can you explain how to change the colours to highlight different projects?
    Thanks !!!

  57. KC on March 10th, 2011 9:19 pm

    How do I open the zip file in Excell 2007?

  58. Gerben van Lent on March 12th, 2011 8:48 am

    Great template (first one)!!
    Two questions:
    Is it possible to change the length to more than 32 week?
    I also tried the 2007 template but after unpacking it is not clear how to work with the files
    Really good stuff

    Gerben

  59. Mai on April 24th, 2011 8:39 am

    THANKX !
    Great Template !

    How can i change the holiday marker though :) ?

  60. Jeremy on May 19th, 2011 2:09 pm

    I am getting the following error when trying to enter in a end date.

    The value you entered is invalid. A user has restricted values that can be entered into this cell.

    Can you provide any guidance?

    Thanks,

    Jeremy

  61. Pojin Weng on May 30th, 2011 2:36 am

    Hi Great template,

    It’s a good tool.

    How to change the color of the Cells to other colors to highlight different projects.

    Thanks again great chart.

  62. Wes Thornburg on June 10th, 2011 6:29 pm

    Just downloaded and unzipped… so how do you import/use the template? Which file do I point Excel at ?

    Many thanks

  63. Oseas Benavides on June 13th, 2011 8:26 pm

    Great chart!

    Very easy to fill out. Thank you very much for this hard work you put to do this and share with everybody.

    I would appreciate if you may tell me how to modify the time frame to have more than 32 weeks.

  64. Nokna on June 20th, 2011 1:31 am

    very good idear

  65. Laura on July 1st, 2011 7:09 pm

    Hi,
    This is a great tool! Best one I’ve found yet! I’m anxious to know if it was updated to accommodate more than 32 weeks.

    Thanks!

  66. Como crear una estrategia de mercadotecnia en las Redes Sociales on August 28th, 2011 6:39 am

    [...] para hacer diagramas de Gantt, en lo personal recomiendo Mind Tools, Bright Hub, Peltier Tech, Excel Template, donde hay excelentes guías, en inglés, de como desarrollar un esquema de este [...]

  67. dipuo on September 12th, 2011 11:30 am

    what a great template…

  68. sibusiso on October 26th, 2011 10:13 am

    Thank you so much. my assignment is due in the next 2 days and was stressing, guess what? its all gone. i’m submitting this afternoon & will sure nail. many thanks

  69. Boris on January 4th, 2012 8:49 am

    Hi, thanks for sharing a great job, I want to change the 3 options in Drop Down 4 Form Control wich are: Monday – Friday, Monday – Saturday and Monday – Sunday, but dataform3.xlam required to modify this Form Control in Visual Basic ask me for a password to access it. Could you give me such password?

    Thanks in advance for your help.

  70. Sheeba on January 7th, 2012 11:43 am

    Good Day!

    Your chart is simply great! It is exactly as I wanted for my project but after I downloaded it i could not see those automatically filling colour bars. Wish it had a percentage coloumn as well.

    I guess this must be a download error, appreciate if you could please email me the file.

    Please help.

    Many thanks
    Sheeba

Leave a Reply