Gantt Charts Made Easy – New Version 5.0

In December, 2020 we released a new version of Gantt Chart Template Pro that is designed to consolidate multiple versions and features into a single template. It is available to both new and current customers. Existing customers can return to the download page to get the new version (no new purchase necessary).

When I first created the Gantt chart template for Excel, my goal was simplicity and ease-of-use with minimal learning curve. Over time it became more complex as people requested new features, capabilities, and different ways to define task durations and dependencies. As with version 4.0, my goal for this new version was to maintain the idea of "Gantt Charts Made Easy!" while still providing as many awesome features as possible.

This page discusses some of the new features that were added or changed in Version 4.0 and 5.0.

1. WBS Numbering Controlled with a Simple Drop-Down Selection

You don't need to understand how the formula works for the work breakdown structure (WBS) numbering (2.4, 3.1, 3.1.1, 3.1.2, etc.). All you need to do is select the WBS Level from a drop-down box. The WBS numbering is automatic. Also, the task descriptions are automatically indented based on the chosen WBS level.

Unlike the free version (and older pro versions), you will not need to copy and paste different WBS formulas for different WBS levels.

For fellow Excel fans: If you are curious about how the mega-formula for the WBS numbering works, you can learn some of the techniques via an article I wrote about various text manipulation formulas. Also, the indenting of the task description is controlled using Conditional Formatting and custom Number formats such as "_s_s_s_s@" to format the text with leading spaces.

2. Multiple Ways to Define Task Dates and Durations

The biggest change in version 4.0 had to do with how you define each task's start and end date. Instead of copying formulas from a set of template rows or cells to define tasks in different ways, you just enter your data in the appropriate input columns.

Watch the Video for Version 5.0

Ultimately, a task needs to have a start date and an end date. You can arrive at those dates in a lot of different ways, depending on whether you want to enter dates manually, enter a duration instead of a date, or whether you want to create dependencies between tasks using Excel formulas.

Now, all you need to do is enter a combination of two inputs. For example, this could be the Start date and End date, or the Start date and number of Work Days. The worksheet has separate columns for the calculated start & end dates. These calculated Start and End dates are what the spreadsheet uses to display the chart.

The downside to this new way of defining tasks is that there are quite a few input columns as well as the calculated columns. However, after you are done defining your tasks, you could hide some or all of the columns you don't need to see.

Tip: In addition to hiding columns to condense the input section of the worksheet, you can use the Arial Narrow font and then make the columns narrower.

Update 12/1/2021: There is now a new Lead/Lag column hidden right after the Predecessors columns. Using this optional column will make the task start X work days after (Lead) or before (Lag) the end date of the predecessor. Enter a positive value for Lead or a negative value for Lag. This column only works in combination with the Predecessors column.

3. Compatibility with Desktop, Mobile and Excel Online

Many of the design aspects of Version 5.0 have to do with making the spreadsheet compatible with Excel Online and Excel Mobile without having to use different versions of the file for different versions of Excel. The spreadsheet uses some features of Excel that are not available in Excel Online such as cross-hatching for displaying Actual vs. Planned - but the spreadsheet also includes a way to make this work in Excel Online (see the help and labels and notes within the spreadsheet).

Questions?

If you have questions about Gantt Chart Pro, please first watch the videos available on the web page, then check the Help worksheet, and then check the FAQ post. If you still can't find the answer to your question, you can contact me.

Q. Why are there two sets of Start/End dates?

The input columns allow you to quickly decide whether to enter dates and/or durations. However, the Gantt chart needs to reference a single column for all Start Dates and a single column for all End Dates. So, the calculated columns use formulas to calculate the dates based on which combination of inputs you choose, and the Gantt chart references these calculated columns.

When printing or presenting, it is better to hide the input columns instead of the calculated columns, because the calculated columns show ALL dates and durations while the inputs may or may not be used. For example, you might define a task by entering a Predecessor and a duration in Days, which means the inputs for Start date and End date are left blank.

Q. Is there any reason to use the older version(s)?

I don't think so, unless you are using an old version of Excel, or you prefer the older designs. However, the older versions will not be updated or supported.

Q. Why are some videos still showing the older versions?

Some older videos are still lingering on Youtube and elsewhere, and for the most part these older videos are still mostly applicable to the new version. See the Gantt Chart Pro page for the most recent videos.

Q. What happens if I enter more than two values to define task durations?

Avoid doing that. If you enter a Start Date and an End Date AND the number of Work Days, then you won't know which of the 3 values were used to calculate the Start and End dates used in the chart.

Q. What is happening when I enter End Date and Work Days as the inputs?

The Start date is calculated to be the number of work days PRIOR to the End date you entered. You might use this for a task that needs to start 5 days prior to the completion of some other task. The End date could be a simple Excel formula that references the end date of the other task.

Q. How do I work backwards from a known project end date, using dependencies?

Version 5.0 allows you to define a task using an End Date and the Duration (in days or work days). You'll just need to enter a formula in the End Date input column such as this one: =WORKDAY.INTL(successor_start_date,-1,weekend,holidays). See the Help worksheet for examples of using formulas to create task dependencies.

Q. What are the little green triangles in some of the cells?

The green triangles are due to the "Error Checking" feature in Excel. Most of the time they aren't actually errors. They may be warnings such as "Number stored as text" or "Inconsistent Formula" or "Unprotected Formula," all of which may be intentional on the part of the user or template designer (such as the WBS numbering purposefully entered as text).

To get rid of the green triangles, you can either choose the "Ignore Error" option every time they show up, or you can go to "Error Checking Options" to turn off some of the various types of warnings.

Q. Will the BONUS files be updated, too?

Perhaps. I've updated the Google Sheets version to 5.0, and will eventually update the other bonus files. However, I do not have a schedule for when that might happen.

Q. How do I show the overall % Complete for the entire project?

The latest update (5.0.1) includes a cell above the % Done column showing the progress for the entire project. If you aren't using the latest version and you want to add this formula to your spreadsheet, you can use the following formula (this requires Excel 2013 or later because of the ISFORMULA function). Note that this formula allows you to ignore the cells in the progress_range that contain formulas, such as if you are calculating the % Done for summary tasks (your overall % Done should not double count the summary tasks rows).

=SUMPRODUCT(workdays_range,progress_range,1*NOT(ISFORMULA(progress_range)))/SUMPRODUCT(workdays_range,1*NOT(ISFORMULA(progress_range)))