Need to create a project schedule? Try our Gantt Chart Template!

New Gantt Chart for Excel Online

◄ 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.

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.

Daily, Weekly, or Monthly Views

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.

Have feedback about this version? Please comment below.

COMMENTS

39 comments… add one
  • Can I change from weekly to monthly on the free version? The drop down box is not available.

    Reply
    • @Gary, Sorry, but the free version doesn’t have that feature.

      Reply
  • 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

    Reply
    • @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).

      Reply
    • Same as unhide you can also use Data Group/Ungroup function to improve the layout and create different views on the go

      Reply
  • HOW TO CHANGE OR CUSTOMIZE COLOR FOR BAR CHART

    Reply
    • @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.

      Reply
  • This is a very good article, thank you!
    PS: I really love Excel Online

    Reply
  • 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!

    Reply
  • 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.

    Reply
    • @Chip, The file gantt-chart_v4-0.xlsx uses that method (and should also work on Excel for Mac 2016).

      Reply
  • 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.

    Reply
    • @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):

      http://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.

      Reply
  • 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.

    Reply
    • @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.

      Reply
  • 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.

    Reply
    • @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.

      Reply
  • 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 :-)

    Reply
    • @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.

      Reply
  • 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! :)

    Reply
    • @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.

      Reply
  • Hi.. My operation does work on weekends and holidays. How do I include thos in this spreadsheet? Thank you.

    Reply
    • @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.

      Reply
  • 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

    Reply
    • @David … I don’t quite follow what you mean by “sub activity colors.” You can email me (see the contact page) with more information.

      Reply
  • 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

    Reply
    • @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.

      Reply
    • @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:

      1. Just set up your conditional formatting in the cell’s you want.
      2. Make sure you activate the “Developer” tab in the menu.
      3. Do this by clicking on the Office button, click on Excel options and in the “Popular” section check the box “”Show developer tab in the Ribbon”.
      4. Click on the Developer menu and go to the Visual basic view.
      5. Click on the menu View and select “properties Window”. In that list you can find the “EnableFormatConditionsCalculation”.
      6. Set this value to “True” and the auto refresh will work.
      7. Perform this action for all tabs in your sheet.

      Source: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/conditional-formatting-not-updating-automatically/746bf300-1328-436f-87fd-47cbfe9378d0?auth=1

      Reply
      • Hi Jon,

        It works ;-)

        Thank you very much.

        Br Jesper

        Reply
  • Hi Jon,

    When will this new version (3.0) bet out of Beta?

    Thanks,

    –YS

    Reply
    • @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.

      Reply
  • 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

    Reply
    • @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.

      Reply
  • I am looking for this excel sheet. Please let me know the Process to download this sheet.(Grant Chart pro)
    Thanks,
    Raja

    Reply
    • @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.

      Reply
      • can you unprotect the free trial version?

        Reply
        • @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.

  • Love it. It is exactly what I need. Thanks so much.

    Reply

Leave a Comment (comments are manually approved)