Technical Support for Gantt Chart Template Pro

by on January 31, 2013

 |   Email |  
 Print |  
 Share |  

Problems Downloading? Need to return to the download page?

See our Template Support Page.

About This Page

Vertex42′s Gantt Chart Template Pro workbook contains a Help worksheet inside that explains how to use it and answers many common questions. This blog post provides answers to newer questions or perhaps less common questions that may not be included in the Help worksheet. You can also use this post as a place to ask additional questions about using Gantt Chart Template Pro.

Gantt Chart FAQ - Purchasing

Have questions prior to ordering the pro version of Vertex42′s
Gantt Chart Template? Find the answers here >

FAQ Quick Links:

Green cells are inputs, but why do some contain formulas?

If a green cell contains a formula, that formula is there to provide an example of what you can enter in that cell. You can overwrite that formula by entering a value manually if you want to.

The dark green cells used for the level 1 tasks are special formulas used to summarize the information in the sub-tasks, such as using MIN() to determine the earliest start date and MAX() for the latest end date, and SUMPRODUCT() to calculate the overall %Done.

Important: When adding rows…

After adding rows, check the formulas in the dark green cells in the level 1 tasks to make sure they are still referencing all the correct sub tasks.

The Percent (%) Complete must be entered manually

Some people have asked why the % Complete value doesn’t update automatically, based on the current date. The % Complete is always a manually entered value (except for the dark green cells which calculate the overall % Complete for the main task based on the respective sub tasks).

A main assumption in the Gantt chart is that the % Complete for a task can only be estimated by the task lead or their team members. The two different colors of the bars in the Gantt chart (gray for incomplete and blue for complete) are extremely useful in quickly determining the overall status of your project. If you see gray in the bars to the left of the red line (current date) that is a warning to you that a task is behind schedule. A lot of blue to the right of the red line indicates you may be ahead of schedule.

Dates in dd/mm/yy format

The Gantt Chart currently uses the mm/dd/yy format to display the Start and End dates. To use the UK format for dates (dd/mm/yy), you can change the custom date format by selecting the cells containing dates and pressing Ctrl+1 to get to the Format Cells dialog box. Go to the Number tab and change the Custom format to “ddd dd/mm/yy”. Depending on your computer’s regional date settings (system preferences), you may then need to enter dates as “26 Jul 09″ to make sure that Excel interprets the date correctly.

German Date format (TT – MMM – JJJJ)

In the new version of the Gantt Chart, the column labels use the TEXT() function to display dates in different formats based on whether you are viewing daily, weekly, or monthly. For the German locale, you’ll need to change those formulas to “TT – MMM – JJJJ” instead of “dd – mmm – yyyy” and ” MMM JJJJ” instead of ” mmm yyyy”. You can make the change in one cell and then copy/paste to fix the others.

Can I Import into Microsoft Project®?

To import tasks from Excel into Project is pretty simple if the data is formatted correctly. You could create a new worksheet and copy your data so that it appears with the headings Name (for the task names), Start (for the Start Date), Finish (for the End Date), and Duration. Then use the Import Wizard in MS Project. I don’t provide support for MS Project (in fact, I don’t even own it).

Link: Importing data from Excel to MS Project – A walkthrough on TechRebulic.com for importing a vendor’s Excel schedule into Microsoft Project.

The red line marking Today’s Date disappeared.

  1. If you are using the worksheet that displays only Monday-Friday, check to make sure that Today’s Date is not a Saturday or Sunday.
  2. If you are using a formula for Today’s Date, use =TODAY() instead of =NOW() because NOW() returns both the date and the time.
  3. Make sure that Today’s Date is within the range being displayed by the chart.

Increasing the number of columns in the Gantt Chart in Excel 2007+.

Video: Increasing the Displayed Date Range in Excel 2007

watch now >

If you are using Excel 2007, 2010, or 2013, you can copy/paste columns to the right to extend the gantt chart for displaying a larger date range (because the new XLSX file format allows a larger maximum column limit).

First, you’ll need to save the gantt-chart.xls file as a workbook with the XLSX extension. After saving the file, close the file, and then re-open it, and you should see that the column limit has increased.

If you are using the GanttChart worksheet, select the last date in the chart (which spans 7 cells) and press Ctrl+Space to select the associated columns and then copy paste those 7 columns to the right. You will need to update the print area afterwards.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Technical Support for Gantt Chart Template Pro, 5.0 out of 5 based on 1 rating

{ 92 comments… read them below or add one }

Dan February 7, 2013 at 8:45 am

