◄ 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
Good day Sir,
Hope this email finds you well. Am actually a MOSES, a student working on doing something that can manage my projects i was doing online research and fell onto your program which is pretty much very useful and of great help i could not afford the prices of microsoft project. whileless i downloaded the free copy of your vertex program and i tried to follow the videos to perfect it to my needs and desires i didn’t actually implement all the features cause some would not be of use to me but its been weeks i greatly need your help with getting the OVER DUE function working and creating the Dashboard for tasks which actually reflect the info of progress and over due thats all. I tried to use some conditional formatting queries e.g AND(Progress = end_date) which am not sure if correct but i see you used it but on my side its not working it basically highlights the whole left side RED of the working space for dates. Whereas i wanted to it on the uncompleted tasks and hence should be changing automatically without creating a check button attached are the pictures to illustrate what i mean. i noticed you do have a conditional format that has a stop but i dont know how to encorporate it with the version i downloaded as the video and the free version differ. Your help SIR would be highly appreciated with the right Conditional formats and changes.
Blessed day
Regards
Moses
There are too many questions related to constructing the Gantt chart from scratch for me to answer them (see the youtube FAQ comment for common questions). I must leave you to try it on your own using the videos, and you can purchase/download the pro version if needed, which includes the working versions of the worksheets used in the videos. For help with customization, you could contact ExcelRescue.net to get a quote for assistance.
Hi Please Kindly advise is it possible to project the overdue on your bar graph.
“Possible”, yes, but you may need to request assistance from ExcelRescue.net to customize the spreadsheet. You’ll need to provide more details about what you are wanting.
Hello,
I want to share our project management Gantt chart with my colleague, but how do I share the file so that they won’t be able to use the one I paid for?
(1) The file itself contains Vertex42 copyrights and links to the terms of use and license agreement (2) You would explain to your colleague that the file can be used for the project you are collaborating on, but they will need to purchase their own license if they want to use the spreadsheet for anything else. If you don’t think they will abide by these terms of use, then you could print to a pdf and give them a pdf, or use Google Drive and share it so that it is viewable but not downloadable (cannot be copied).
Dear Sir Jon,
I am reaching out to seek your assistance regarding a query I have encountered while working with Gantt Chart Pro. Specifically, I am attempting to link the %Done value to another workbook without altering the conditional formatting of the cell. Despite my efforts, I’ve observed that when capturing the value from the other workbook, the cell turns gray instead of maintaining the expected green color for 100% completion. Any guidance or insight you could offer on resolving this issue would be greatly appreciated.
Thank you for your attention to this matter.
Not sure how you have it linked up, but you may need to check the CF rule to see what’s going on. If the cell value is 100% (and not 99.99999%) then it should be green, if I’m understanding what you are doing.
I would like to add holiday dates to the Holidays Tab in the Gantt Pro template. I would like to delete holidays that do not apply to my country and add my own. I added holiday dates (manually via DD/MM/YYY) below those existing and checked the Named range however I now get #value! error across the template.
My guess is that you modified the Named Range somehow – make sure it is still correctly defined (by comparing to the original copy of the file). Also, try entering dates in m/d/yy format. If you enter in d/m/yy but it’s expecting m/d/yy then it may be interpreting the dates as text. You can delete rows and insert rows as long as you don’t delete ALL rows and you insert rows within the defined range.
Good Evening
We are using the Gannt Chart for scheduling trades and creating a schedule for construction.
When we enter a start date earlier than the scheduled date it doesn’t change the start date. Also if we have a lead/lag in there it doesn’t change the start date either.
I don’t have a ton of excel experience
You might have the task defined redundantly (such as using both a predecessor and a start date), or the logic may be different than you are expecting. When a task uses both a predecessor and a start date, the MAXIMUM start date is the one that is used. Delete the predecessor if you want to use a manually entered start date instead of the predecessor.
I need to change the Quarters from calendar quarters to quarter 1 starting Sept 1, Q2 Dec1, Q3 Mar 1, Q4 June 1. Is see in the formula: IF($R$3=”Quarterly”,”Q”&INT((MONTH(X4)-1)/3+1), so I believe it is tied to making a change here. Can you you help? Thanks
Replace “Q”&INT((MONTH(X4)-1)/3+1) with “Q”&CHOOSE(MONTH(X5),2,2,3,3,3,4,4,4,1,1,1,2)
You may also need to change cell X5 so that the first month is september rather than january. Change DATE(YEAR(C4)+R9-1,1,1) to DATE(YEAR(C4)+R9-1,9,1)
Ability to scroll through the weeks (box located at the top on the right next to Display, week and show overdue) is no longer functional. Just a white box, no formula etc. Help in figuring out how to fix it please? Thank you
You probably accidentally broke it. You’d need to email the file for me to check. Or, download a new copy and transfer the information (could be very time consuming to do that).
I tried adding new rows exactly as instructed but the formulas for the Start and End columns aren’t working correctly on the new rows. Here’s what was in the original cell for Start:
=IF(OR(J19″”,F19″”),MAX(J19,IF(F19″”,WORKDAY.INTL(MAX(IFERROR(INDEX(R:R,MATCH(F19,B:B,0)),0),IFERROR(INDEX(R:R,MATCH(G19,B:B,0)),0),IFERROR(INDEX(R:R,MATCH(H19,B:B,0)),0)),IF(ISBLANK(I19),1,I19+1),weekend,holidays),0)),IF(M19″”,IF(L19″”,M19-MAX(0,L19-1),WORKDAY.INTL(M19,-(MAX(K19,1)-1),weekend,holidays)),” – “))
And here’s what copied down:
=IF(OR(J20″”,F20″”),MAX(J20,IF(F20″”,WORKDAY.INTL(MAX(IFERROR(INDEX(R:R,MATCH(F20,B:B,0)),0),IFERROR(INDEX(R:R,MATCH(G20,B:B,0)),0),IFERROR(INDEX(R:R,MATCH(H20,B:B,0)),0)),IF(ISBLANK(I20),1,I20+1),weekend,holidays),0)),IF(M20″”,IF(L20″”,M20-MAX(0,L20-1),WORKDAY.INTL(M20,-(MAX(K20,1)-1),weekend,holidays)),” – “))
To me, they look correctly copied, but what’s displaying is a date “Tue 01/02/00” which is not correct, it should build on the prior row’s dates. I can’t find anything else that hasn’t been entered as a value that would impact it.
I also tried both methods of making new rows (insert blanks, then used ctrl+d and also did insert blanks and copy/drag). Doesn’t seem to make a difference.
What do I need to do to get the dates to continue correctly?
Thanks in advance for your help!
You’d need to send a copy for me to look at and diagnose. I can’t tell easily what is going on based on what you’ve included. But if you send a copy of the file and let me know which cells to look at and what you are expecting or not expecting, that would help. Typically, if you see something like “01/02/00” as a date (and it does not refer to 01/02/2000), then somewhere in the chain of calculations there is a missing date or something that is causing the calculation to be “2” . Most likely, the formulas in the Start/End columns are correct, but the inputs are not.