◄ Return to the Gantt Chart Template Page
One of the most common requests that I get from users of my Gantt Chart template is the ability to switch the view between daily, weekly, and monthly. The new version of the template provides a solution that is very easy to use. All you do is select the view you want from a drop-down box.
Check out the images below to see the new view-control features:
Version 2.3 for Excel 2003 (.xls)
Version 2.3 for Excel 2010 (.xlsx)
Note: Both files (.xls and .xlsx) will work in Excel 2007+. When using Excel 2007, 2010, or 2013, the .xls file will open in Compatibility Mode.
New Color-Coding Feature in the XLSX Version
The main thing that the XLSX version adds that is not possible to do with Excel 2003 (without adding VBA) is automated color-coding of the bars of the gantt chart.
You will see in the above screenshots that I've added a "Color" column where you can enter a color code to choose the color for the bar ("r" = red, "g" = green, etc.).
By using a formula in the Color column, you can automatically color code based on project lead and/or urgency. Example formulas for doing that are included in the spreadsheet.
Please give feedback regarding this new version of Gantt Chart Template Pro. After all, feedback is what led to these new features in the first place.
Update 2/12/2014: In response to a question about the Google Sheets version of Gantt Chart Template Pro, I have posted a screenshot below. This link to the Google Sheets version is included as a bonus with the purchase.
I used to work with some previous version products. I like its so much for studying
I d/l’d the Gantt Chart Template (*xslx) for Excel 2010 this morning, but how do I get the drop down to change it for “days” as opposed to “weeks”? Thanks!
Are you using the correct file? The one named gantt-chart_2-3-beta.xlsx? The file gantt-chart.xlsx is still the older version and does not have the drop-down box. Please email me using the email on my contact page (https://www.vertex42.com/about.html) if you need additional help – I will also need more details about the problem. Thanks.
Will I be able to use this program with MAC… I currently use Microsoft for MAC – Excel, Word etc.
Can someone advise. Thanks.
@Sophia … Yes, it works in Excel for the Mac. There is no VBA in the gantt chart templates.
I see the videos. Beautiful tool.
Some question before to buy.
How I can represent if an activity are in late in respect for the beginning plan (es. in microsoft projec I can divide an activity on the same line)
In the predecessor column, how can I do to say that an activity begins after “n” days after or before another one?
Is it possible to plan in hour insted of days, telling to the programm that one day is 8/10 hours?
Thank you in advance.
1) If the current date, represented by the red line is after the end date of a task that is not complete, that should tell you something. There is nothing built into the spreadsheet to show what the original end date was intended to be if you decide to change the duration or the end date. So, it doesn’t work like MS Project in that regard.
2) Use =enddate+n for the formula in the Start Date column where enddate is a reference to a cell in the End Date column for another task and n is the number of days. You can also use =WORKDAY(enddate,n) if you want to avoid including weekends. I think that is in the video and help content.
3) Not with the template as it currently is, no. But, you could add a new column and label it Hours and then use a calculation for the duration column if you wanted to enter hours rather than days. But, the gantt chart still only works based on number of days, not hours.
I saw an example under your Gantt Chart Template that uses times
Start 7:00 AM End 7:15 AM. There is a statement to ‘Make sure the Time durations are multiples of the minimum time interval’. So it appears it can be done, but I cannot find where to go to set it up.
I need to schedule the manufacturing of multiple products on multiple production lines. If I can set it up like the example in your demo, that would work!
Thanks for your assistance!
@Linda, There is a bonus file included with Gantt Chart Template Pro that is designed to work with times instead of days (it’s a completely separate file). If you still have questions after finding that file, you can email me.
Is there a version for Excel 2007?
@GBowden: Yes, both the XLS and XLSX file should work in Excel 2007. I just updated the article because it was written originally when version 2.3 was still in beta.
hi, there seems to be a limitation on the number of date columns that can be scrolled (it goes from column I to IP). is this an Excel limitation or of the tool?
Alvin, In Excel 2003 the column limit is the issue. In Excel 2007+ more columns can be added, but the more you add the more formulas Excel must handle and it can slow down the calculation speed. See Increasing the number of columns in the Gantt Chart in Excel 2007+.
Hi Jon, on the free template (ver. 2.3.1), I noticed that the scroller on row 8 displays only rows L to IO for a total of around 238 columns. Is there a way to extend this, as this version of Excel goes well beyond row IO?
Alvin, The column limit is based on Excel 2003, for which the original gantt chart template was created. Excel 2007+ allows more columns, so more columns can be added to the pro version.
I brougth the pro version and it said “You can enter a formula in the Color column….”
I want to apply the following condition for color coding, but its failed as I cant find the color column.
The example below makes the 10-day warning orange and a 2-day warning red.
= IF(end_date < TODAY() + 2,"r",IF(end_date < TODAY() + 10,"o",""))
Please show me WHERE should I place the formula, thanks.
Please kindly reply
@Tom: Column N in the .xlsx version of the template.
I couldn’t get the Gantt Chart Template (*xslx) for Excel 2010 this morning, but how do I get the drop down to change it for “days” as opposed to “weeks”? Thanks!
@Giji, Only the Pro version has the drop-down box feature to change between days, weeks, and months.
I bought your gantt chart and I am having a problem with the color coding. I like the way it goes from red if late orange if you are working on it and yellow if you should be thinking about it. How can I make green after the yellow, I have tried to add to the formula but it does not work
=IF(H31<$G$8 + INDEX(urgency,1),"r",IF(H31<$G$8 + INDEX(urgency,2),"o",IF(H31<$G$8 + INDEX(urgency,3),"y",IF(H31<$G$8 + INDEX(urgency,4),"g",""))))
Is there someway to create the Index urgency, 4
Once I am say a month out I would not care if it stays blue. The only way I have found to turn it green is to type the "g" in the color column
@Tracy … To add a 4th option for color-coding urgency, you’ll need to update the Named range “urgency” to include the new cell. Currently, it refers to the range E161:E163 in the Help worksheet, but you could extend it to E164.
This is a great tool. Do you have a capacity planning template?
@Jim … I’m afraid I don’t know enough about the topic to create a capacity planning template.
Is there a way in the pro version to customize it for a different language?
So it becomes danish weekdays etc.
@Jimmy … yes, but not automatically. Date formats can be modified via the traditional methods in Excel. There isn’t anything that will automatically translate all of the text.
How do you print the entire range of a chart that is a very long duration and requires use of the (custom) horizontal scroll bar to view in its entirety?
@Brandon, If you are using Excel 2007 or later, you could add more columns to the right end of the chart and then increase the print area. Or, you can change the view to Weekly or Monthly to condense the view.
Within your excel / google sheet Gaant chart diagrams, do any of them show dependancy lines between the tasks? i.e.
If I use columns D->F to define dependancies, does this appear within the Gaant chart at all?
Also, do you have a google sheets demo sheet? I am presenting your solution to the financial accountants for Woolworths Australia, we are merging away from Microsoft Office products and are mostly interested in your google sheets solution.
None of my gantt charts show dependency lines, but you can unhide the Dependency columns and use those to define tasks. Or, you can insert columns and list dependencies for purposes of displaying them on printouts.
I do have a Gantt Chart Template for Google Sheets, but not one I can share point to on this blog. I include the link to it as a bonus with the purchase of Gantt Chart Template Pro, because it does pretty much what the XLSX version does – except that it only has a daily view. I will add a screenshot to this blog post as well as on the main gantt chart template page.
I am using your free version as a trial, and am considering purchasing the Pro version. I attempted to delete some of the sub-task rows, and it affected the formulas of several cells. Does this happen in the Pro Version as well? If so, how do recommend one go about correcting this issue? Thanks.
@Tito … you should be able to delete rows in the gantt chart without affecting other rows … UNLESS the other tasks were referring to the task you deleted as a predecessor. So, if you delete a task and that causes errors in other rows, then just know that you probably deleted a predecessor, and you’ll need to correct the inputs in the row that was referring to the one you deleted.
HI, I am trying to make my purchase for the Gantt file, but I can´t move on after I click the Purchase now button.
@Susana, Some people have reported that their company has a firewall blocking clickbank.net or clickbank.com. Unfortunately, I don’t have any other payment systems in place at this time. You could see if your company would reimburse a purchase made from a home computer.
Please send us the Demo of latesr Excel Gantt Chart. and also provide the prise for the pro Version
@Bhavesh, the download of the free version as well as the price of the Pro version can be found on the following page: https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html
I would like to ask you, how can I get the file Version 2.3 for Excel 2003 (.xls).
Also I need per hours also, can you give more information about these files?
How can I get them.
Thank you very much.
@Ruslan, If you have purchased the Pro version and have not been able to download the files, please send me an email and include your order number in the email.
Any chance you could provide me with the instructions in order to create a gantt template with the switchin views option (periods) and of course I could pay you for the instruction.
@Gerardo, We don’t provide that type of instruction. We provide templates so that you don’t have to create stuff like that from scratch.
I like these template!
Could we have such Gantt Chart in Hourly Basis pls.
@Ghamdi and @Misty, Many people have requested a version that supports an hourly basis. I’m working on a solution for that, but it is complicated by the fact that NETWORKDAYS and WORKDAY functions are based only on days, not hours.
Hi, I have downloaded the free template, and think it is very good. One question I have – I have more than 9 main tasks, however if I enter in a number with 2 digits the colour formatting disappears on the main task line. Where do I go to change the main task number of characters from one to two (or even three) characters. Thank you
@Bronwyn, You will need to send me a copy of your file via email for me to diagnose your issue. I don’t understand where you are entering the number you mention.
Do u have formulated Gantt chart in excel that reflects the schedule for day shift only
Ex: say people work only on days either 8hrs[between 7AM to 4PM+1hour lunch break]. if the total work duration is 10hrs will end time and date change to next working day.
@Suheb, I don’t have any gantt chart templates that are based on shifts or handle different numbers of hours in a day. If you wanted to enter hours instead of days, and round to the nearest number of days, you could insert a new column for entering hours and then use a formula like =ROUND(hours/10,0) or =ROUND(hours/8,0) for the Work Days or Calendar Days columns (depending on which one you are using as an input). Although I’ve created a gantt chart that handles times, it’s just too complicated to put out there for general use.
I am working on a project that is going 6 days a week, how do I change to a 6 day work week? and 7 days if a future project does such a schedule?
@Chuck, The Pro version files gantt-chart_intl.xlsx and gantt-chart_o365.xlsx allow you to define the work week to be whatever days you want. These versions use the WORKDAY.INTL() and NETWORKDAYS.INTL() functions that are only available in Excel 2010 or later. The Help worksheets explain how to define the work week.