I just purchased Gantt Chart Pro and am very impressed.
I have started using the 2.3 beta and am having trouble with the bar colours.
The help file states that codes 1 to 6 are theme based colours, but they do not change to my selected theme. On inspecting the conditional formatting, I see that it does go someway to choosing my colours, but does not select the main one, rather the mid-grade colour that can be chosen as variations on the main shade.
Are you able to help me be able to select the main theme colours instead?

Reply

Jon Wittwer February 11, 2013 at 8:32 pm

@Dan … if you start with a fresh file (so that the conditional formatting isn’t messed up), in Excel 2010 you can go to Home > Conditional Formatting > Manage Rules, and then select “This Worksheet” from the drop-down box. That will brink up all the formats for the worksheet and you can go through and edit the colors to your heart’s content.

Reply

tom berk February 12, 2013 at 10:30 pm

Hi,
Can I add columns in this template and get the averages in all columns or only in the ones which it comes in – generic forms only with no adjustments for additional columns?
I’m doing my taxes and would like to use this every month the rest of my life, but i need to see the monthly averages for my own custom consumer spending.
Thanks for answering me in advance,
Tom Berk Seattle Wa

Reply

Jon Wittwer February 13, 2013 at 11:00 am

@Tom … I’m confused … what does your question have to do with the gantt chart template? taxes? consumer spending? Sounds like budget-related stuff rather than project planning.

Reply

Rick Chin February 25, 2013 at 8:12 am

I am using beta 2.3 of the rtemplate in Excel 2010/2013. How do we add a third or fourth level to a project? There are only blank examples at the bottom for label only at these levels.

Reply

Jon Wittwer February 25, 2013 at 8:57 am

The cells containing the WBS level can be copied/pasted without having to copy/paste the entire row. The WBS formula is independent of cells in other columns. The indenting of the Task description for various WBS levels can be done via cell formatting.

Reply

Shan Hill March 15, 2013 at 3:33 pm

I would like to put an “end date” so that my project does not contiue way out past deadline. I cannot figure out how to limit the number of columns (Excel 2007)

Reply

Jon Wittwer March 16, 2013 at 2:24 pm

@Shan … To specify an end date for a task, use one of the template rows where the end date column is highlighted green. Then, you can enter the start and end dates for a task rather than a start date and duration.

Reply

hector rayner March 19, 2013 at 9:50 pm

I am impressed with this spread sheet but why there are some features from 2.1 version not included in latest version? I love to use the hide/unhide feature at the side which is quick for me since i am dealing a lot of operation in one sheet

Reply

Jon Wittwer March 20, 2013 at 9:11 am

@Hector … you can still use grouping if you want to. It’s a standard built-in Excel feature found in the Data tab (in Excel 2010).

Reply

Dan April 3, 2013 at 4:01 am

Jon – many thanks for your earlier reply about colours.
I have been using the template very successfully for the last couple of months and must thank you for your excellent work.
I have a couple of ideas in case you have time to develop the template further:
1. Adapt the y-axis date title to show full months instead of weeks (or both together). I realise this is complex, but will help people get their eye in quickly when looking at a full year. It would also be good to have a slightly bolder grey vertical grid-line at each month.
2. Make some kind of tag that will place a bold red line underneath a bar to denote critical path. This would be manually generated.
3. An option to duplicate the task title in the coloured box (or immediately following it). I find this helps people read the chart and I have been adding them manually (or supplemental comments).
Lastly – I think I am on version 2.3 beta. How can I get the latest version?

Reply

Jon Wittwer April 3, 2013 at 1:22 pm

@Dan,
Last question first: Version 2.3 (beta) is still the latest version. To download the latest versions, see the support page for how to log into clickbank.com to get back to the download page using your receipt and email.
1. Noted
2. This could be done with the XLSX version by adding another conditional formatting condition based on a new column (similar to how the Color column works), though I’m not sure yet whether I’ll work that into a future version or not. The critical path analysis would complicate the gantt chart template too much (without VBA), though I have a spreadsheet that demonstrates the method: http://www.vertex42.com/ExcelTemplates/critical-path-method.html
3. I don’t know how to do that without VBA. Adding formulas to all of the cells to display the Task title within the chart would not work, because the title will not overlap other cells if they contain formulas.

Thanks for the suggestions.

Reply

Dan April 4, 2013 at 12:06 am

