◄ Return to the Gantt Chart Pro page
Problems Downloading? Need to return to the download page?
See our Template Support Page.
About This Page
Vertex42's Gantt Chart Template Pro spreadsheet contains a Help worksheet inside that explains how to use it and answers many common questions. There are also multiple videos on the Gantt Chart Pro web page showing how to use it and demonstrating some features. This blog post provides answers to various questions that may or 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 Pro (although if you need a response within a couple of business days, please use email instead).
Have questions prior to ordering the pro version of Vertex42's
Gantt Chart Template? FAQ - Prior to Ordering >
FAQ Quick Links:
- Most Important: How to add new rows?
- Why are there some formulas in the input cells?
- The Percent (%) Complete must be
entered manually - The spreadsheet is being slow. How do I speed it up?
- How to I see/print my entire project? I can only see/print what is currently viewable on my screen.
- Dates in dd/mm/yy format
- German Date format (TT – MMM – JJJJ)
- Creating Custom Date Formats
- Can I Import into Microsoft Project®?
- Is there a way to transfer data from some other Gantt chart spreadsheet?
- The red line marking Today’s Date disappeared.
- Increasing the number of columns in the Gantt Chart in Excel 2007+.
Most Important: How to add new rows?
See the Help worksheet for instructions on how to add new rows. It is critical that formulas are copied down after inserting new rows.
Why are there some formulas in the input cells?
If an input 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. For example, instead of manually entering today's date in the Today field, you could use the formula =TODAY() to have it automatically update.
See the Help worksheet for examples of ways to use formulas to define 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 (unless you are using formula to calculate the % Complete for a summary task, which is explained in the Help worksheet).
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. In some cases, the % Complete may be represented as a gray portion of the bar in the Gantt chart. This is fairly accurate for the Daily view, but is much less accurate in the Weekly/Monthly/Quarterly views because you might have only one or two columns for representing the duration of a task in the monthly view.
The spreadsheet is being slow. How do I speed it up?
The additional color-coding and other advanced features in Gantt Chart Pro 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.
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:
- A. Use the Weekly or Monthly view. Displaying and printing a larger range of dates is what this new feature was designed for.
- 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 (depending on how much of the worksheet you are viewing on your screen at one time - see the previous question).
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
By default, the spreadsheet uses the mm/dd/yy format to display the Start and End dates. See the Help worksheet for a new option that uses conditional formatting to let you display dates in dmy format.
To manually change date formats to the UK format (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)
Some labels may 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
The following is a pretty good article on working with custom date formats: "How to change date format in Excel and create custom formatting" on ablebits.com.
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).
Link: Importing data from Excel to MS Project - A walkthrough on TechRebulic.com for importing a vendor's Excel schedule into Microsoft Project.
Is there a way to transfer my info from a free Gantt chart to the Pro version?
No automated way, no. The Pro version does not contain any special import feature (because it does not contain any VBA macros). If you prepare the Pro version with enough new rows, you could probably copy the task descriptions and use Paste Special > Values Only, so that you don't overwrite any formulas or formatting. But ultimately, you'll need to create your schedule again in the Pro version if you want to use the Pro spreadsheet instead of a spreadsheet that you have already been using.
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+.
Video: Adding Columns to the Gantt Chart to Increase the Print Range
Watch via YouTube >
Here are the basic instructions without watching the video: Select the last 7 columns of the Gantt chart, then use the selection's drag handle to copy that group of columns to the right.
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).
Comments
I purchased the Gantt chart template, and was wondering how I could remove the excess months from the scroll bar, so that my graph doesn’t look as squashed?
@Marjan, You could temporarily hide the unneeded columns so that they aren’t printed. You could also adjust the print area or try modifying other print settings like scaling.
Hi, How do I change the colour grey in the “Completed Task” Cell Colour Key?
I am looking to have lighter colour ink use and also an easier visual.
regards
Brad
@Brad, You would need to alter the conditional formatting rules to change the grey color, which would be easier to do with an unedited version of the spreadsheet. Home > Conditional Formatting > Manage Rules … choose “This worksheet” from the drop-down to see all rules on the page. Cleaning up the conditional formatting for a heavily modified worksheet can be difficult if copy/insert-copied-rows was used instead of inserting rows then copying formulas down. The former approach causes Excel to create duplicate sets of rules, breaking up the “Applies To” ranges. Inserting then copying formulas down prevents that.
Hi, I´ve just purchased Pro. I am using “gantt-chart_o365” file. As soon as I save it with another name, G and I columns values turn into #¿NOMBRE?#. How can I fix that?
Thank you
What version of Excel are you using? I’m guessing you are also using Spanish language settings? I didn’t design this for anything but English, so my guess is that there is some formula that is not being converted to Spanish so the formula name is not recognized. I can give you a refund, but I don’t have the resources to support other languages. Sorry about that.
I am really happy with this program, however, when I opened the file, it came in in “Compatibility Mode” (I’m using 2007), and when I got half the info in and tried to save it, it comes up with multiple errors warning I will lose data unless I save it in another formal. I have to leave my work unsaved and my computer on until I can remedy this issue… help!
Additionally, I can’t seem to print anything but the first page. I tried changing the print parameters, but then it just put everything on one giant page. Where can I get help with this? Is there a support number?
Sincerely,
Lost and Confused.
Info on printing: Tech Support – Printing
As for editing in compatibility mode, I assume you were working with a “.xls” version of the file rather than a “.xlsx” version. If you use features of Excel 2007 that are unavailable in Excel 2003, then try to save it as a .xls file, then you may lose data. Sounds like you need to save your file as a .xlsx file type. You may want to Google “Excel Compatibility Mode” for more information.
Is there any way to add more debts to the debt reduction calculator excel file? I am trying to manage student loans. Thank you!
@jackelyn … you can download the extended version that allows up to 20 debts or 40 debts (separate files). It’s not just a matter of inserting rows, which is why there are separate files for listing more debts.
why does the new gantt_chart_o365v4-0 file have two sets of columns for start date, end date, work days and end days? The prior version of the workbook had one set of these columns.
@quinton … One set (the green columns) are inputs that might or might not be used, but the gantt chart requires dates, so the second set is required. You can hide the columns you don’t need to see after completing your gantt chart.
How do I add more (names & color code) to the templet box on the Help Sheet in the Gantt Chart Templet?
Color-Coding Based on Lead Names
If you want to use the table (I do) listed to the right of this help section, you can edit the following formula in the Color column. Not that the Names must match exactly.
=IFERROR( INDEX( lead_color, MATCH( ref, lead_names, 0) ), “”)
@Bill … What you are asking is an advanced customization that requires quite a bit of Excel know-how to expand. You’ll need to add more conditional formatting conditions to the gantt chart sheet to add new color codes and then expand the list in the Help worksheet so that the named ranges (lead_color & lead_names) include your new rows (or edit the named ranges directly using the Name manager). If any of that is confusing, you may need to hire an Excel consultant to help you customize your spreadsheet.
Hi, Really liking this tool. Helping a lot. Is there a way that I can get the individual task bar to reflect the % progress so that it turns grey as I manually enter the % progress. I have used the formula that turns the bar grey when 100% but visually it would be great if – for example – it was 50% grey when I entered 50%. I am fairly sure the tool has this capability but I am not having much luck. Thanks. Diane
Re comments above – I am using v3. Having downloaded and tested V2.4 just now I note that the template does this automatically as soon as I enter the 50%. why does V 3 not do this? Sorry – am I missing something really obvious?
@Diane, to make version 3 compatible with all the various versions of Excel, this particular feature had to be dropped as it caused a significant slowdown in recalculation speed and also because many people wanted to have the bars NOT change color based on %complete. Most of the differences between the various files are due to capabilities or limitations of the different versions of Excel.
Thanks – will use the earlier version as this feature is useful to our particular project
In your pro version is there a way to change projects items %completion bar on the gantt chart to a diff color, not just grey?
@Charles. Yes and no. If you are comfortable creating and editing conditional formatting rules, then it is technically possible for you to change the %complete bar to something other than gray. Go to Home > Conditional Formatting > Manage Rules and select “This Worksheet” from the drop-down to see all the rules. It is better if you do this with a fresh copy of the spreadsheet. You may want to try using the new desktop/mobile/online version which has the %complete as a separate column rather than having the bars turn gray.
I have been using the perpetual calendar to plan my lessons at the school I teach. not having to re-enter everything every year has been great! This year the calendar is a week “off” starting exactly one week later than I would like. How can / is it possible to make the population of data one week later?
Thank you
@Rae, I’m not sure what you mean by being a week off. You may need to contact me via email with more details and possibly with a copy of your file so I can see what you mean.
Hi
Is it possible to add planned and actual start/end dates? Is it a simple case of adding the columns? Sorry not a very heavy user of excel.. :-)
@Mathew … Yes, you could add new columns. They wouldn’t do anything, but you could add the columns. In the older version, there is a worksheet that shows planned vs. actual (there is a blog post about it), but the cross-hatching isn’t possible with Excel online, so that feature did not make the cut in the latest versions.
can I bring data directly from SQL table to the sheet that contains the GANTT
@Maria … Probably not. You may be able to import/export from SQL into a new worksheet and then get the data in a format that will allow you to do some copy/pasting into the gantt chart. However, you’ll need to prep the gantt chart to have the correct number of rows and then use paste special > values.
Hello, I have purchased the pro version of the template. I have modified the WBS level drop down to include more than 5 levels, and the WBS number is displaying correctly. The task column is not continuing to indent though, and my level 6 item has the same indent as a level 1 item. How can I made this indent automatically? Thanks!
@Brenna, The indent for the description is controlled via conditional formatting (Home > Conditional Formatting > Manage Rules), so you would need to add new rules for the other levels if you are adding more levels. You can look at the other rules to see how it is done.
Hi, when I insert a new row and then enter my dates, the bars don’t auto populate – I can’t seem to fix it as I don’t see the background formula that creates the auto fill. I have tried inserting brand-new rows directly below rows that do work and hit ctrl+d but it’s still not working. any suggestions?
thanks
Mel
@Mel, What you are describing sounds like the correct way to edit the file. So I probably won’t be able to diagnose the problem without seeing your file. You can email the file to me via the email address on my contact page. Make sure to mention what version of Excel you are using.
hi,
i currently using your template and i’m getting on with it really well. but is their any way of changing the original dates form a shaded grey to a shaded light red?
@Andrew … yes, if you change the format of the correct conditional formatting rule. Go to Home > Conditional Formatting > Manage Rules and then select This Worksheet from the dropdown to see all the rules. Good luck.
How to change the text color for task text on all WBS level two from black to the color I want?
@Bonnie, In the Color column you could add a formula such as =IF(level=2,”g”,””) where “level” is a reference to the cell in the level column. In this case it would make the bar green if it was level 2.
I have the Gant Chart Template Pro and I like all the features but the one thing I really don’t need is the actual Gantt Chart to rights. Is there another template I should use or can I delete those columns without harming the rest of the cells?
@Vivian … If I understand correctly, it sounds like you just want the task list part of spreadsheet and not the gantt chart. I’m pretty sure that you could delete all of the columns containing the gantt chart without messing up the task list. Save a backup of your file and try it.
Please can you tell me why each task description that I add seems to have a large gap before the text appears? Editing the alignment doesn’t make a difference and it is fine on some tasks.
Thanks
Shar
@Shar, In the new version indenting is added automatically based on the chosen WBS level. If you don’t want that to occur, you will need to remove the conditional formatting rule that is controlling the indenting (or choose a lower WBS level).
I’m using Gantt chart pro and would like to schedule tasks for persons who are working parttime. E.g. One team member is working 60% (off on Monday and Friday) and tasks assigned to this person should be adjusted accordingly. Can this be done by using kind of personalized holiday calendar in combination with the lead column? E.g. lookup of holidays using the value in column? I’m using the latest Gantt chart xlsx.
@Philipp … The template can’t handle different people with different work schedules without some customization. You would need to use different inputs for the WORKDAY.INTL() and NETWORKDAYS.INTL() functions for the rows where that team member was working. So, YES, you could define a separate list of dates to use for the holidays and/or define a separate “weekend” value, but then you’d need to either change the formulas manually, or figure out a way to use IF() within the formulas to check whether that team member’s name was in the lead or assigned to column. Perhaps a simpler solution would be to use a START DATE + WORK DAYS approach for defining the duration, and enter 40 work days as =ROUND(40/60%,0) for that person who is working 60%. That isn’t perfect, of course, but it would be a lot easier than the other approach.
Hi Jon,
finally I added a holiday calendar for each of the four team members. For each of those I defined a named range (e.g. consultant1_holiday). I also added a lookup table where names and ranges can be looked up (e.g. Phil –> consultant1_holiday). In the workday formulas I replaced the holiday argument with this lookup function:
INDIRECT(IFERROR(VLOOKUP(S171;holiday_lookup;2;FALSE);”holidays”))
This way the name in the Lead column (S171) is used for looking up the holiday range in the holiday_lookup range. If the name in the lead column is not found the usual holidays range will be returned.
@Philipp, Awesome! Thanks for sharing – sounds like a pretty good solution.
I downloaded gantt-chart_o365_L and it only shows 5 weeks. How do I add additional weeks? Nothing in the directions about that.
Rush request – I’m doing this for an interview and it’s due tomorrow morning (3/15/17). Please respond ASAP so that I don’t have to find another Gantt chart and start over.
@Ann … sorry I didn’t see your comment sooner. The free version only has very limited ability for customization. I’d suggest using one of the other gantt chart or timeline templates on my site if you need to use and customize a free version. In the Pro version, you can change to a weekly view or add more columns to the right (via copy/paste) to expand the gantt chart area.
Hi. I see in the holidays sheet that there are dates provided that are marked as US or UK in column C. Does the software use these to select the holidays for the correct country, and if so, where can I identify the country and the dates. Canadian holidays are different. All I’ve done at the moment is added the Canadian dates, and then altered the named range, “holidays” to stop at row 125 instead of row 4700. Is there an easier way?
@Hugh, You can delete the holidays you don’t want to include. Listing US or UK was just an easy way to identify the different ones. What you’ve done (added the holidays that are relevant to you) is the way it was intended to work.
I downloaded the .xls version some time ago but just started trying to use it for a project. It is really GREAT of you to offer this product free of charge.
I noticed however, that the slider doesn’t work so I downloaded the .xlsx version today but although I see a grey area there is still no slider nor arrows.
Is it just available in the pro version?
I also tried copying and pasting columns but the sheet is protected so I assume that I have to purchase the pro version first. Please let me know if this is the case.
Thanks.
@Don, The slider bar works in most versions of Excel (not Excel Online, though). The free xlsx version was designed for Excel online and mobile apps, so instead of a slider, you can change the Display Week number. The Pro version is not locked (i.e. password protected) and it has more features, but if you are using Excel Online or the mobile Excel apps, the form field slider bars cannot be used.
I downloaded gantt-chart-v4-0 today and the predecessors column doesn’t seem to do squat. I was expecting that if I enter 2.1 as the predecessor in column F20 I would anticipate that the start date in I20 should have jumped to 6/15/16 according to the dummy data that is setup. Please help. Why isn’t the spreadsheet working?
@Matt, Make sure that you are either using a formula to reference the correct WBS cell, or that you are entering the wbs value as text, as explained in the Help worksheet.
Does Gantt Chart Template PRO provide “actual start date” for tasks?
Thanks,
@roman … Yes, there is a version that allows you to specify both a planned and actual start date. See this article.
Can someone fully explain how to add names to tasks? I don’t fully understand these instructions. How and where do I add this formula? Where is this formula? Can’t figure this out. Thank you!
Color-Code Based on Lead Name
One popular reason for color-coding is to help identify ownership of a task. To do this, you could create your own nested IF formula: =IF(ref=”Bob”,”o”,If(ref=”Sally”,”p”,””))
where “ref” is the reference to the cell in the Lead column. If you want to use the table listed to the right of this help section, you can edit the following
formula in the Color column. Note that the Names must match exactly.
=IFERROR( INDEX( lead_color, MATCH( ref, lead_names, 0) ), “”)
@Adam, if you are using an existing “Lead” column (column might be hidden, or you may need to add your own column for that), then you simply type the name of the owner of the task in that column. That is how you “add names to tasks” … you don’t need a formula to do that. The formulas for color-coding based on lead name is a different issue – the formula to choose a color code based on a Lead name would go in the Color column. You still add names in the “Lead” column manually (or by setting up a data validation list if you know how to do that in Excel).