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.
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?
- The Percent (%) Complete must be entered manually
- Dates in dd/mm/yy format
- German Date format (TT – MMM – JJJJ)
- Can I Import into Microsoft Project®?
- The red line marking Today’s Date disappeared.
- Increasing the number of columns in the Gantt Chart in Excel 2007.
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.
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.
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.
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.
- 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, or 2013, 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).
First, you’ll need to save the gantt-chart.xls file as a workbook with the XLSX extension. After saving the file, close the file, and then re-open it, and you should see that the column limit has increased.
If you are using the GanttChart worksheet, select the last date in the chart (which spans 7 cells) and press Ctrl+Space to select the associated columns and then copy paste those 7 columns to the right. You will need to update the print area afterwards.