Thanks again for the speedy reply Jon.
I will have a look into the conditional formatting and see if I can come up with something for the critical path. I wasn’t suggesting an automated analysis – I just wanted to be able to highlight some bars in my Gantt. I agree that adding formulas to all of the cells would complicate the spreadsheet – I can certainly make do as is – I am just annotating, really.
After writing yesterday, I had a few other thoughts:
1. I started representing milestones as single day activities and then moved on to overlaying annotated triangles on the bars. This was pretty successful, but I wonder if there’s a way to have multiple single day bars on one line. I realise this would mean multiple date inputs and hence I think it’s not really feasible. It maybe worth thinking about how to represent project milestones, though.
2. The overall line for a group of sub-tasks might be best represented as a thinned black line. I think this might be another conditional formatting solution that I could put together.
3. Would it be worth changing the conditional formatting of the red ‘today’ line so that it overrides the grey grid-line? This would avoid the stripy effect.
4. My critical path idea from my previous message could also be used to show planned vs actual, but again would require more date inputs.

Reply

Jon Wittwer April 4, 2013 at 9:32 am

Hi Dan,
Re: Milestones on a single line (Not Easy) – Probably the best way to include single-day milestones on a single line would be to define a list somewhere of the milestone dates and then create a row with a unique conditional format condition that would fill the cell some color if the date associated with that cell was found in the list. The formula for the conditional format might be something like =NOT(ISERROR(MATCH(P$9,milestones,0))) where “milestones” was a named range referencing the list of dates. I’ve done this in a test file and it works well (for the daily/weekly views). If you’d like me to send you a copy, email me.
Re: “today” line less “stripy” – This will be integrated in the next update – just required adding red borders to the conditional format condition to override the grey gridline, in case you want to do that yourself.

Reply

Dan Bourne April 9, 2013 at 3:20 am

@Jon
Thanks again!
BTW – I was not getting email notification of your reply, maybe because each message was a new one – hence the delay

Reply

Melinda April 16, 2013 at 1:30 pm

For some reason the red line disappeared, and I do not have problems with any of the three possible solutions listed above. Any other suggestions?

Reply

Jon Wittwer April 17, 2013 at 7:51 am

@Melinda, no additional suggestions. You’d need to send a copy of the file via email so that we could diagnose the problem.

Reply

Melinda April 22, 2013 at 7:37 am

Where can I email it?

Reply

Jon Wittwer April 22, 2013 at 7:56 am

@Melinda … See the following page: http://www.vertex42.com/about.html

Reply

Ron Mueller February 2, 2014 at 1:03 pm

I put all the information in and using Insert to add lines. the formula appear in each block but it does not plot the timelines? must have done something incorrectly .
Any help is appreciated

Reply

Jon Wittwer February 3, 2014 at 1:24 pm

@Ron … Insert of inserting blank rows, you need to copy an existing row (from the set of template rows for example) and insert the copied row.

Reply

Rahul May 3, 2013 at 6:32 am

I have converted the Weekly format into the hourly format.
i.e. from 1 Jan 2013 – 7 Jan 2013 – 14 Jan 2013….. To 1-1-2013 12AM – 1-1-2013 8AM – 1-1-2013 4PM – 1-2-2013 12AM….
All things are going well
Except, the total duration part which is represented by gray color is lagging exactly 24 Hours (1 Day).
i. e. if start time is 1-4-13 8:42 AM and end time is 1-12-13 11:00 AM
then gray color starts at 1-4-13 8:42 AM & ends at 1-11-13 11:00 AM instead of 1-12-13 11:00 AM.
Thanks You….

Reply

Jon Wittwer May 3, 2013 at 11:40 am

Hello Rahul,
You would need to email me a copy of your file for me to look at. Is there a reason why you didn’t just start with the bonus file that is already set up to handle an hourly format?
Thanks

Reply

Rahul May 7, 2013 at 6:39 am

Hello Jon,
I have added some columns into the template.
After that the today,s Date line (Red color) is not displaying after some columns.

Thanks You….

Reply

Jon Wittwer May 7, 2013 at 7:57 am

@Rahul, sounds like the conditional formatting conditions need to be updated. You should be able to find a cell within the chart that works and then copy that cell across and down.

Reply

Pat Sniffin May 28, 2013 at 7:21 am

Is there a way to change to a 4 day work week? My department works 4 ten hours days with Friday being the off day.

Reply

Jon Wittwer May 28, 2013 at 1:29 pm

@Pat … Possible, yes. The new WORKDAY.INTL and NETWORKDAYS.INTL functions in Excel 2010+ make it possible to say exactly what days should be considered “weekends” but the template(s) do not currently use those functions.

Reply

Kenneth Leung June 10, 2013 at 2:20 am

