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

336 comments… add one
  • Hi,

    I downloaded a gantt chart from here. Made modifications, added various other sheets to my workbook. Now when I moved back to gantt chart and try to add more rows, it seems like it no longer views the sheet as a gantt chart. Bars dont change corresponding to new tasks. Any ideas on how to fix it?

    Reply
    • @Dheera, Difficult to diagnose without seeing the spreadsheet. You can email and send it as an attachment. The bars are created via conditional formatting, so perhaps formatting was removed from the new rows.

      Reply
  • Dear Support team
    I’m a new user of your Grant Chart Pro, I’ve try to use this for my work tracking and it seem to be ok, But I’m working in Thailand and then I’ve to update the Holiday table in the excel but found that it have some fomula inside the cell and I’m not sure it will effect to the other formula or not.
    Threfor, I need your support for the instruction how to add Thailand holiday and how it work?
    Best Regards
    Vikorn Liapprasert

    Reply
    • @Vikorn, Instead of trying to figure out formulas for calculating Thailand holidays, you could just look up the dates for the holidays (via resources online) and enter the dates into the list for the current year and next few years. You can clear/delete the default list of holidays that don’t apply to you.

      Reply
  • Is there still support for this product?

    Reply
  • Hello,

    Is there any way I can create a recurring task? e.g. I want task x to show up on the chart each month without having to keep inserting fresh each month?

    Reply
    • @Dan, Doing something like that requires some custom formulas. In a couple of the files, I have an example of a recurring task at the bottom of the gantt chart, but it isn’t based on a particular day each month. In general, the gantt chart isn’t designed to show recurring tasks on a single row. In part, that is because it would be hard to create any dependencies between that task and another task. Other gantt chart software may be better suited for creating calendar items such as recurring tasks that occur on specific days of the month, or every other week, etc.

      Reply
  • I purchased the Critical Path Pro version . But when i add the data and labels . the graph only shows the first 23 lines . The remaining lines are not visible. I only have 75 lines of input , i followed the steps of adding 1 row at a time and copy pasting from the line above the the Finish line or the line below the start line . I have tried it 3 times but the result is the same .

    Reply
    • @Kudzai, You’ll probably have to email a copy of the file to me (see the contact page for the email to use) so I can see what you are doing. You do need to add more rows. After inserting more rows you have to copy an entire row and fill in the empty rows so that all formulas, including those in the hidden columns, are copied down as well.

      Reply
  • How do I expand the date grid to show 2020 columns?

    Reply
    • See the section in the post about adding more columns to the gantt chart.

      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.

Cancel reply

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