Creating a Gantt Chart with Excel is Getting Even Easier

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)

Screenshots of the new view controls in Gantt Chart Template PRO, version 2.3 for Excel 2003 (XLS)

Version 2.3 for Excel 2010 (.xlsx)

Screenshots of the new view controls for Gantt Chart Template PRO 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.

Feedback

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.

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 (http://www.vertex42.com/about.html) if you need additional help – I will also need more details about the problem. Thanks.

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?

@Davide:
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

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

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?

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.

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

Chris,
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.

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

@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

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.

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

Your Name will be displayed along with your comment. Your Email and IP address are stored with the comment and used to identify/prevent spam (via a service provided by Automattic.com), but are not shared publicly. See our privacy policy to learn more.

Save my name, email, and website in this browser for the next time I comment.