Work great, although need some time to get familiar with Gantt chart pro (Since i need to change to format to suit my preference). I found that it is even easier to use than MS Project. Latest version of MS project made things too complicated.

btw, i have to following questions
1. How to add a blank row without affect the WBS numbering? sometimes i want to make a remark right after a task
2. How to Shorten the width of the work calender?
3. Is it possible to have a special icon for Milestones

Thanks~

Reply

Jon Wittwer June 10, 2013 at 1:23 pm

3. The conditional formatting used in the gantt chart area cannot display arbitrary icons, but milestones could be represented as a particular color (if you are using the XLSX version), or you could edit the conditional formatting of one of the color conditions so that a border was added in addition to the color.
2. Work calendar? I don’t know what that is referring to.
1. You could either manually enter the WBS after a blank row, or the formula could be edited by changing the “-1″ within every OFFSET() function in the formula to “-2″ so that it refers to the cell two rows above the current one rather than one row above.

Reply

Kenneth Leung June 10, 2013 at 10:03 pm

3. Can you add the ‘white’ color’ for me to manually enter a text on the milestone? I use Wingdings ‘u’ as the icon. but the format make the text look odd~ btw, i am now checking if the workday=0, the bar color change to black

2. i solve it by hide the columns

1. Thanks, manual enter the WBS work great and easy~

btw, is it possible to have one more vertical line? i want to check the task against a deadline~
now i do it by altering the today date~

Reply

Jon Wittwer June 11, 2013 at 1:13 pm

Kenneth … Using the Excel 2007+ version (XLSX), it would be possible to add more than one vertical line, by adding another conditional formatting rule. You can change the color formats for the conditional formatting rules if you want to customize the spreadsheet for your use. Go to Home > Conditional Formatting > Manage Rules and select “This Worksheet” from the drop-down box to edit the rules.

Laurie June 12, 2013 at 7:19 am

Is it possible to make the Monthly time sheet always begin with Sat. but still add the dates automatically and to subtotal the weeks from Sat-Sun. for OT purposes?

Reply

Jon Wittwer June 12, 2013 at 7:30 am

Possible? Probably. I’m guessing the follow-up question will be “how”. But, this question is unrelated to the Gantt Chart template. Please email me if you need help customizing the time sheet.

Reply

Joe July 2, 2013 at 9:24 am

I need to add Saturdays as a work day in my Gantt Chart, right now it does not include it as a work day but rather a weekend day.

Thanks in advance

Reply

Jon Wittwer July 2, 2013 at 5:26 pm

