◄ 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
I downloaded the Simple Budget Worksheet. I have more expenses than the worksheet has space for. I think about line 32 or 33 there is a Total Line. I need to move that Total Line down so I can enter more expenses. How do I do it?
Joe
@Joe, You’ll need to insert a row above the last item (not just above the total row, perhaps line 30) and then copy formulas down. Check the formulas for the Totals to make sure that after you insert the row, the references are still correct.
I am very impressed with this spreadsheet template. Everything worked quite well so far – except that the blue task bars under a certain line do not turn partially grey any more. They turn grey completely when I enter 100%, but anything less than that leaves them blue. I tried copying a section where it worked, but it won’t change. Any suggestions? – Thanks!
@Michaela, Hard to diagnose without seeing the spreadsheet. You can contact me via email and send a copy of the spreadsheet. It should work to copy the entire row where the conditional formatting is working, but if you have lingering messed up conditional formatting rules (due to sorting, inserting/deleting cells without inserting/deleting entire rows, etc.), then fixing the spreadsheet may not be trivial, and depending on how much you have edited your file, it may take less time to start with a new copy of the spreadsheet than to try to fix your file, unless you are familiar with how to clean up conditional formatting rules.
Can I use different WEEKEND days function in the same Gnatt chart?
My project moves from a 5 day week build into a 7 day week operation but I can only get the WEEKEND day string to apply to the whole sheet. Thanks Andrew
@Andrew. If you want to have different tasks use a 7-day week, then use the Calendar Days input instead of the Work Days input. The spreadsheet doesn’t have a built-in way of changing the Work Days option to different types of work weeks for different tasks.
Weekends/ Holidays –
I’m having an issue with the weekend’s and holidays still being used when I’m inserting my work days. I selected Saturday and Sunday to be non workdays in the help tab by selecting 1. Do I need to select something on the Gantt chart tab in order to get it to work? or type in a different formula in the work days?
Thanks in advance.
@Brandon, If you have selected the Saturday/Sunday weekend option, then they should be excluded if you are entering Work Days as the input duration. To diagnose a problem or figure out if you are doing something differently from how it was intended to work, I would need you to send an email and include your file as an attachment and reference the specific part of the worksheet for me to look at. Thanks.
Using the paid Gantt version, I’m unable to add columns for additional descriptors, in my case a column that lists a WBS number from a client schedule. How can I do this? It says in the File Info page of Excel the workbook structure is locked and requires a password. However, the second sheet in the workbook says it is unlocked. can I get the password so I can insert this important column? Thanks in advance for your help!
@Jim, the free version is locked, but the pro version is not (no worksheets in the pro version are locked). My guess is that you are still using the free version. To return to the download page for the pro version (which also provides the password to unlock the free version), see the instructions on the support page.
I downloaded gantt-chart_v4-0.xlsx and I used MacBook OS X El Capitan, Excel for Mac 2011. I did see the column of “Lead”. Can I create it myself? Please help.
@Lin, The “Lead” column is hidden by default in that file. You can unhide columns D and E.
I purchased Gantt Chart Template and I’m starting of use it.
Is it normal that when I choose Display=Weekly then year is not showed but only YYYY?
Line 6 of the template, right part.
Thank you
@Federico, If you are using the gantt chart template in Excel that isn’t using English-language settings, then you may need to update the TEXT formulas or number formatting. The english code for showing the year is YYYY, but in another language it may be something else. For example, I think Spanish uses the code aaaa instead of yyyy (and perhaps Italian does as well). TEXT Function
I’ve manually added arrows on the timeline to show dependencies / predecessors…but can’t get them to lock with the chart when the dates are scrolled. How can I lock them in place with the bars?
@Rick … Sorry, but without using some custom VBA programming, you aren’t going to be able to move lines and arrows that you’ve inserted into the worksheet. So, if you make manual formatting changes or insert arrows and that type of thing, you won’t be able to scroll the chart and have all of your manually entered stuff move with the chart.
I have the Gantt Chart Template 4.0 I’m looking to change the color of the lines that delineate the weeks. They are currently gray and they are hard to see, would like to change them to black. Can someone advise.
Thanks
Stephanie
@Stephanie, To modify the colors used for the conditional formatting, go to Home > Conditional Formatting > Manage Rules and choose This Worksheet from the drop-down to see all the rules used in the worksheet. Alternatively, select a cell where you want to change the conditional format and choose Current Selection to see only the rules that apply to that selected cell.
Even though I have Saturday and Sunday selected as non workdays and in the chart it shows SS in gray for the day. When I put in a number in the Work Days column the chart still shows the task on Saturdays and Sundays?
Also I do not have a box to check to select not to show non work days as shown in the help tutorial?
@Glenn, The chart will show the bar extending over weekends even though weekends might not be included as work days. The point of the bar is to show total duration, not the exact days worked or not worked.
Not all of the files have the checkbox to show/hide work days. You may need to use the gantt-chart.xlsx file if you want that feature.
Hello, Is there anyway to have the calendar just cover one year or 52 weeks? I would like to create separate files for my projects that are on a one year basis. Thank you
@Nikoleta … In this blog post, see the section “How do I print my entire project?” option B. You would need to adjust the # of columns to display just one year.
Hi there, I’m wondering in Pro version, is there a” collapse ” feature in duration/time, which will e.g. automatically update the duration/beginning/end time of a higher level when changing the beginning/end time at the lower level task?
@Elfi, There isn’t an automated feature for that. Instead, for higher level tasks, you can add formulas in the Start and End date columns to calculate the minimum of the start dates via MIN(start_dates) and the maximum of the end dates via MAX(end_dates). This question is addressed in the Help worksheet. You’ll need to add and update those formulas on your own, though. To show/hide sub-tasks you could either use the grouping/outlining feature in Excel or just manually hide/unhide rows.
Hi Jon Wittwer,
I am working in the country of Kuwait where weekends on my job is only Sunday, and holidays I entered on the holiday chart you add to the help is not updating. How can I update the holiday chart after entering Kuwait holidays?
@edwin … Not sure what you mean by “holiday chart.” The newer versions of the gantt chart allow you to define a weekend as just Sunday. I would need to see your file to figure out what you may have done to make the holiday worksheet stop working. Contact me via email. Thanks.
Hi I am using Excel for Mac version 15.23, 2016. And I’m having issues with the workdays not actually excluding the weekends. I have the Weekend set on the Help sheet to 0000011 and on the GanttChart sheet Sat/Sun have the gray backgrounds but when I put in a number of Work Days to 7 it’s giving me 7 calendar days. Example.
Start Date: 3/2/18
7 Work Days
Should create an end date of Mon. 3/12, instead it’s ending on Thurs 3/8.
How do I fix this?
I can send a screen grab if needed. Thanks!
@Laura, If the only thing you edit starting from a brand new worksheet is Start = 3/2/18 and Work Days = 7, then the calculated end date should be 3/12/2018, because the default weekend is set to Saturday/Sunday. You might need to email me screenshots or a copy of your file.
I’ve entered my end date as June 15. However, my calendar along the top will only show up to April 30. How do I see the rest of the months?
@Brenda, listing an end date at the top is only for reference. It doesn’t affect the Gantt chart. See the answer to the question “How do I see/print my entire project?”
I am using gantt chart version 4 ,,
the red color in case of behind schedule were not showing up !! would you please show me how can I fix it
regards
@naief, See the help worksheet to turn on/off that feature.
I’m using version 4. When I enter the percent complete the progress bar color does not change to reflect the percent complete. How do I fix this?
@David, Depending on the file you are using, the progress bar may only show within the progress cell. Use the file gantt-chart_v4-0.xlsx if you want the progress to be shown in the gantt chart rather than in the progress column.
I was using the free Gannt chart but have now updated to the Pro version. I can easily change the colour of the individual bars but the percentage complete does not appear to be working. ie at 50% complete the bar is 100% the required colour, not half grey?
@Scott, The “o365” version does not shade the bar. Use the file gantt-chart_v4-0.xlsx if you want the bars in the gantt chart to show the percent complete.
Creating Gantt chart using Template 4.0 – what I am experiencing is that some of the items are showing the progress (percentage complete) as grey on the timeline bars, others are not showing any progress (no grey) no matter what value is put in for the percentage complete?
@Mark, The colored bars of the gantt chart are created using conditional formatting rules. If these rules get removed or messed up from for some reason, you can select all the cells in a working row of the gantt chart and copy them down. This will copy formulas and formatting, including conditional formatting.
I’ve added rows to my Gantt Chart and everything works fine except on the last few rows the bar only changes colour if %completion is set to 100%. What have I done
@Alex, Sometimes if you don’t insert rows the way the spreadsheet was intended for them to be inserted, the conditional formatting formulas get messed up. You can try copying a row that is working and pasting the formatting and formulas over the rows that aren’t working. Or just copy and paste a working row over the one that isn’t working and then edit the task info.
On the “Time Interval Gantt Chart”, where the increments are measured in minutes, if you have a task that carries over into a new calendar day, but that day is a weekend or holiday that is not a workday, how do you eliminate those unavailable days from being used to calculate end dates?
Thanks,
Ken
We just purchased the pro version. Is there a way to reformat the display of the dates on the chart – to be more like the Free version – where it groups it “week 1”, etc. and not having all the dates listed vertically?
Nancy, You could try one of the other files available on the download page for the pro version, for example gantt-chart_o365_v4-0.xlsx
Question about the colored schedule bars. Every one of my tasks ends on the same day that the next task starts. I need the next task to start on the next weekday following the end date of the previous. I see the WORKDAY +1 instructions in the comment section. I hesitate to simply replace the formulas in columns O and P because they are so elaborate and complicated. Maybe I need to use the WBS functionality. I think that is more detail that I want to enter. My needs are relatively basic. Thanks in advance.
@Dean … The way the formulas are set up, the tasks should already be starting on the next weekday following the end date of the previous task, if you are using the Predecessor column to create dependencies. You don’t need to change the formulas in the start/end columns. Instead, if you want to create your own dependency formulas, you can enter a formula within the green (input) Start column using the WORKDAY or WORKDAY.INTL functions or just =end_date+1.
I appreciate the help but I am really struggling with this one. Obviously my basic Excel skills are not enough to manage this spreadsheet. I tried the WORKDAY function but couldn’t get it to work properly. I tried using the end_date +1 (column P) but it then used the weekend days in it date calculation.
I really only want the next row’s start date in I to advance to the next workday following the end date in P. For example. I enter 3-4-19 in I12. I enter 10 working days in K12. P12 shows an End date of 3-15-19. How do I get I13 to show a date of 3-18-19? The spreadsheet would continue in this fashion as I add job after job with a new Task name and an estimated number of work days in column K. I’m sure this is ridiculously simple but I can’t seem to get it right. Thank you again.
@Dean. A couple of ways. Method 1) Using the Predecessor column: Enter =B12 in cell F13. This will automatically calculate the Start date in cell O13 to be 3/18/2019. Method 2) Enter your own formula into cell I13: Enter =WORKDAY(P12,1) into cell I13. This will calculate the Start day as 3/18/2019. Alternatively, use =WORKDAY.INTL(P12,1,weekend,holidays) if you are using a weekend other than Sat/Sun as defined in the Help worksheet and if you want to exclude holidays that are listed in the Holidays worksheet.
After getting row 13 to work, insert a bunch of rows below row 13, then select row 13 and use the drag handle to copy row 13 down to fill in the newly inserted blank rows. This will copy the formulas and you can adjust the task descriptions and durations. Hope that helps.
Hello, i could not figure out how to see entire chart on secreen. ?
@Emre – See the section about adding more columns to the gantt chart
Good morning,
I am just beginning to use your program Gantt Chart Pro on my desktop. I am wondering if it has the capability to link tasks to risks and issues? Please let me know.
Thank you!
@Ali … “risks and issues” is not a part of any of my templates … so that would be something you would have to do on your own, or perhaps with the help of an Excel consultant to create something for you.