◄ 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
How do i see the previous weeks? the sheet auto-updates to show/start the current week but i’d like to be able to go back and see what work was planned/done.
The Start Date at the top of the Gantt chart controls the timeline view.
I got this today and am really impressed with the features, but I am really struggling with the dependencies.
All I want to achieve is if I change the start date I want the whole project to move but with the new start date and I cannot get it to work without a green triangle appearing in the dependencies box, and the colour bar vanishing from the chart.
Green triangles are just annoying things that Excel adds (see the https://www.vertex42.com/support.html page about that), but otherwise it’s a matter of making sure that you are using references … very difficult to explain in a blog comment. You’d need to email and send a copy of the file and ask a very specific question about what you’d want me to look at. Thanks.
I started a Gantt Project using Gantt Chart Template Pro in Google Sheet in a collaborative way. One Editor accidentally erase “Holidays” Tab and now we have a lot of cell with “#REF” problem. How could I solve this problem without having to change the file or start from scratch?
Uhhh …. kind of difficult, but you could try adding a Holidays Tab and then creating a new named range called “holidays” or redefine the current named range that probably has a #REF error now. Probably easier to just start from scratch.
Recently purchased grant chart pro template . How do you display months , then below that the corresponding weeks within that month
Hi David, There is a monthly view option, but to customize the spreadsheet to do something it isn’t already designed for, you may need to contact an Excel consultant.
Hi, A well-constructed model. I’m using Pro. Have you built a formula to calculate the start date using dependencies ***and lag/lead times***?
(I did see in the Help tab of the free version the description of how to do this, but the formula in Pro is more complex). Thx.
You can still create your own formulas, but when using the Predecessors columns, it assumes you want to start the dependent task on the next work day.
Just purchased the PRO version. I have set the workdays to M – F (no weekends). Yet the tasks that span the weekend color Sat and Sun cells are colored the same as the task. The work days and calendar days are correct, but visually this is misleading. Is there a fix?
If you want them to be the color of the shaded weekends, you could change the order of the conditional formatting rules so that the rule that highlights the weekends is listed first. I think that may produce the result you are looking for.
Hello,
I am using the pro version of the gantt chart template and was wondering if there is an easy way to shift all dates in the project by x business days. We have a delay in a project that needs to move all dates and I have not been able to find a way to do this easily.
Thanks
That depends on whether you used dependencies to define the tasks. If all the tasks are linked, then changing the original task would change the rest of them. If you just entered dates manually, and want to add 5 days to all of them, then you could copy/paste the dates somewhere, add 5 to them, then use Paste Special > Values to past the new values over the top of the original dates. So, it all depends on how you set things up. The main reason for creating dependencies between tasks is so that the schedule can shift as needed when making one change.
Hello,
I purchased pro version. and I am using as monthly view. But date looks “mmm 2021 – mmm 2022” in the W10 cell. there should be written month name instead of “mmm” so how can I fix it and it can show month name properly? Thank you in advance.
It was designed for English language settings, so if you are using non-English language settings you’ll need to modify the formula to use the date format code appropriate for your language/location. For example, I think Germany uses JJJJ for the year. Google “Excel Date Formats for Different Language Settings”
Hi – great product, so happy and GLAD I have finally upgraded to the pro version! One question, in the project tracker section, can I add notes that remain locked in that cell, regardless of what week I am showing? I.e. if I make a note on Week 1 for instance, it frezzes in the view so when showing Week 7, the comment is still there?
Many thanks
Chris
@Chris – no, the timeline is updated with formulas and if you change anything like formatting or notes manually within the gantt chart view area, it will not move when you change the view or the dates.
I need to change the holiday dates to Australian Holidays. I looked at the holidays tab bottom left and it made no sense to me. Is there a version for Australia or do I have to change it manually?
Thank you. :)
I’m afraid I don’t have a list of those holidays. I’d recommend looking them up on a website somewhere and entering the dates. You can delete the rows of dates from the Holidays worksheet that do not apply, or overwrite them with dates that do apply to your case.
Actually, I would like to define task durations by inputting hours, Gantt Chart Template PRO has supported the hourly format yet?
@Jo, Time-based start times and durations are not supported in the main version of Gantt Chart Template Pro. There is a separate/older bonus file that was designed specifically for times on a 24-hour basis, but I’m guessing that’s not what you are wanting. While it is possible to add in a column to input number of hours and then calculate what the end date should be, there would be significant rounding errors due to the way the WORKDAY and NETWORKDAYS functions work.
Hi, just tried your Gantt Chart Pro and everything is nice except for your WBS numbering. Everything stays as 1.1.1 and does not go to 1.1.2 or 1.1.3, and so on. Can you help me on this? Thanks!
@Ariel, You’ll need to email a copy of your file so that I can see what you might have edited. The formulas should be working. See the Contact Us page for the email. Thanks.
Hi Jon
I have just bought and started to use the Gannt Chert – excellent!
A problem I have though is that date formats in columns P&Q start and finish dates, are formatted differently, no matter what I try to change them so they appear the same, they won’t change.
So when I try to change the font size to make it bigger, I can see that the row height changes, ie gerts bigger, but the date text remains small???
Thanks for any help
Billy
@Billy, if the “Shrink to Fit” option is turned on (Format Cells > Alignment tab), the dates will shrink to fit the size of the cell (to avoid showing ###### within a cell that contains a date that doesn’t fit the width of the cell). You can either widen the column or turn off the Shrink to Fit option.
Hi,
When I toggle between the Daily, Weekly, Monthly and Quarterly view, the year display shows YYYY and not the actual number. How can I fix this?
Thanks for your prompt answer
@Josee … you must be using different language settings. “YYYY” is the format code for the full year but for other language settings you would need to change the formula to be whatever is appropriate for your language, such as “JJJJ” for German.
Impressive work and does pretty much everything I need it too, but I am confused about one item. I have a larger project with several main tasks with sub tasks etc. I can use the “SUMPRODUCT” Formula to have the task show the % complete from the subtasks, but I don’t see an apparent way to show the % complete of the entire project.
Good question, @Carl … the answer is complicated when you have summary tasks within summary tasks. This is similar in a way to trying to calculate a grand total from a column that has subtotals, except that there isn’t a convenient function like SUBTOTAL that you can use to ignore the subtotals.
To calculate the %Done for a summary task, the generalized formula is =SUMPRODUCT(workdays,progress)/SUM(workdays), where workdays and progress are the ranges of cells within the Work Days and the % Done columns including the sub tasks but NOT other summary tasks. If you are using Excel 2013 or later, you can modify this formula to ignore the cells with formulas in the progress column (the summary tasks):
=SUMPRODUCT(workdays,progress,1*NOT(ISFORMULA(progress))) / SUMPRODUCT(workdays,1*NOT(ISFORMULA(progress)))
Note that we’ve changed SUM() to SUMPRODUCT() so that we can perform a “SUMIF” with the ISFORMULA function. The “1*” in this formula is for converting the TRUE & FALSE values to 1 & 0.
Example, let’s say that you have tasks with the following WBS values: 2 (level 1), 2.1 (level 2), 2.2 (level 2), 2.2.1 (level 3), 2.2.2 (level 3), 2.3 (level 2). You calculate the %Done for task 2.2 (which is the summary for tasks 2.2.1 and 2.2.2). So, that cell in the %Done column for task 2.2 contains a formula. For the calculation of %Done for the main summary task 2, you’ll need to use the modified formula to ignore task 2.2.
Hi,
in the weekly view my Template shows for the week numbers (Row 10) “00 YYYY – 00 YYYY” instead of “01 2022 – 06 2022”.
Anybody knows how to get the correct view for the weeks?
Thank you, very best, Michael
That means you are using non-English language settings. You’ll need to find out what date format code you should be using for the year instead of “Y” and then update the formulas. See the following about custom number formatting:
https://www.vertex42.com/blog/excel-tips/custom-number-formats-in-excel.html
Is there a way so that it does not include weekends in the bar graph?
You could hide the weekend columns if you are using the daily view and then unhide them if you change to one of the other views. If you are using a new version of Excel, you could go to View > Workbook Views > Custom Views to save different views, such as one without columns hidden and one with columns hidden, then use Custom Views > Show to switch between those views.
Thank you for offering your Exel wizzardry!
I am considering buying the pro version. May I please double check the “Advanced Version with Resource Availability” is no longer Beta but final and whether the resource allocation sheet is linked in such a way that it works together with Gantt Chart Pro rather than requiring entering the same data twice.
Might it be possible by any chance to use Time-Based Gantt Chart inside the Gantt Chart Pro, so some Tasks could be based on days and others on days?
Thank you for your help,
Ute
@Ute
– The Advanced version is still in beta, but it appears to be working fine. It would just be a formality to remove the “beta” label at this point, but I’m leaving it as-is.
– The Resource Availability worksheet is linked. You use it to enter resources (employee names), not tasks.
– The time-based gantt chart is different fundamentally from the day-based gantt chart. If you want to use a mixture of hours and days, I’d recommend using spreadsheet.com or projectmanager.com.
Hi,
Is there a way to show the delayed task ( for exp. the days that delayed will turn to red )
thanks
You could use a formula in the Color column to change the color to red “R” based on the date. See the Help worksheet for more details on using the Color column.
Hi! I have a question about predecessors.
When I put predecessor date of task changes not to next day, but next day +1.
For example I have Task1 that ends 20.02.2022. I need to start Task 2 after Task 1, i.e. 21.02.2022. But when I use predecessor Date of Task to changes to 22.02.2022.
Why it happens?
Was emailed about this already, but for the sake of others, there was a holiday on Friday and Monday. See the Help worksheet for settings related to the Weekend, and you can edit holidays via the Holidays worksheet.
In the 5.0 Advanced Beta I noticed that when you hide loads in the GanttChart, the milestone icons will also disappear. I fixed that by giving the conditional formatting rule for milestones a higher priority than the rule that hides “Load” numbers (by simply moving it up).
Also is there a way to set the current first week (cell Z9) in the GanttChart based on ISO numbering (e.g. based on a date that will be converted to ISO week number)? Looks like this is somehow tricky as the scroll bar is linked to Z9 and when I add a formula to Z9 the scroll bar will overwrite it. Of course I could write a macro that sets the scroll bar to a certain value but maybe there is a more elegant way…
@Christian. Thank you – I’ve updated the CF rule. To modify how the start date works while still allowing use of the scrollbar, modify the formula in cell AF5 (on a hidden row).
Could you provide the formula to use if we would like to have the % Complete value update automatically based on the current date?
@Joshua – Try this: % Complete = Days Complete / Total Days. Total Days = End Date – Start Date + 1. Days Complete = MAX(Start Date,MIN(TODAY(),End Date)) – Start Date. You’ll need to replace Start Date and End Date with references of course.
(Delay/lost day), If I have a {rain day} that causes a delay/loss day in my schedule where I loose that days work. How can I automatically add that delay to the remainder of my schedule, where from the delay date on, adds # day(s) delayed to the remaining working days. would also need to include the Predecessor to add days to all tasks
Hello Jay, If you have all of the predecessors defined (dependencies created between tasks) then if you change the duration of a task, the successors should automatically adjust.
How do I change the bar colour to GREEN when changing % complete. Currently it’s set to GREY colour
The color for % complete is not a variable. You’d have to go into the conditional formatting rule(s) and modify them. If you have been using copy/paste heavily, then the rules may have been split, so modifying the rules may be cumbersome and confusing. Note the “Applies To” ranges. Select a cell within the gantt chart area for the task you want to modify then go to Home > Conditional Formatting > Manage Rules and find the one(s) that affect the gray background. Save a backup of your file in case you mess something up. Contact ExcelRescue.net to request a quote for help with customization.
Hello!!! I need to repeat each task of my project every year! Can you help me please???
Thank you!!! Best regards
You could create a new copy of the worksheet each year. There is only one start/end date per task per row.