@Joe, If you are using Excel 2010 or 2013, it would be possible to replace the WORKDAY() and NETWORKDAYS() functions with the WORKDAY.INTL() and NETWORKDAYS.INTL() functions so that you can specify which days of the week to include as work days. There are many places that those functions are used in the template. You can look up these new functions in the Excel Help system. You might try using Find/Replace to replace all “WORKDAY(” with “WORKDAY.INTL(” and all “NETWORKDAYS(” with “NETWORKDAYS.INTL(” and all “,holidays)” with “,11,holidays)”

Reply

ian lamaletie July 19, 2013 at 5:50 am

Is there a way to establish a milestone line for each phase of the project plan? So that everything is with say WBS 1…… has the phase deadline .

Many thanks
Ian

Reply

Jon Wittwer July 19, 2013 at 2:45 pm

@Ian, If using the .xlsx version … For a milestone, just use one of the template rows that lets you choose the Start and End dates and set the color code to “k” for black. If that doesn’t answer your question, you will need to email me with more specifics.

Reply

Patricia July 19, 2013 at 8:10 am

I just bought Gantt Chart Pro and I think its amazing. I just have a question, can I modify the workdays so it includes Saturdays but not Sundays?
Thank you.
Patricia

Reply

Jon Wittwer July 19, 2013 at 2:43 pm

@Patricia, Yes. See Joe’s comment. I’ve created a version that uses NETWORKDAYS.INTL and WORKDAY.INTL, but for now I’m just emailing it to people who need it. So, send me an email with your receipt # and I’ll send you a copy.

Reply

Jason July 27, 2013 at 1:57 pm

I am wondering if there is a way in the Gantt Chart Dailyto use a 7-day work week. RIght now it only displays Monday – Friday, even if I insert calendar day rows.

-Jason

Reply

Jon Wittwer July 28, 2013 at 1:50 pm

@Jason … make sure the “Show Weekends” checkbox is checked.

Reply

j July 30, 2013 at 9:41 am

I am guessing that the answer is going to be embarrassingly obvious but…..where do I find the “Show Weekends” checkbox?

Reply

Jon Wittwer July 30, 2013 at 1:49 pm

Take a look at the screenshots shown in the following article. You’ll see the “Show Weekends” checkbox in those screenshots. If you aren’t seeing the checkboxes in the excel files, then either something very weird is going on, or you aren’t using the updated versions.

http://www.vertex42.com/blog/help/gantt-chart-help/creating-a-gantt-chart-with-excel-is-getting-easier.html

Mary September 11, 2013 at 5:53 am

I am very impressed with your time sheets. I would like a weekly sheet for a construction company that would allow notes on what was done during the day and where they worked. Do you have one already?

Reply

Jon Wittwer September 11, 2013 at 12:12 pm

The closest thing to that might be the “time tracker”: http://www.vertex42.com/ExcelTemplates/time-tracker.html

Reply

GGredley September 20, 2013 at 6:48 am

I have just purchased the pro template and am having difficulties using any function simply because it is so slow to use. I am using it off an IMAC through excel 2010 and am constantly seeing the spinning beach ball. I am trying to format the cells/cloumns and rows to a size that suits and cannot seem to drag multiple rows or columns to do so. Can you help please.

Reply

Jon Wittwer September 20, 2013 at 12:02 pm

@GGredley: One thing that might help both issues is to just increase the zoom, which changes only the display on your screen. If you are trying to view the entire worksheet at the same time, it takes Excel longer to do the calculations necessary to refresh the screen. Another way to speed things up is to delete some of the columns from the right side of the gantt chart area. That will reduce the overall number of formulas and should speed things up a bit, but zooming may be a better approach (and less permanent than deleting columns). The Excel 2003 version (.xls), which will still work in Excel 2010, might work faster than the .xlsx version because it uses fewer conditional formatting rules (the extra conditional formatting rules are for the color-coding).

Reply

Gilbert September 20, 2013 at 7:42 am

Want to use more than one predecessor! How can i, if possible?

Reply

Jon Wittwer September 20, 2013 at 11:54 am

@Gilbert: There is a video demonstrating how to use the predecessor columns. I would recommend watching that. Or, if using a formula, you could use something like =MAX(WORKDAY(enddateA,1,holidays),WORKDAY(enddateB,1,holidays)) where “enddateA” and “enddateB” are references to the dates listed in the End column for tasks A and B.

Reply

Renee September 23, 2013 at 1:57 pm

Hi,

I would like to download a 2013 calendar with all the holidays and dates already on it. I want to be able to type in activities from my computer on to this calendar and save it with the project and eventually print it out.

What calendar do I download? I have Microsoft Office Excel 2003.

Thanks

Reply

Jon Wittwer September 24, 2013 at 1:43 pm

@Renee … sounds like you might want to try the one on this page: http://www.vertex42.com/ExcelTemplates/excel-calendar-template.html (the big green download button that says “for Excel 2003 or later” underneath).

Reply

Jerome Seguin October 13, 2013 at 8:04 am

I have been using Calendar Pro for several years. I believe I have version 1.6. The mini calendars at the bottom of the generated calendars are showing Dec and Feb for every month. How can I fix it to reflect the correct months? Thanks

Reply

Jon Wittwer October 15, 2013 at 1:58 pm

@Jerome … you’ll need the most recent version if using Excel 2010 or later. Email me if you are unable to log in to download the new version.

Reply

Jeorme Seguin October 20, 2013 at 1:10 pm

Thanks Jon: I purchased Calendar Pro about 6 or 7 years ago and I do not have a copy of a receipt. Is there any way I can download it at an upgrade. The application works fantastic with the exception of the mini calendars. I do not want to have to spend the extra $9.95 to resolve the minor inconvenience, Thanks.

Reply

Jon Wittwer October 21, 2013 at 7:21 am

@Jeorme … You’ll need to contact me via email so I can send you the file.

Jim Ong November 25, 2013 at 5:43 pm

I am using the gant chart and would like to include all weekends but exclude hoildays.
The WORKDAY.INTL function only allows to select a differnet weekend.

Reply

Jon Wittwer November 27, 2013 at 9:21 am

Yes, the WORKDAY.INTL and NETWORKDAYS.INTL functions would be the way to go. In this case, you could use a string for the weekend parameter of “0000000″ to mean that there is no weekend, but it still allows you to define holidays. If you are a “Pro” customer, I can send you a version that uses these .INTL functions (send me an email with your order #).

Reply

Leave a Comment

Previous post:

Next post: