Need to create a project schedule? Try our Gantt Chart for Excel!

Technical Support for Gantt Chart Template Pro

◄ Return to the Gantt Chart Template Page

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.

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:

  1. Zoom In, using the zooming feature in Excel (View > Zoom)
  2. 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.
  3. 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:

  • 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, 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

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.

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: Adding Columns to the Gantt Chart to Increase the Print Range

Watch via YouTube >

 

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

Comments

334 comments… add one
  • 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?

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

      Reply
  • 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

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

      Reply
  • 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

    Reply
    • 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.

      Reply
  • 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.

    Reply
    • 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.

      Reply
  • Is there any way to add more debts to the debt reduction calculator excel file? I am trying to manage student loans. Thank you!

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

      Reply
  • 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.

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

      Reply
  • 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) ), “”)

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

      Reply
  • 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

    Reply
    • 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?

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

        Reply
        • 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?

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

      Reply
  • 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

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

      Reply
  • 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.. :-)

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

      Reply
  • can I bring data directly from SQL table to the sheet that contains the GANTT

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

      Reply
  • 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!

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

      Reply
  • 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

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

      Reply
  • 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?

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

      Reply
  • How to change the text color for task text on all WBS level two from black to the color I want?

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

      Reply
  • 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?

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

      Reply
  • 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

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

      Reply
  • 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.

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

      Reply
      • 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.

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

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

      Reply
  • 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?

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

      Reply
  • 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.

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

      Reply
  • 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?

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

      Reply
  • Does Gantt Chart Template PRO provide “actual start date” for tasks?
    Thanks,

    Reply
    • @roman … Yes, there is a version that allows you to specify both a planned and actual start date. See this article.

      Reply
  • 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) ), “”)

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

      Reply

Leave a Comment

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.