◄ 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 just purchased Gantt Chart Pro and am very impressed.
I have started using the 2.3 beta and am having trouble with the bar colours.
The help file states that codes 1 to 6 are theme based colours, but they do not change to my selected theme. On inspecting the conditional formatting, I see that it does go someway to choosing my colours, but does not select the main one, rather the mid-grade colour that can be chosen as variations on the main shade.
Are you able to help me be able to select the main theme colours instead?
@Dan … if you start with a fresh file (so that the conditional formatting isn’t messed up), in Excel 2010 you can go to Home > Conditional Formatting > Manage Rules, and then select “This Worksheet” from the drop-down box. That will brink up all the formats for the worksheet and you can go through and edit the colors to your heart’s content.
Hi,
Can I add columns in this template and get the averages in all columns or only in the ones which it comes in – generic forms only with no adjustments for additional columns?
I’m doing my taxes and would like to use this every month the rest of my life, but i need to see the monthly averages for my own custom consumer spending.
Thanks for answering me in advance,
Tom Berk Seattle Wa
@Tom … I’m confused … what does your question have to do with the gantt chart template? taxes? consumer spending? Sounds like budget-related stuff rather than project planning.
I am using beta 2.3 of the rtemplate in Excel 2010/2013. How do we add a third or fourth level to a project? There are only blank examples at the bottom for label only at these levels.
The cells containing the WBS level can be copied/pasted without having to copy/paste the entire row. The WBS formula is independent of cells in other columns. The indenting of the Task description for various WBS levels can be done via cell formatting.
I would like to put an “end date” so that my project does not contiue way out past deadline. I cannot figure out how to limit the number of columns (Excel 2007)
@Shan … To specify an end date for a task, use one of the template rows where the end date column is highlighted green. Then, you can enter the start and end dates for a task rather than a start date and duration.
I am impressed with this spread sheet but why there are some features from 2.1 version not included in latest version? I love to use the hide/unhide feature at the side which is quick for me since i am dealing a lot of operation in one sheet
@Hector … you can still use grouping if you want to. It’s a standard built-in Excel feature found in the Data tab (in Excel 2010).
Jon – many thanks for your earlier reply about colours.
I have been using the template very successfully for the last couple of months and must thank you for your excellent work.
I have a couple of ideas in case you have time to develop the template further:
1. Adapt the y-axis date title to show full months instead of weeks (or both together). I realise this is complex, but will help people get their eye in quickly when looking at a full year. It would also be good to have a slightly bolder grey vertical grid-line at each month.
2. Make some kind of tag that will place a bold red line underneath a bar to denote critical path. This would be manually generated.
3. An option to duplicate the task title in the coloured box (or immediately following it). I find this helps people read the chart and I have been adding them manually (or supplemental comments).
Lastly – I think I am on version 2.3 beta. How can I get the latest version?
@Dan,
Last question first: Version 2.3 (beta) is still the latest version. To download the latest versions, see the support page for how to log into clickbank.com to get back to the download page using your receipt and email.
1. Noted
2. This could be done with the XLSX version by adding another conditional formatting condition based on a new column (similar to how the Color column works), though I’m not sure yet whether I’ll work that into a future version or not. The critical path analysis would complicate the gantt chart template too much (without VBA), though I have a spreadsheet that demonstrates the method: https://www.vertex42.com/ExcelTemplates/critical-path-method.html
3. I don’t know how to do that without VBA. Adding formulas to all of the cells to display the Task title within the chart would not work, because the title will not overlap other cells if they contain formulas.
Thanks for the suggestions.
Thanks again for the speedy reply Jon.
I will have a look into the conditional formatting and see if I can come up with something for the critical path. I wasn’t suggesting an automated analysis – I just wanted to be able to highlight some bars in my Gantt. I agree that adding formulas to all of the cells would complicate the spreadsheet – I can certainly make do as is – I am just annotating, really.
After writing yesterday, I had a few other thoughts:
1. I started representing milestones as single day activities and then moved on to overlaying annotated triangles on the bars. This was pretty successful, but I wonder if there’s a way to have multiple single day bars on one line. I realise this would mean multiple date inputs and hence I think it’s not really feasible. It maybe worth thinking about how to represent project milestones, though.
2. The overall line for a group of sub-tasks might be best represented as a thinned black line. I think this might be another conditional formatting solution that I could put together.
3. Would it be worth changing the conditional formatting of the red ‘today’ line so that it overrides the grey grid-line? This would avoid the stripy effect.
4. My critical path idea from my previous message could also be used to show planned vs actual, but again would require more date inputs.
Hi Dan,
Re: Milestones on a single line (Not Easy) – Probably the best way to include single-day milestones on a single line would be to define a list somewhere of the milestone dates and then create a row with a unique conditional format condition that would fill the cell some color if the date associated with that cell was found in the list. The formula for the conditional format might be something like =NOT(ISERROR(MATCH(P$9,milestones,0))) where “milestones” was a named range referencing the list of dates. I’ve done this in a test file and it works well (for the daily/weekly views). If you’d like me to send you a copy, email me.
Re: “today” line less “stripy” – This will be integrated in the next update – just required adding red borders to the conditional format condition to override the grey gridline, in case you want to do that yourself.
Jon I was looking for the milestones on a single line. Actually I wanted to show an audit process for a project on the line below the project. Currently I have an individual line for each date but if you could email me the what you worked on for this I would adapt it for my needs. Any help is appreciated. Thanks.
@Joe … Email me if you want me to send you an example. However, if you want to implement this in the file that you are using, you’ll need to either figure it out from the example (and learn about conditional formatting and lookup functions and named ranges) or get a quote from an Excel consultant to help you.
@Jon
Thanks again!
BTW – I was not getting email notification of your reply, maybe because each message was a new one – hence the delay
For some reason the red line disappeared, and I do not have problems with any of the three possible solutions listed above. Any other suggestions?
@Melinda, no additional suggestions. You’d need to send a copy of the file via email so that we could diagnose the problem.
Where can I email it?
@Melinda … See the following page: https://www.vertex42.com/about.html
I put all the information in and using Insert to add lines. the formula appear in each block but it does not plot the timelines? must have done something incorrectly .
Any help is appreciated
@Ron … Insert of inserting blank rows, you need to copy an existing row (from the set of template rows for example) and insert the copied row.
I have converted the Weekly format into the hourly format.
i.e. from 1 Jan 2013 – 7 Jan 2013 – 14 Jan 2013….. To 1-1-2013 12AM – 1-1-2013 8AM – 1-1-2013 4PM – 1-2-2013 12AM….
All things are going well
Except, the total duration part which is represented by gray color is lagging exactly 24 Hours (1 Day).
i. e. if start time is 1-4-13 8:42 AM and end time is 1-12-13 11:00 AM
then gray color starts at 1-4-13 8:42 AM & ends at 1-11-13 11:00 AM instead of 1-12-13 11:00 AM.
Thanks You….
Hello Rahul,
You would need to email me a copy of your file for me to look at. Is there a reason why you didn’t just start with the bonus file that is already set up to handle an hourly format?
Thanks
Hello Jon,
I have added some columns into the template.
After that the today,s Date line (Red color) is not displaying after some columns.
Thanks You….
@Rahul, sounds like the conditional formatting conditions need to be updated. You should be able to find a cell within the chart that works and then copy that cell across and down.
Is there a way to change to a 4 day work week? My department works 4 ten hours days with Friday being the off day.
@Pat … Possible, yes. The new WORKDAY.INTL and NETWORKDAYS.INTL functions in Excel 2010+ make it possible to say exactly what days should be considered “weekends” but the template(s) do not currently use those functions.
Work great, although need some time to get familiar with Gantt chart pro (Since i need to change to format to suit my preference). I found that it is even easier to use than MS Project. Latest version of MS project made things too complicated.
btw, i have to following questions
1. How to add a blank row without affect the WBS numbering? sometimes i want to make a remark right after a task
2. How to Shorten the width of the work calender?
3. Is it possible to have a special icon for Milestones
Thanks~
3. The conditional formatting used in the gantt chart area cannot display arbitrary icons, but milestones could be represented as a particular color (if you are using the XLSX version), or you could edit the conditional formatting of one of the color conditions so that a border was added in addition to the color.
2. Work calendar? I don’t know what that is referring to.
1. You could either manually enter the WBS after a blank row, or the formula could be edited by changing the “-1” within every OFFSET() function in the formula to “-2” so that it refers to the cell two rows above the current one rather than one row above.
3. Can you add the ‘white’ color’ for me to manually enter a text on the milestone? I use Wingdings ‘u’ as the icon. but the format make the text look odd~ btw, i am now checking if the workday=0, the bar color change to black
2. i solve it by hide the columns
1. Thanks, manual enter the WBS work great and easy~
btw, is it possible to have one more vertical line? i want to check the task against a deadline~
now i do it by altering the today date~
Kenneth … Using the Excel 2007+ version (XLSX), it would be possible to add more than one vertical line, by adding another conditional formatting rule. You can change the color formats for the conditional formatting rules if you want to customize the spreadsheet for your use. Go to Home > Conditional Formatting > Manage Rules and select “This Worksheet” from the drop-down box to edit the rules.
Is it possible to make the Monthly time sheet always begin with Sat. but still add the dates automatically and to subtotal the weeks from Sat-Sun. for OT purposes?
Possible? Probably. I’m guessing the follow-up question will be “how”. But, this question is unrelated to the Gantt Chart template. Please email me if you need help customizing the time sheet.
I need to add Saturdays as a work day in my Gantt Chart, right now it does not include it as a work day but rather a weekend day.
Thanks in advance
@Joe, If you are using Excel 2010 or 2013, it would be possible to replace the WORKDAY() and NETWORKDAYS() functions with the WORKDAY.INTL() and NETWORKDAYS.INTL() functions so that you can specify which days of the week to include as work days. There are many places that those functions are used in the template. You can look up these new functions in the Excel Help system. You might try using Find/Replace to replace all “WORKDAY(” with “WORKDAY.INTL(” and all “NETWORKDAYS(” with “NETWORKDAYS.INTL(” and all “,holidays)” with “,11,holidays)”
Is there a way to establish a milestone line for each phase of the project plan? So that everything is with say WBS 1…… has the phase deadline .
Many thanks
Ian
@Ian, If using the .xlsx version … For a milestone, just use one of the template rows that lets you choose the Start and End dates and set the color code to “k” for black. If that doesn’t answer your question, you will need to email me with more specifics.
I just bought Gantt Chart Pro and I think its amazing. I just have a question, can I modify the workdays so it includes Saturdays but not Sundays?
Thank you.
Patricia
@Patricia, Yes. See Joe’s comment. I’ve created a version that uses NETWORKDAYS.INTL and WORKDAY.INTL, but for now I’m just emailing it to people who need it. [Comment Updated] – The Pro version files gantt-chart_o365.xlsx and gantt-chart_intl.xlsx both use these new functions now. See the Help worksheet within those files.
I am wondering if there is a way in the Gantt Chart Dailyto use a 7-day work week. RIght now it only displays Monday – Friday, even if I insert calendar day rows.
-Jason
@Jason … make sure the “Show Weekends” checkbox is checked.
I am guessing that the answer is going to be embarrassingly obvious but…..where do I find the “Show Weekends” checkbox?
Take a look at the screenshots shown in the following article. You’ll see the “Show Weekends” checkbox in those screenshots. If you aren’t seeing the checkboxes in the excel files, then either something very weird is going on, or you aren’t using the updated versions.
https://www.vertex42.com/blog/help/gantt-chart-help/creating-a-gantt-chart-with-excel-is-getting-easier.html
I am very impressed with your time sheets. I would like a weekly sheet for a construction company that would allow notes on what was done during the day and where they worked. Do you have one already?
The closest thing to that might be the “time tracker”: https://www.vertex42.com/ExcelTemplates/time-tracker.html
I have just purchased the pro template and am having difficulties using any function simply because it is so slow to use. I am using it off an IMAC through excel 2010 and am constantly seeing the spinning beach ball. I am trying to format the cells/cloumns and rows to a size that suits and cannot seem to drag multiple rows or columns to do so. Can you help please.
@GGredley: One thing that might help both issues is to just increase the zoom, which changes only the display on your screen. If you are trying to view the entire worksheet at the same time, it takes Excel longer to do the calculations necessary to refresh the screen. Another way to speed things up is to delete some of the columns from the right side of the gantt chart area. That will reduce the overall number of formulas and should speed things up a bit, but zooming may be a better approach (and less permanent than deleting columns). The Excel 2003 version (.xls), which will still work in Excel 2010, might work faster than the .xlsx version because it uses fewer conditional formatting rules (the extra conditional formatting rules are for the color-coding).
Want to use more than one predecessor! How can i, if possible?
@Gilbert: There is a video demonstrating how to use the predecessor columns. I would recommend watching that. Or, if using a formula, you could use something like =MAX(WORKDAY(enddateA,1,holidays),WORKDAY(enddateB,1,holidays)) where “enddateA” and “enddateB” are references to the dates listed in the End column for tasks A and B.
Hi,
I would like to download a 2013 calendar with all the holidays and dates already on it. I want to be able to type in activities from my computer on to this calendar and save it with the project and eventually print it out.
What calendar do I download? I have Microsoft Office Excel 2003.
Thanks
@Renee … sounds like you might want to try the one on this page: https://www.vertex42.com/ExcelTemplates/excel-calendar-template.html (the big green download button that says “for Excel 2003 or later” underneath).
I have been using Calendar Pro for several years. I believe I have version 1.6. The mini calendars at the bottom of the generated calendars are showing Dec and Feb for every month. How can I fix it to reflect the correct months? Thanks
@Jerome … you’ll need the most recent version if using Excel 2010 or later. Email me if you are unable to log in to download the new version.
Thanks Jon: I purchased Calendar Pro about 6 or 7 years ago and I do not have a copy of a receipt. Is there any way I can download it at an upgrade. The application works fantastic with the exception of the mini calendars. I do not want to have to spend the extra $9.95 to resolve the minor inconvenience, Thanks.
@Jeorme … You’ll need to contact me via email so I can send you the file.
I am using the gant chart and would like to include all weekends but exclude hoildays.
The WORKDAY.INTL function only allows to select a differnet weekend.
Yes, the WORKDAY.INTL and NETWORKDAYS.INTL functions would be the way to go. In this case, you could use a string for the weekend parameter of “0000000” to mean that there is no weekend, but it still allows you to define holidays. If you are a “Pro” customer, I can send you a version that uses these .INTL functions (send me an email with your order #).