◄ 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
My progress tracker is not displaying properly on reopening the spreadsheet (openoffice version). The progress bars seemed to have moved down with the bars for the first tasks now sitting alongside the last few tasks, and now 2 red lines (like the today’s date line) have appeared.
Help.
@Kirsty, The OpenOffice version is a free version. We don’t do spreadsheet consulting, but you are welcome to request help from your local spreadsheet expert or get a quote from a professional spreadsheet consultant. It sounds like cells may have been removed and adjusted up/down rather than using full row operations. If you purchased the Pro version, contact us via email and send a copy of your file.
Hello,
I am civil Engineer. In construction industry we use bar bending schedule a lot. In that case i want to insert a number of bar shapes in a particular drop down list so that I can select a particular one. But i don’t find any macros for the particular problem. Is it possible for you provide a Excel template and i have model template but macros are protected with password so if reply me i send you the model template for your understanding purpose .
It will be useful for many people.
Awaiting for your reply.
Thanks & Regards,
Saravanan
@Saravanan, We unfortunately do not usually have time to help with customization of templates. In the Pro version you should be able to insert a new column to store additional info about a task. You might also want to read up online about how to create drop down lists via data validation.
Your system is using a 5 day work week. I need the dates counted on 7-day work week. My wife and I are Realtors. All contract days are counted as 7-day work week.
HOW DO I MAKE EXCEL COUNT ALL DAYS DURING THE WEEK?
@Vernon, For 7-day work weeks, you can just use the template rows that are based on Calendar Days rather than Work Days. See the video demo that discusses this. The Pro version is set up by default with rows that use Work Days as inputs, but the video demo shows how you can use the template rows to set up a different set of inputs.
Hi there,
I purchased Gantt Chart last month and have been playing with it to support a few of my projects. I have a suggestion.
Some of my tasks will take 1.23 days or .5 days etc. Is there a way to have a hybrid of the hours Gantt and the days Gantt so that I can schedule tasks to take 1.25 for example.
Another suggestion.
Prior to a weekely meeting to review the plan, I highligh items past due in Red (end date past todays date and not 100%), Items to be worked on this week in Green and items to be focued on next week in another color. I also color 100% complete in blue.
I can add a formula to support this, but thought it would be great to add into the solution you have here.
@Ric, A few people are testing a version that I made that supports times/hours, though the gantt chart still shows everything rounded up to days. I may post this as an extra download. Email me if you want to try it out.
Using a formula in the Color column is what I would suggest for automatically color-coding the bars.
I am using your Gantt Pro version. I have a Project Schedule that I used consistently every year. Is there a way to copy my current Gantt Chart into a new workbook and then just change the years for the new schedule? For example:>Copy current Gantt chart sheet to new workbook>update all 2014 dates to 2015
@Michael, You could try just saving your file as a different file name. Copying the worksheet into a new workbook will mess things up. In the new workbook, you could try selecting a range of input cells containing dates and using Find/Replace (Ctrl+f) to change 2014 to 2015. If you have used formulas to set up task dependencies, then that minimizes the number of dates you would need to update each year.
I purchased the Gantt Chart Pro template. Any help on how to make the calculation when entering data move faster? Changing any field results in 3-5 seconds of waiting. I know there’s a lot of conditional formatting in here, but 5 seconds isn’t really usable. Do I need to turn off auto calculating? I dropped the “beta” and example sheets and it didn’t do anything. Quick help would be appreciated
@Jason, It usually helps to zoom in the Excel window so that fewer cells are visible on your screen. If you are zoomed way out (seeing all the data and the whole chart at the same time) then it can take longer to recalculate. Using Freeze Panes to keep the header rows in place as you scroll is useful. If you have done a lot of copy/pasting then conditional formatting conditions can get duplicated and become messy. I’m not sure if that increases the recalculation time or not, but if zooming in doesn’t help, you can send me your file (via email) and I’ll see if there is something else going on.
I like the gantt chart software I just purchased but I can’t seem to make the holiday function work. When my start day rolls to a Saturday the end for a 1 day event goes to Monday. Great but what about when that Monday is a holiday? Shouldn’t the finish date roll to Tuesday?
@Peter – If the start date is a Saturday, and Saturday is considered a weekend (as with the WORKDAY() function), then the WORKDAY() function won’t work right. The START date must not be a holiday or a weekend if you want the work day calculation to be correct. This means that if you are using a formula to set the start date based on the finish date of a previous task, you would need to use =WORKDAY(end_date,1,holidays) to add one day instead of just =end_date+1. Note that if you are using Excel 2010 or later, the new WORKDAY.INTL() function can be used to define what days are considered to be work days (such as treating Saturday as a work day).
Hello,
I have been using your “Excel Stock Quotes Template” for years and I appreciate you making this template available for download. However, for the past few days, the template does not work when hitting the Data refresh button. I am using Excel 2007. When hitting the button, the formatting of the template is lost. I was wondering if there is a fix for this problem?
Any assistance would be greatly appreciated.
Thank you in advance,
Sidney Jain
@Sidney … Your question is off topic for this particular post, but watch for notices on the stock quote template page.
Above you state: “The two different colors of the bars in the Gantt chart (gray for incomplete and blue for complete) are extremely useful in quickly determining the overall status of your project.” Doesn’t the gray portion of the bar show the percent complete and the blue portion shows the percent incomplete?
Also, what would be the conditional formatting formula to change a 100% complete bar color from gray to green for example?
@John, In the latest version I switched the blue and gray so that gray indicates complete. This allows more colors to be used for the incomplete portion of the bars. The original version used blue to indicate complete. To see the formulas used for conditional formatting, you can go to Home > Conditional Formatting > Manage Rules and choose This Worksheet from the dropdown. You will find they are fairly complex, but it is possible to change the colors if you are comfortable doing so.
I would like to now to generate the red “Today Bar” on the Gantt chart.
Regards,
Samuel
@Samuel, It is done via conditional formatting. The cell background is red when the date matches the cell containing the =TODAY() date.
Thanks John. I am testing it now. Grateful.
With regards,
Samuel.
Hi is there a way of changing the working week to start on a Sunday, so the weekly view starts at the right date for me
@Lucy, I think there is still an option for that in the hidden Row 1. After unhiding the top two rows, look at the cell comments to figure out which cell controls the start day of the week.
Hi, I’d like to make a year-long calendar. I’ve watched your video on adding dates to expand the viewable dates, but when I try to copy columns in a batch like it shows on your video, it only copies one column at a time. Is there a setting in Excel 2007 that prevents me from copying multiple columns? Otherwise, I will have to copy and paste a column 270 more times! Thank you!
@Susannah … I just tried this on the gantt-chart.xlsx file in Excel 2010 and you can copy columns containing multiple weeks at a time and then select the column to the right of the chart and paste. I don’t know about Excel 2007. You may find that due to the number of formulas in the .xlsx version, adding 270 more columns may make Excel run too sluggishly. That is why I added the dropdown box that lets you change the scale from daily to weekly or monthly.
Hi, I just purchased Gantt Chart Pro because I’ve inherited a project which I now have to initiate and manage to completion. The project is laid out using Gantt Chart Pro which has made it much easier to understand the entire project, the tasks, dependencies, etc. I need to update all the dates as the project will commence 18 months after the original project start date. Is there a way I can update the start date and have Gantt Chart Pro refresh all the dates within in the Gantt Chart based on the new start date? I don’t want to have to create a new Gantt Chart for the project by entering the tasks again all over again into a new file. Thanks for your help on this.
@Evelyn, If the person who originally created the file created dependencies between the dates via Excel formulas, and if they set it up to refer to the start date, then yes. If they did not do that, then you’ll need to edit the dates. Take a look at the demo videos for more info about creating dependencies (the video links are located near the button where you purchase Gantt Chart Pro). Make a copy of your current file and try editing dates (after reading the Help worksheet enough to understand what cells are inputs).
Hi, I am currently using the free version of the template as an evaluation for the PRO before I purchase it. But one question. Why can’t you add comments to the cells within the spread sheet? Or is this issue just for the free version. If that is not the case, how can I “unlock” the cells to add comments?
I am currently using Excel 2011 for Mac.
Regards Niklas
@Niklas … not being able to add comments is only an issue with the free version. The Pro version is not locked, so you have access to all Excel features.
Rats, I previously fat fingered and erased or submitted my issue.
I would like to show the task name to the left of the task “bar”. My bars are many (tens to hundreds) of inchs to the right of the task list.
I have not figured out how to find the formula that actually “draws” the bar. My spreadsheet skills are about a 3 on a scale to 10l I am buffaloed at how you do that. I did find the OFFSET for the date, but not how the offset is used.
I pasted a (right justified) link to the ‘task” next to the bar. When I change the time bar, my entry is static, while that bar moves with the “date”. Tilt!. That does not work.
How/where do I apply a link that is “attached” to the shaded/colored bar?
Thank you. – Gary Vandeman
@Gary… In my gantt chart template, the bars are created using conditional formatting. A gantt chart using an actual Excel Chart object would let you show labels over or next to the bar. The CPM spreadsheet is the only one that I have that does that.
Using formulas to label the bar won’t work because if you add formulas to all of the cells, the text will not be able to overlap other cells.
You might want to try using Freeze Panes. The gantt chart uses this technique to make the header row always shown while scrolling vertically. First, go to View > Freeze Panes > Unfreeze Panes. Then, select the cell below and to the right of the row and column that you want to have remain, and go to View > Freeze Panes > Freeze Panes. Then, when you scroll to the right, the Task will stay in place as you view the gantt chart area.
This company is selling your templates for their benefit! Please report them to clickbank!
http://www.pmmilestone.com/
Good luck!
i HAVE JUST PURCHASED THE FILE. HOW CAN I CHANGE IT QUICKLY SO THAT THE DATES INCLUDE WEEKENDS AS A TOTAL. WORKING A 7 DAY WEEK HERE..
@Peter … To include weekends, use the template rows that define tasks based on “Calendar Days.” Or, if using the international version of the spreadsheet, you can define the work week to have NO non-working days.
I’ve purchased the pro version and am still getting my head around things (my brain and spreadsheets dislike each other immensely). I’m trying to print my gantt using the daily view and with a timeframe of March to September, it will only print until the end of April. I’ve tried setting print areas but that hasn’t worked either – it’s like once you get to the end of april, all the rest of the year disappears, but if you use the slider bar (underneath the daily/weekly/monthly drop box) you can move along to see those date – which is fine, but doesn’t help me print the entire view. Is there any way to print the full date range of the project in the daily view?
Thanks in advance.
@Nardia, In Excel you can only print what is currently shown in the worksheet. One of the main purposes of allowing you to switch to the weekly view is so that you can print a larger date range. If you want to use the daily view to print a larger date range, then you would need to add more columns to the right of the gantt chart. See the section “Increasing the number of columns …” in this blog post. Or, you can use an old-school method: Print, then change the date range (with the scroll bar) and print again, then cut/tape your pages together to display the gantt chart across multiple pieces of paper.
how do i contact a sales person so i can interact with a human and ask directed questions
@Victor… See the About page for my email. We have no separate sales force or technical support staff.
I’ve been trying to write a macro (unsuccessfully) that will determine if a cell is either colored or uncolored down a column of the color coded area. I am not real familiar with Visual Basic but understand the basics of macros, does anybody know of a way to do this for the Gantt chart pro? For my purposes all I really need to know is whether or not the conditional formatting is being applied to a specific cell or if it is left white.
@Daniel, That’s something I’d Google about: “VBA get background color of cell” or something like that.
Any info I’ve obtained from google does not help given the complicated conditional formatting that exists in the the gantt chart pro. There seems to be no way to obtain the displayed color of a cell since the cells I am interested in are all conditionally formatted.
@Daniel, True there is no formula that I know of that can tell you what color the background of a cell is. You could refer to the End date and Start date if you need information about a task. Also, in the Google Sheets version, the cells in the chart area use formulas to display a letter representing a color. That is what the conditional formatting uses to determine the color. Let’s say you wanted to create a row that would tell you how many tasks are active during a certain day. You could use =ROWS(L10:L15)-COUNTBLANK(L10:L15) or if you wanted to count the number of tasks colored blue =COUNTIF(L10:L15,”=b”).
How do I delete the formula controling the end date? I just want to insert a start and end date and let the chart calculate the number of days for me. Pl help
@Shiri, the video demos and the Help worksheet explain how to use the template rows for choosing different sets of inputs, such as Start Date + Calendar Days, Start + Work Days, or Start Date + End Date.
Hi, I am trying out the o365 GANTT and am having problems getting the ’00 yyyy’ field under a weekly display mode reflect the real month and year. I just get 00 yyyy. I am working with DD-MM-YYYY date format.
I would have liked to attach a screen print or even send you the whole file if that helps.
Thanks!
Pierre
@Pierre, To send a screenshot and file, you can email me via the email listed on the contact page. Please also let me know what location settings you are using (country / language). Some locale settings don’t use the same format codes.
Hi
In the Daily View:
Can i change the way the grantt shows dates, from the US(mm-dd-yyy) way to the europe way(dd-mm-yyyy)?
In the Weekly view: The spreet sheet show the year as: yyyy. ex. jan yyyy- mar yyyy
can I fix this?
@Jonas … For the new Office 365 version (gantt-chart_o365.xlsx), in the weekly view if you are using the template in a country/language that doesn’t use the English date format codes, you will need to edit the formulas to use the date format codes appropriate for your country.
To use dd-mm-yyyy instead of mm-dd-yyyy in other cells (such as the Start and End columns) you can go to Format Cells and create whatever custom date format you want. In the gantt-chart_o365.xlsx file, we are also experimenting with a simple drop-down option (in the Help worksheet) that lets you change from mdy to dmy format (using conditional formatting).
Could you give me some assistans on how to edit the formulas to use the date format codes appropriate for my country.
@Jonas … 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. I’ve updated the gantt-chart_o365.xlsx file to use the [$-409] locale code (English – U.S.) for custom date formats. 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.
I am using Gannt chart Pro and every time I insert a row using either method shown in the tutorials, the gannt view with the dates that were previously there completely clear out. Is there some formatting that is being changed when I insert?
@Kristina, The gantt chart is displayed using conditional formatting. When inserting new tasks, make sure to insert entire rows, then copy formulas down (Ctrl+d). Or copy formulas from the set of template rows. If you don’t insert entire rows, the spreadsheet will get messed up.
Just purchased. Download new beta version. Does this work with the new WPS offices spreadsheet.
I am getting strange info in the calculated date field.
Wed 5/06/15 #NAME? 24 #NAME? 0% b
Wed 1/07/15 #NAME? 7 #NAME? 50% k
@Lynn, Sorry but this spreadsheet was designed for Microsoft Excel. You can contact me via email to request a refund, or see this post for info about requesting the refund.