◄ 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
Hi. I can’t add a single new row – no matter how I try it, I get the message, “You can’t change part of an array.” I feel I’ve missed something obvious here.
Are you talking about Gantt Chart Pro? I don’t think there are array formulas in that template. Inserting rows is how it’s meant to work.
My date range is 10/19/22-7/19/23 and I have UNHID the whole chart and I am not seeing my graph display my bar past January 2023. Dis I mess up something? What columns formulate that so that I can make sure that I did not mess up the formula?
@Charlotte, I can’t really answer that without seeing your file and looking at what you’ve done to edit it. Visit the contact page for the email address. You can email a copy of the file.
Hi, the nature of my project requires gaps in between the same task. Is there anyway for me to input the different date ranges and show them in the same row?
Or perhaps is there a formula that I can use for one row that only highlights the cells that are colored 2-3 rows under it as a summary?
There isn’t currently a feature built in that allows gaps in a bar within one row. The summary row would show a single bar as well (from minimum to maximum date).
I just purchased you Debt Reduction Calculator 40, input everything but the interest column does not populate showing #NA as the value. Please lmk what to do to correct.
@kim … Please email me a copy of the file (see the “contact us” page) so I can diagnose the issue, if you haven’t figured it out already. Thanks.
I have noticed in the free version the minimum duration is 1 day, partials are not accepted. How about the pro? Can I put 1.5 days for example?
No. Days only, because the NETWORKDAYS and WORKDAY functions in Excel do not work with hours.
First of all, this spreadsheet is great! I’m still learning how to use it, but I wanted to ensure all of the holidays are correct. You have US and UK holidays, but I need Canadian holidays.
I’ve tried to edit the dates in the list, but the formula is a little advanced for me. Is there any way you could help me with a list of Canadian (BC) stat holidays?
Thanks!
For now, other holidays would just need to be added by entering the dates manually into the list. See the perpetual calendar page for a few formulas that may help define certain types of holidays.
Hello,
Is it possible to stack the differnet colored bars onto one? My projects have phases and each phase has a color. But I need a multi project view, one project per row with the different colored bars corresponding to my phases.
Is this possible?
@Manuel, This would require doing something custom and is not a feature built into the template. You’d need multiple columns to define the dates for the different phases and then new conditional formatting rules to display the bars associated with those date ranges. Possible, with significant customization. See https://www.vertex42.com/excel-consulting.html for consultants that may be available to help with customization projects.
I was adding rows to the chart, did insert new, then ctrl d and worked all the way down to row 33 then it doesn’t show the blue bars on weeks off to the right. What am i doing wrong here?
I have figured out it has something to do with the predecessors, column, When I copy the previous formulas, it works until I update the predecessor.
Is there a way in to have a 1-day task repeat once a week or once a month and show that on one row?
Using the google sheet version.
Thank you in advance for any pointers!
I might provide an example formula for that at some point, but it’s not really how the gantt chart is meant to be used. The older version (version 4) has an example of this type of custom repeated task at the bottom of the table (sometimes it’s a hidden row), but it is so different from the way the rest of the gantt chart works, that it doesn’t really fit well. Maybe you could put a task like that at the very top of the gantt chart or at the bottom, but if you have rows like that in the middle of your other types of tasks, it could really mess things up because the formulas would be so drastically different that you can’t just copy down to fill in columns.
Hi there,
I tried to group colones. In the gantt diagram, I would like to group colones by months. The problem is when I group for exemple January in one group and after I try to group the febuary, the excel combines the two months together.. Someone can help me ?
It depends on the view you are using, and in the Pro version some cells are merged for some labels. The problem with grouping columns will be that some months have a different number of days, and the gantt chart is currently set up in groups of 7 columns (7 days per week). Unless you customize the labels to behave differently, then you’ll need to work within the constraints of the current design.
Hi,
My question if for the gantt diagramm, I would like to delete the colon of the weekend for all my driagramm. It is possible ?
If you delete columns, that could mess things up, but you could hide the weekend columns.
Hi, great template, I love it! I’d like to remove the yellow notes that describe each heading e.g. in the cells for ‘Task’ ‘Assign To’ etc. They seem to be embedded/locked, but I find it quite annoying that they pop up each time I’m on the cell. Can you please offer instructions on how to delete these notes? Thank you!
In Excel, those yellow notes are created using Data Validation. To remove them, click on the cell and go to Data > Data Validation, then the Input Message tab and uncheck “Show input message when cell is selected”.
I’m using the Gantt chart pro for office 365 (the newest version) and I really like it. But I do have some planned overtime in the weekends, can I make those weekends show up as work days for said overtime without changing all of the weekends to workdays? When I try to do this I have to mark all of the other weekends as Holidays, which makes it a very confusing list.
There isn’t a way to provide the WORKDAY and NETWORKDAYS functions with exceptions to the weekend option. If you want some weekends to be work days, you’d need to first make all weekends work days, and then add all of the non-working days to the list of Holidays. Alternatives might be to get ahead of schedule or reduce the expected duration due to your planned overtime.
I have used the template of grantt chart, there is one problem left is that I want to add text to the chart plain where the progress of the taks apears in horizantal bars-like. I need to fill in some text, short sentences in the bars,, it works, but when I change the displayed weeks, these added text don’t move when that weeks are shifiting, and I don’t know if it is possible to add a text where it shift when changing the weeks.
Nope, if you enter text manually into cells in the Gantt chart area, that text will not move when you change the display range. Almost anything is “possible” if you add enough VBA code, but it’s certainly not practical with this style of Gantt chart. Using a stacked bar chart for the Gantt chart would allow you to display data labels, but this template does not do that.
I have converted the v5-0 Gantt Pro for use in Google sheets but the milestone diamond is not showing up after selecting “M” fro the dropdown in the color column (N). I have looked at the conditional formatting menu for the chart area but can only see how you could change the color or text format. I can’t see where you would specify the character for the black diamond or enter any such value. Everything else seems to be working just fine. Any help much appreciated.
The diamond character is created using a conditional formatting rule that changes the text formatting. For that to work, there must be text in the cell. That is why all of the cells contain a formula that display a space. It is possible that when inserting a row, if you didn’t copy all cells down into the new blank row, the formulas in the gantt chart may not have been copied down. You can fix that by copying down from another row where the CF rule is working.
Hi, how do I remove the notes from the excel?
Notes are removed by right-clicking on the cell and selecting Delete Note or Delete Comment. If the note is created via Data Validation, then it is removed by going to clearing the Data Validation within the cell.
My gantt duration needs to extend beyond a few months. How do I add more to the right of the gantt?
See the last section of this blog post about how to add more columns.
I just purchased Gantt Chart Template Pro and would like to update the quarterly view Q# display to coincide with a fiscal year starting 6/1 but am unsure how to do that.
You’d have to change the formula that displays “QX” in row 12. If June 1st is the start of quarter 1, then you could try changing the following portion of the formula in cell X12:
Original: “Q”&INT((MONTH(X5)-1)/3+1)
Change to: “Q”&CHOOSE(MONTH(X5),3,3,4,4,4,1,1,1,2,2,2,3)
Then copy that cell to the right to update the other formulas in that row.
I go the Gantt Pro and I really like it.
I have identified Sat-Sun as weekend but the Start Date still includes Sat or Sun. What could be wrong?
When entering dates manually as inputs, you’ll need to avoid entering weekend dates. If you are using ‘work days’ to calculate the duration, then weekend days should be skipped. If you are still having issues, please contact me with more specific information.
Is there a way to get rid of the dynamic scroll bar? Or have it display more than 8 weeks at a time? We are wanting to create a gantt chart for our construction and it would be helpful to see the full chart / bars of the entire time period as an overview and not only always just an extract of 8 weeks at a time. Your help would be highly appreciated! Thank you
See the last section of this blog post about adding columns to the Gantt chart.
Im liking this up to now! Ive modified it quite a bit to fit my needs since im using it as an overview of all our projects including their main tasks. So my first column (B) is the project number. The cell of the project number is merged with as many rows as there are tasks in the project. I would like to make a conditionnal formating that considers every row that is included in the merged cell of the project number. For example My project number is a merged cell of B10 up to B20. I have individuals tasks for every row in C collumn. I would like the merged cell (B10 up to B20) to have conditionnal formating marking it red whenever at least one task is late in the corresponding rows (C10 to C20) . My goal is for the overall big project cell to display the whole project as late when at least one task is late. When i try any formula for this to work it either only works with the row 10 (if the row 10 is late it marks red the merged cell (B10:B20) but it doesnt factor in the rows 11 to 20. Other formulas that I tried simply wont work.
Hi Kevin, I’m not sure if it’s possible to detect with a formula whether cells are merged (or how many are merged). Perhaps with a fancy use of the COUNTA function to determine how many values, but then how do you know which cells to count? You might need to contact ExcelRescue.net to see if they could do some customization for you.
With enough helper columns and formulas, it may be possible to eventually do what you want, but I can’t think of an easy solution.
Hi, new to these charts but is there a way to have the bars not fill in across the weekends, so for example if we have a 2 day task that starts on friday, it fills in Fri/sat/sun/mon
It’s possible to set up a conditional formatting rule to essentially block out the weekends. Currently, in the Pro version, that rule is listed AFTER the others (so the weekends are highlighted, but the bars look like they are on top), but you could move that rule up the list so that it takes precedence over the bar colors.
Hello:
I’m a happy user of v4.0 version of Gantt Chart Pro since 2019. You mention free updates for those who buy v5.0 which made me curious if there is upgrade pricing from 4.0 –> v5.0.
Thanks,
Hubert
No need to repurchase. You just need your order/receipt #.
Hi,
I am using your gantt chart with great results. Now I would like to connect it to an app I am developing and the IT engineer is asking me if we can get the JSON mapping. I don’t know what it is…
let me know
best regards
I’m sorry. I don’t know what the JSON mapping is, either.
Good day, its my first time trying the template and i got stuck trying to make the over due tasks to color red automatically if the today date is lapsed Please i tried everything including the formuales i see in you guide but its not working
Did you enable the related setting in the Help worksheet?