Technical Support for Gantt Chart Template Pro

Problems Downloading? Need to return to the download 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.

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.

The spreadsheet is being slow. How do I speed it up?

The additional color-coding in the XLSX version of the gantt chart can sometimes make the spreadsheet recalculation sluggish. The key to speeding this up while you are making changes is to display less of the gantt chart on your screen at one time. Here are some ways to do that:

Zoom In, using the zooming feature in Excel (View > Zoom)

Hide columns and rows that you do not need to see for now. For example, hiding columns starting from the right side of the gantt chart, or hiding a group of rows for sub-tasks that you are not working on.

Try the XLS version. You can save it as a XLSX file after you open it, but the point is that the XLS version does not use the color-coding feature and therefore tends to refresh faster.

How to I print my entire project?

In Excel, you can only print what is currently viewable in the worksheet. You can change the range of dates viewable in the gantt chart (using the scroll bar), but to increase the range of dates, you will need to either:

B. Add more columns to the right side of the gantt chart and then update the print area. See the FAQ below for a video that shows how this is done. Note that adding more columns can significantly slow down the recalculation speed in the XLSX version, and the XLS version is already using the maximum number of columns that Excel allows.

There is also the old-school method: If you must use the daily view and want to print your entire project and you can't add more columns, you can try using scissors and tape. Print, change the date range, print, etc. then cut and tape to assemble a wide view of the chart on multiple pieces of paper.

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.

Creating Custom Date Formats

Finding Your Locale Code: If you want to use a locale code like [$-409] in your date format string, you can look for your locale code in the LCIDHex column of this official list. For example, the LCIDHex code is "0c0c" for French_Canadian, so you can use a custom date format code of "[$-0c0c]dddd, mmmm d, yyyy" to show day and month names in your language.

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

The red line marking Today's Date disappeared.

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.

If you are using a formula for Today's Date, use =TODAY() instead of =NOW() because NOW() returns both the date and the time.

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

If you are using Excel 2007, 2010, 2013, or 2016 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 than the XLS file format).

Important: When using copy/paste to append columns to the gantt chart, make sure to copy and paste columns in groups of 7. Select the last 7 columns in the gantt chart and press Ctrl+c to copy them. Then, select the next blank column and press Ctrl+v to paste.

Selecting columns in the versions of the gantt chart that have very narrow columns: If you are currently displaying the weekly view in the files that use 7 columns per week, you can select a group of 7 rows by first selecting the last date in the chart (which spans 7 columns) and then pressing Ctrl+Space to select the associated columns.

After adding columns to the gantt chart, you will need to update the print area (via Page Layout > Print Area).

The following video demonstrates this process with an older version in Excel 2007, beginning with saving a .xls file to the .xlsx file format:

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 #).

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

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.

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.

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

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.

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

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.

@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. [Comment Updated] – The Pro version files gantt-chart_o365.xlsx and gantt-chart_intl.xlsx both use these new functions now. See the Help worksheet within those files.

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

@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)”

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

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.

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~

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.

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

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.

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.

Jon I was looking for the milestones on a single line. Actually I wanted to show an audit process for a project on the line below the project. Currently I have an individual line for each date but if you could email me the what you worked on for this I would adapt it for my needs. Any help is appreciated. Thanks.

@Joe … Email me if you want me to send you an example. However, if you want to implement this in the file that you are using, you’ll need to either figure it out from the example (and learn about conditional formatting and lookup functions and named ranges) or get a quote from an Excel consultant to help you.

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?

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

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

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

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.

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

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?

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