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
- The spreadsheet is being slow. How do I speed it up?
- I can only print what is currently viewable on my screen. How to I print my entire project?
- Dates in dd/mm/yy format
- German Date format (TT – MMM – JJJJ)
- Creating Custom Date Formats
- 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.
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:
- 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.
- 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.
- 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, 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).
The following video demonstrates this process with an older version in Excel 2007, beginning with saving a .xls file to the .xlsx file format: