◄ 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’m a Pro customer. Is there a way to change the start day of each week to Sunday instead of Monday? My goal is to see the dates for Sundays when I look at the Weekly view instead of the dates for Mondays. I use this in a church context so summarizing to Sunday is important since most of our critical execution falls on Sundays.
@Toby, In the gantt-chart.xlsx and gantt-chart.xls files you can unhide the first two rows and change the cell with the comment labeled “Start of Work Week” to 1. I think it’s cell M1. This is an undocumented option.
Thanks for responding so quickly. In the Office 365 sheet, the first 2 rows aren’t hidden and M1 is a blank cell, but I found this formula in cell L4:
=IF(G5=”Monthly”,EDATE(DATE(YEAR(F4),MONTH(F4),1),F5-1),F4-WEEKDAY(F4,1)+2+7*(F5-1))
In the WEEKDAY (F4,1) portion, I changed the 1 to a 16 and that seemed to do the trick. Will that change mess up anything elsewhere in the sheet that I should look out for? If not, I think I’m all set.
@Toby … I don’t think the change you made should mess up anything else.
I have been playing around with the free version and am considering purchasing the Pro version.
This will be a very helpful tool to track projects.
But we can have several different projects going at once. Is there a feature on the pro version that allows you to view a “Master sheet” that shows you progress of all your projects?
@Dwayne, The short answer is no, but you could use one copy of the gantt chart spreadsheet to serve as a master sheet and then use cell references to refer to other worksheets. There is nothing that automates this for you, but if you are familiar with Excel, it might be something you could set up yourself.
Hi,
I am wondering if you can sort the chart, so that the end date is viewed chronologically rather than the start date?
@Lisa, Because of the way the WBS numbers are calculated, if you change the order of the tasks, the WBS numbers will be changed. So, if you want to sort, you would need to enter all the WBS numbers manually first.
So there is no way to sort without upsetting the formulas? as our preference is to view the end date chronologically, however still maintain the automated nature of the spread sheet.
@Lisa, No. Not with the WBS formulas as they are now. Allowing sorting would require using different WBS formulas that would get messed up if you added and deleted rows. I came up with the current WBS formulas specifically to allow insertion of new tasks without having to update all the WBS numbers.
The shading of the “work days” is not appearing in some cells? I’m a novice and can’t see where the formula[s] might be wrong.
Thank you.
@Jane, Please email me and send screenshots and a copy of your workbook or something to help clarify what you are referring to. Thanks.
Hi,
Gantt 365, set on weekly. I would like to change the week numbers from 1-7 to individual ie 1234567
Please advise how this can be done. I must say I’m very impressed with the pro templates!
@Mark, That would require customization of the template. You may be able to alter the formula in the row that normally shows “M”, “Tu”, “W” for the daily view to show week numbers instead. For assistance in customization, you can get a quote from an Excel consultant: https://www.vertex42.com/excel-consulting.html
Hello,
I just recently purchased the Pro version of the Gantt Chart and was wondering if there is a way to change the formatting of the main task to coincide with the subtasks different colors.
For instance:
Task 1 – Color is set at G for green
Subtask 1.1 – Color is set at O for orange
Subtask 1.2 – Color is set at Y for yellow
Because the start dates happen after the end dates of each task I would like to see the color change on the Main task line. So it would show Green for entire task and Orange will fill in over the Green on the start date thru the end date of the subtask and then Yellow will follow suit.
Is this possible and if so how would I do it.
This would come in handy when using the grouping of the tasks and seeing what phase of the task it is really on.
Thank you
@Zed, Breaking a single bar into multiple colors is not something built into the gantt chart template. You could use an IF() function within the Color column to change the color code, but this would change the entire bar. If you created your own conditional formatting rules, you may be able to do what you are asking. You may want to request a quote from an Excel consultant if you need help creating a custom solution.
I’m using free Gantt Chart which file name is Gantt-Chart_0365_free(Project schedule).xlsx.
I’ve a question.
Can I mark off day by Red color or blank in the chart, because sunday or national holliday should be blank or different color?
really thank for sharing nice Gantt chart.
In theory, you can do whatever Excel allows you to do. If you manually change the color of a cell and then change the display range, the color will stay put, so manually marking a holiday is not advised. It would be possible to add a conditional formatting rule to that holidays were highlighted in the label. The pro version contains a list of holidays that you can edit, and the formula for checking whether a date is in that range would be =NOT(ISERROR(MATCH(the_date,holidays,0))) where “the_date” is a reference to a date and “holidays” is the named range referring to the list of holiday dates. If you want to do this in the free version, customizing the spreadsheet is something you’d have to do on your own.
Hi Jon
I would like to enter a subtask, ie a 1.1.1 task but I do not know how to do this at all. Let alone quickly. Any advice for this layman would be greatly appreciated.
Many thanks
Redmond
@Redmond, You can just copy and paste one of the cells that contains the formula for a 1.1.1 task.
We want to use Vertex42 to schedule Manufacturing on a daily basis. Need to Schedule Minutes. Start time is 7:00 AM end at 3:30 PM. We build multiple products on multiple manufacturing lines. I saw a screen shot of such an approach but cannot figure out how to set it up ad the days, weeks, months seem to be hard wired.
Thank you for your assistance.
Linda
@Linda, The file that is mentioned for the gantt chart that works with times is a completely separate download. You can return to the download page via the instructions here. If that file doesn’t work for you, contact me via email to try out an alternate solution.
Great product.
I’ve added tasks and in some places the Gantt line has shifted up a row.
I looked in the formula and it seems to ref the correct row.
What can I do to fix the Gantt lines to be back in sync with the task row?
@Brenda, You could try deleting cells in the optimal place to fix the problem. It sounds like the problem may have come from not inserting an entire row (inserting only a select number of cells which then shifted only those cells down).
I have a master document that I have copied both the Gantt Plan and the Holidays sheets into – each on its own worksheet. I have my plan set to weekly calculations. Even though I pasted the entire chart into a clean worksheet in my master document and put in actual dates (e.g. 02/15/15), the dates in the weekly column headers change to 00-JAN-00. Please advise what formula I need to change in order to prevent this from happening and for the plan to reflect the actual weekly headers. Thank you.
@Lisa, The template was not designed to be copied into another workbook. It would probably be easier to copy your worksheet into the gantt chart spreadsheet.
That makes sense! Thank you!
Can I have multiple milestones represented on one line within the Gantt chart widget?
@Michael, “Can” may not be the right way to ask, because you can do almost anything with Excel if you know enough about it to create your own formulas and stuff. However, The gantt chart template does not currently have something built in to show multiple milestones on a single row. If using the daily view, the conditional formatting formula could be a lookup formula based on a list of milestone dates, but for the weekly or monthly views, the formula would be significantly more complicated. You could manually change the background color of a cell to show multiple milestones (as long as you don’t change the daily/weekly/monthly view or change the date range).
Even if you did display multiple milestones on a single row, you’d need a way to label them. So, even though it is theoretically possible to do what you are asking, I think I’d still recommend displaying milestones on separate rows so that the dates and descriptions were clear. Having multiple milestones on the same row would also create a problem if you were using dependencies.
Hello,
I am having difficulty finding a procedure to show key dates (as reminders) on Gantt Chart Template Pro. I would like to see a date highlighted as a reminder on the chart, for example, to prompt me to order products in time for delivery deadlines. Is there such a feature? Any help would be much appreciated
Thank you!
@Jonathan, You could try using Conditional Formatting to highlight dates based on some criteria you set, but you’d either need to learn how to do that yourself, or find an Excel consultant to help you. That is not a feature already built into the template.
2015/08/23
I just bought Gantt chart pro and I realize the standard days calculation does not take in charge the weekends. it’s a bit confusing because some of my projects have to considere the weekends. is-it a way to easyly change that situation ?
regards.
@Richard … If you need to include the weekends, then use the template rows that use calendar days rather than work days as an input. The demo video shows how to use the template rows. If you are using one of the versions that allows you to define the work week (gantt-chart_intl.xlsx or gantt-chart_o365.xlsx) then you can just redefine the work week to include or exclude weekends. See the Help worksheet in those files. The Help worksheet also explains how to use the different template rows.
Why isn’t filter working on the Lead column? How can I get this to work?
@blubell, if you want to use filtering, you’ll need to select the appropriate range of cells and then go to the Data tab and turn on the Filter. By the way, I would not recommend using Sorting with this worksheet unless you are using manual WBS numbering and are familiar with Excel enough to fix errors that sorting might cause.
Hi,
I have just purchase the Pro version and I have a little display problem. In the beta version the weekend and holidays are not displayed as in the ganttchart sheet. I just want to shadow the weekends and holidays.
Thanks
@Nicolas, You’ll need to email me with more info as I don’t quite know what you mean. See the About/Contact page for the contact email.
Hi, I’ve downloaded the Gatt sample template and also paid for the Pro version. As I have already populated the sample template, I just unlocked and wanted to keep using. My problem is that it doesn’t have the colour coding 1-6 as the Pro version. Can you please advise how to unlock this as I am having difficulty doing it without re populating Pro, which I don’t want to do.
Sorry, I am not that good at excel.
@Phillip, “Unlocking” means using the password to unprotect the worksheet(s) … it doesn’t change the functionality beyond making all the features of Excel available for that worksheet. If you want to use the features of the Pro version like color coding, you’ll need to download the separate Pro files. You will indeed need to re-populate the files with your data if you want to use the pro files. Some copy/pasting using Paste Special > Values may make the process go a little faster, though. To return to the download page, see the instructions here.
Hello,
Apologies if this has been asked. I’ve searched for the answer to no avail. I am a pro customer.
I have tasks categorized and listed above a section of resources within the same Gantt chart. I need to assign these resources to multiple tasks which means the horizontal resource line will ultimately show many color code bars associated with the above tasks. How do I create multiple color code bars on one row?
Thank you.
Jason
@Jason, I’m not sure how you are assigning the resources to separate tasks. What you are describing might be done with some clever use of array formulas or the SUMPRODUCT function for handling multiple criteria, but this could get complicated quickly (I have tried something like this before). You may contact me via email and attach your worksheet for clarification, but I may need to just refer you to an Excel consultant if you need help customizing your spreadsheet.
Hello,
Great work and thank you for the gantt chart pro spreadsheet.
I have a couple of questions regarding timing that I am hoping you could please advise on:
1. How I can mark/distinguish between a task actual completion date to a deadline.
2. The best way to work backward from a task deadline to ensure previous tasks are completed by the required time.
Kindest Regards
Hi there, last week I bought Gantt Chart Pro. Do you have any template where I can have Gantt by hours including calendar working hours. i.e Working hours are from 7:00am to 2:00pm and from 3:00pm to 7:00pm.
Thanks
Ruymán Márquez
@ruymanmd. Yes, and no. See Bonus File #4. Contact me via email if you want to try a more complicated gantt chart that uses working hours like 7:00am to 2:00pm.
When I’m in weekly display view (using the drop-down), I want to have the weeks span like, Week 1-3, 4-6, 7-9, 10-12,13-15,16-18,19-21,22-24
How do I update the formula?
@Dominic, if you are talking about gantt-chart_o365.xlsx, then I’m afraid you’re stuck with 7 week spans because in order to provide the daily view option, the columns are grouped into 7. The gantt-chart.xlsx has similar issues. The change you are requesting would require a significant change to the spreadsheet, not just a modification of a couple formulas.
Thanks for a great, affordable product! What a helpful tool for project planning. How do I fix conditional formatting for the task bar completion? Somehow on some rows the gray indicated percent completed stopped working. I’ve checked the Start Date is correct, as suggested in the help tab. The color for these particular rows is not working, either. But a status bar does show up, just in original blue instead of the orange I have indicated with an o in the color column. Help, please! Thanks!
@Stephanie, Hard to tell without seeing a copy of the file. However, you could try finding a row that is working correctly and copy all the cells that make up the gantt chart area for that row and paste them into the row that isn’t working. The gantt chart can get messed up if cells are deleted or inserted without inserting/deleting entire rows. It can also get messed up if CUT/paste is used instead of copy/paste.
[Update for gantt-chart_o365.xlsx] – I’ve had a few requests from users that have led to some minor additions to this version of the gantt chart:
1. Project Completion Date: I added a row to display this in the header, but the row is hidden by default. The formula for this date is just the maximum of the dates in the End Date column. You could enter this date manually if you want to.
2. Define Tasks Using End Date and Duration: Some people have wanted to define tasks working backward from a milestone date. I added two new template rows that allow you to enter an End Date for a task and a duration (Calendar Days or Work Days). The Start date for the task is calculated.
3. Display a Milestone as a Diamond: If you like to use diamonds for Milestones, and your computer/device can display the Wingdings font, then you can try the new milestone template row.
4. Daily View Shows Day of Month: In the daily view, instead of just the day of the week (M,T,W,etc.), the column headers display the day of the month.
5. Recurring Task Every N Days: A common request is to be able to display a recurring task on the same row (like a biweekly meeting). However, that doesn’t allow you to create dependencies to specific occurrences of the task. I added a hidden template row that you can play with if you want to try displaying a recurring task that can be defined as starting on Start Date and ending on End Date and repeating every N calendar days.
I have purchased the Pro version and having trouble finding the hidden template row to enable me to create recurring tasks. Can you show me where the hidden template is located?
@John, if it is one of the versions that has the hidden row, it would be at the bottom of the table.
hello,
I have purchased the gantt chart and using it successfully. Is there a way to link the status of each project to a summary/tracking cover sheet?
@Amanda, Probably. When you create a summary sheet, you can reference the status cell. How you do that is up to you. You could also do the reverse by using a formula in the status cell to reference a cell in a summary sheet.
[This comment applies to the CPM spreadsheet, not the gantt chart template]
Hi .. I have recently bought a ” package from you”. and have set up Critical path programs in CP Analysis program… but the program is in days and I want to display in weeks and months. is this possible? If I click help, it takes me back to site saying purchase of $ 14.95. Did I not buy it already? and if not no problems will do so if this program can be displayed how I want it?
Please give me direct email helpline address. so that I can show my problem.
Regards Guy Johnstone
[This comment applies to the CPM spreadsheet, not the gantt chart template]
@Guy … The short answer is “no, there is not a function in the CPM spreadsheet for automatically changing from days to weeks or months.” If there was a simple way to customize the spreadsheet to automatically switch between days and weeks and months, I’d say how to do that, but there is not. The gantt chart template has the functionality to change between displaying days/weeks/months, but it does not do a critical path analysis, and is substantially different from the CPM spreadsheet.
The help link takes you back to the CPM web page which has a section lower on the page describing how to use the spreadsheet. It is not to suggest that you need to pay for technical support. For general Excel help, see the support page. The email to contact me can be found on the About page. If you need extensive technical support, we do not provide that type of service, but you can request a refund.