◄ Return to the Gantt Chart Template Page
Today I created a new version of the Gantt Chart Template (Pro) designed to work in Excel Online, Excel 2010+, and Excel for iPhone/iPad. Though quite a few changes were made, most of the functionality is still there.
Screenshot of the Gantt Chart Template for Excel Online.
Completion Status: One of the main differences is that the completion status is shown as a progress bar in the % Done column rather than changing the bars in the chart area.
Customize the Work Week: This version uses the WORKDAY.INTL() and NETWORKDAYS.INTL() functions that let you define exactly which days of the week should be considered non-working days. See the Help worksheet if you want to define the work week as something other than Monday-Friday.
Highlighting the End Date: Another new feature allows you to highlight the End date red when the task is overdue. This feature can be turned on or off in the Help worksheet.
Displaying Dates in d/m/yy Format: Another option in the Help worksheet lets you switch between m/d/yy and d/m/yy display formats. Note that this is only a formatting switch. How you ENTER or EDIT dates will depend on your computer's system settings.
Weekly and Monthly views: Excel Online does not currently support vertically oriented text. It also had trouble with the number of columns that the original XLSX version used. This required a significant change to the way the chart is labeled. A consequence of this change is that the Weekly and Monthly views are less precise, meaning a week or month containing a 1-day task has a bar that is the same length as a week or month containing a 5-day task.
Choose a Daily, Weekly, or Monthly View (currently showing the Weekly view)
In the weekly view (shown above), the date shown in the column label is the date for Monday of that week. Another thing to notice in the above screenshot is that the week containing today's date (March 26th) is highlighted green (Monday is March 23rd).
If you want to change the range of dates shown in the chart area, you can change the Display Week number. For example, if the project start date is 1/5/2015, changing the Display Week number to 2 will make the display start at 1/12/2015. In the above screenshot, Display Week 8 means that chart begins with 2/23/2015. In previous versions, changing the range of dates was done using a scroll bar, but scroll bars are not supported in Online Excel.
An Alternate Version, Not a Complete Replacement
This new version is a new download option, but Pro version customers can still download the older versions if they are using older versions of Excel. What you use may depend largely on the version of Excel you and the others on your team are using.
Need help returning to the download page? Follow these instructions.
Comments
Love it. It is exactly what I need. Thanks so much.
I am looking for this excel sheet. Please let me know the Process to download this sheet.(Grant Chart pro)
Thanks,
Raja
@Raja – Just click on the “Gantt Chart Template” link at the beginning of the article to get to the download page if you are looking for the free trial. You can also purchase the pro version from that page.
can you unprotect the free trial version?
@Leslie, The download page for the pro version contains the password to unlock the free version. This does nothing other than remove worksheet protection, though. If you want the features of the pro version, you need to use those files rather than the free one.
Hi,
I just purchased this template. The int’l version is the one I was looking for to be able to start the week on Sunday. It’s not working .. I’m getting errors all over the sheet!!! (#Name?)
Please help
@Mark, The #Name? error probably means that you aren’t using Excel 2010 or later. The int’l version uses the WORKDAY.INTL() and NETWORKDAYS.INTL() functions which are only available in Excel 2010 or later.
Hi Jon,
When will this new version (3.0) bet out of Beta?
Thanks,
–YS
@Yogesh, Good question. I’ve gotten enough feedback now to say that the version for Excel Online is no longer in beta, but a lot of people are still using Excel 2003 and 2007 and this new version requires at least Excel 2010 (due to the use of the WORKDAY.INTL() and NETWORKDAYS.INTL() functions). It’s a stable spreadsheet, but I’ve still been making some changes. The latest change was to add an option for switching between m/d/yy and d/mm/yy date formats easily (via an option in the Help worksheet). It’s likely that this new version will remain labeled as a beta until I can get new demo videos created.
Hi,
I have used the O365 Gantt with great pleasure with Excel 2010. I have made the complete plan and it all seems to works. Then I open the Gantt with iPad or iPhone Excel and do my stuff. When I then open the Gantt again on Excel 2010, then it won’t update at all. The bars won’t update and I can’t change colors etc. It seems like all formulas in the sheet are disabled.
Br. Jesper
@Jesper, Very interesting. It looks like maybe a recent app update (I’m testing with Excel for iPhone) may have led to a bug. The formulas all appear to be working when edited with Excel 2010, except that the conditional formatting (which controls the bars in the gantt chart) does not update until you force a full recalculation by pressing Ctrl+Alt+F9. I will need to report this bug to Microsoft. I will also experiment to see whether this is a systemic issue or something unique to the way the conditional formatting is done in the gantt chart. Meanwhile, you can use Ctrl+Alt+F9 to update the bars in the gantt chart.
@Jesper, I found a solution to this issue via a thread at answers.microsoft.com. Here are the steps to fix the problem instead of just pressing CTRL+ALT+F9 all the time:
Source: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/conditional-formatting-not-updating-automatically/746bf300-1328-436f-87fd-47cbfe9378d0?auth=1
Hi Jon,
It works ;-)
Thank you very much.
Br Jesper
Jon,
This is very close to a solution my company has actively been looking for. Is it possible to purchase a customization which includes a summary line which contains the sub activity colors. The scheduling we do makes sure that none of them overlap (each must finish before the next can start). I would enjoy it very much if you could contact me regarding this.
Best
@David … I don’t quite follow what you mean by “sub activity colors.” You can email me (see the contact page) with more information.
Hi.. My operation does work on weekends and holidays. How do I include thos in this spreadsheet? Thank you.
@Heli, You would use the template rows that are based on Calendar Days rather than Work Days. The template rows contain different sets of inputs and formulas. The help worksheet and the video demo shows how to work with them.
HI!
I just bought the Gantt chart for our charity. Got a suggestion though, since I am doing this action now manually and was wondering if that can be done automatically:
It would be nice if there is an option that the text in the task field can be copied automatically into the bar (see attachment. That would make it so much more helpful since I am doing it now manually. Having the task in the bars gives a much quicker overview of what is coming up! :)
@Paul … thank you for the suggestion, but that wouldn’t be possible without VBA (or using a chart object for a gantt chart instead of the method used in this spreadsheet). I’ve designed my gantt chart templates specifically so that they do not require VBA.
Hi Jon, I have recently purchased the gantt chart pro to aid me on my work. Since I am working here in the middle east, and the work days here is different, I would like to change weekends from (Saturday & Sunday) to (Friday only) and start the week on Saturday. How will I do this with the file? I am working with excel 2010, thank you… BTW, great work in providing this type of gantt chart on excel :-)
@Francis. Thank you. Use either the gantt-chart_intl.xlsx or gantt-chart_o365.xlsx file, both of which allow you to define the work week. See the Help worksheet in each file for details and instructions.
I am now using gantt-chart_o365.xlsx and I cannot get the no work days to function properly. I have gone to the help page and changed the non-work days to number 1 (Saturday and Sunday) but my main chart keeps counting Saturday and sometimes Sunday as a work day. This is really frustrating as I have to manually change start days of each element of the project. Thank you for any help you can provide.
@Claudia, You might be using the template rows designed for calendar days (which ignore the weekend setting). First, make sure that you are using template rows for Work Days (if you use the spreadsheet as-is after download, the initial set of tasks are using the work day formulas). If that doesn’t help, you may need to send me a copy of your file so I can diagnose the problem. When you do, please let me know what version of Excel you are using, and reference a particular example in your file that doesn’t appear to be working. Tnx.
Any other payment option for the pro package, I don’t have credit card… and paypal forces me to pay with credit card for this payment.
@Stephan … Sorry, but Clickbank.com is the only reseller for this template, and I don’t have any other way of collecting payments. They’re supposed to be able to accept e-checks.
Hi there,
I have just bought the Gantt Chart template Pro. I need to extend the amount of weeks (the template has only 8) to about 30 weeks. I have looked everywhere to try and find the solution to this but can’t see how to do it. Can you please send me the answer or a hyperlink to a video so I can see what to do. In my version there is no red line to move across. Thanks.
@Heidi, The weekly and monthly views were designed for displaying a larger range of dates. But, if you want to add more columns to the right side of the gantt chart, you can do that using copy/paste. For adding more columns to the right side, see the following Q&A on the support page. It was written specifically for Excel 2007, but the idea still applies to later versions as well (Excel 2010 or later):
https://www.vertex42.com/blog/help/gantt-chart-help/gantt-chart-support.html#columns
Note that when you add more columns, the number of formulas in the file increases so adding too many columns may slow down the recalculation speed a lot.
I really like the O365 version, works great on my Excel for Mac 2016.
But, I find I miss the old completion status color change as it’s a quick indicator to being ahead, behind, or right on schedule.
Any way of getting the old completion status without reverting back to the old version? Even just providing conditional formatting tips are appreciated.
@Chip, The file gantt-chart_v4-0.xlsx uses that method (and should also work on Excel for Mac 2016).
Actually, I found a solution myself. Learning from the experts!
New conditional formatting rule:
Style: Classic
Interface: Use a formula to determine which cells to format
Formula: =AND(($F10+ROUNDDOWN($I10*$J10,0)-1)>$F10,$F10L$4)
Format with: I chose a pattern
I moved the rule to the bottom of the list so the row color would appear “behind” the pattern.
Works great!
@Chip … Thanks for providing a solution!
This is a very good article, thank you!
PS: I really love Excel Online
HOW TO CHANGE OR CUSTOMIZE COLOR FOR BAR CHART
@Gilbert … there is a column for entering a color code (b=blue, g=green, etc.) to change the color of the bar in the gantt chart. To customize it further you would have to edit the conditional formatting rule, and that would require you to have a lot more experience with Excel.
Hi there,
I recently bought this solution and I am very happy with it, but IO don’t know how to set more than one predecessors.
Please can anyone help me?
thk!
bye bye
@Tanny, in the new versions, you can unhide the hidden columns to show the columns that allow you to list more predecessors (up to 3).
Same as unhide you can also use Data Group/Ungroup function to improve the layout and create different views on the go
Can I change from weekly to monthly on the free version? The drop down box is not available.
@Gary, Sorry, but the free version doesn’t have that feature.
Hi
I am considering to purchase the Pro version, but I need to keep track of resources in my projects. Any suggestions about how to do that in Pro?
@Mogens, I have a couple different spreadsheets I created a while back (not the latest version) that included a very specific employee resource planning techniques (too complicated to want to explain how to do it from scratch via email or in a blog post), but I don’t have anything that does resource scheduling like you can do in Microsoft Project or some of the other online subscription-based Gantt chart tools.
Hi,
I have downloaded the free version as I only need it for a one off project, but it appears that areas of the excel work sheet is protected and when I attempt to un-protect it it requests a password.
Please assist.
Graham
@Graham, The free version is locked which prevents customizing it beyond the main intended use (and to encourage purchase of the pro version). Unfortunately, Excel on a Mac doesn’t allow as much freedom when a worksheet is locked, so the free version is more limited on a Mac. You are welcome to try the free Google Sheets version … it is not locked … here is the link.
How to change the colors of bar according to the task?
@Charu … the Pro version includes a Color column for entering a color code for different tasks (like “b” for blue and “g” for green).
Can we customize the colours in the pro version?
@SiMo, To change colors of the bars in the Gantt chart template, you would need to edit the Conditional Formatting rules. That may require more advanced experience with Excel.
can i change the weekly starting day to Sunday?
@Dalit, if you are using a version that lets you change the start day, try pressing CTRL+g to open the Go To menu and then find “startday” in the list. If you are using the Pro version(s) this will take you to the Help worksheet where you can change the start day.
Do you have an updated version of the Pro Gantt Template?
Updated since when? The latest version(s) are mentioned on the web page.
Hi – I downloaded the free version but am looking for the version that works on Excel online. Am I missing something?
Which free version? The main .xlsx download on the following page works in Excel online except for the slide bar (Excel online doesn’t support the slider bar form control). https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html
is it possible to show quarterly view?
Yes, the latest version includes a Quarterly view.
Hi,
I purchased Gantt Chart Pro using Paypal on 8/6, but did not receive any license letter notification. Paypal has indicated that the payment is being processed. Will license be sent to the mail I left on the purchase page?
@Will … Check the FAQ related to the payment process. Visit the support page for info about how to return to the download page.