Creating a Gantt Chart with Excel is Getting Even Easier

by on December 5, 2012

 |   Email |  
 Print |  
 Share |  
Post image for 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)

Gantt Chart Template version 2.3 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)

Gantt Chart Template version 2.3 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.

Gantt Chart for Google Sheets
View Larger Image

VN:F [1.9.22_1171]
Rating: 4.4/5 (9 votes cast)
Creating a Gantt Chart with Excel is Getting Even Easier, 4.4 out of 5 based on 9 ratings

{ 34 comments… read them below or add one }

Thang December 15, 2012 at 7:55 pm

I used to work with some previous version products. I like its so much for studying

Reply

Steve January 21, 2013 at 11:49 am

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!

Reply

Jon Wittwer January 21, 2013 at 3:40 pm

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.

Reply

Sophia February 17, 2013 at 3:13 pm

Will I be able to use this program with MAC… I currently use Microsoft for MAC – Excel, Word etc.

Can someone advise. Thanks.

Reply

Jon Wittwer February 17, 2013 at 9:54 pm

@Sophia … Yes, it works in Excel for the Mac. There is no VBA in the gantt chart templates.

Reply

Davide March 12, 2013 at 11:03 am

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.

Davide

Reply

Jon Wittwer March 16, 2013 at 2:32 pm

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

Reply

narath April 10, 2013 at 7:50 pm

Very good

Reply

GBowden April 29, 2013 at 8:52 am

Is there a version for Excel 2007?

Reply

Jon Wittwer April 29, 2013 at 11:15 am

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

Reply

alvin June 11, 2013 at 5:08 pm

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?

Reply

Jon Wittwer June 11, 2013 at 5:53 pm

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

Reply

alvin June 12, 2013 at 4:55 pm

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?

Reply

Jon Wittwer June 13, 2013 at 11:54 am

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.

Tom Ho July 2, 2013 at 8:15 pm

Hi,

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.

Tom

Please kindly reply

Reply

Jon Wittwer July 3, 2013 at 11:07 am

@Tom: Column N in the .xlsx version of the template.

Reply

Giji Thomas July 25, 2013 at 7:30 am

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!

Reply

Jon Wittwer July 25, 2013 at 11:44 am

@Giji, Only the Pro version has the drop-down box feature to change between days, weeks, and months.

Reply

Tracy D August 2, 2013 at 1:00 pm

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

Reply

Jon Wittwer August 3, 2013 at 11:19 am

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

Reply

Jim November 12, 2013 at 10:15 am

This is a great tool. Do you have a capacity planning template?

Reply

Jon Wittwer November 14, 2013 at 2:14 pm

@Jim … I’m afraid I don’t know enough about the topic to create a capacity planning template.

Reply

Jimmy Hartington December 13, 2013 at 5:15 am

Is there a way in the pro version to customize it for a different language?
So it becomes danish weekdays etc.

Reply

Jon Wittwer December 16, 2013 at 5:14 pm

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

Reply

Brandon Tarr December 18, 2013 at 3:56 pm

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?

Reply

Jon Wittwer December 18, 2013 at 8:13 pm

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

Reply

Chris Firgaira February 11, 2014 at 7:45 pm

Hi Jon,

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.

Regards

Reply

Jon Wittwer February 12, 2014 at 10:49 am

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.

Reply

Tito Vazquez July 9, 2014 at 11:11 am

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.

Reply

Jon Wittwer July 10, 2014 at 10:47 pm

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

Reply

susana mendez July 14, 2014 at 4:53 pm

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.

Reply

Jon Wittwer July 15, 2014 at 9:58 am

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

Reply

Bhavesh July 18, 2014 at 11:51 pm

Please send us the Demo of latesr Excel Gantt Chart. and also provide the prise for the pro Version

Reply

Jon Wittwer July 19, 2014 at 2:16 pm

@Bhavesh, the download of the free version as well as the price of the Pro version can be found on the following page: http://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html

Reply

Leave a Comment

Previous post:

Next post: