Gantt Chart Template for Excel

I placed answers to most of the questions that I get asked in a Help section within the Gantt chart worksheet. You are welcome to contact me if you have other questions, but please take a look through the Help first.

Automatic Work Breakdown Structure (WBS) numbering makes it easy to copy/paste or move rows around, without having to redefine all of the task numbers. You can also use Excel's grouping and outlining feature to hide or show groups of tasks. See my article on Grouping and Outlining in Excel for an explanation of how to use Excel's "Group and Outline" feature for expanding or contracting groups of items.

In the professional version of the Gantt Chart Template, template rows have been included at the bottom of the worksheet(s) that give you different ways of entering the dates and durations for the tasks. You can even use different methods within the same gantt chart as shown in the image below (the inputs have green backgrounds).

Figure 2. Various ways to enter task dates and durations.

• Working Days (default): The new version of the gantt chart lets you choose the duration of a task by entering the start date and the number of work days (excluding weekends and holidays). This is a critical feature for creating a project plan when you are creating dependent tasks.
• Duration: Enter the number of calendar days for a task. Some projects use 7-day work weeks, or require Saturdays or Sundays to be work days, so this option allows you to handle these cases.
• End Date: Choosing the end date can be useful when you have a deadline that forces you to complete a task by that date.

Also notice that in the professional version, the Start and End dates are formatted to show the week day. This helps you avoid starting or ending on a weekend, without having to consult a calendar.

The time-span displayed in the gantt chart is fixed (8, 11, or 3 months, depending on which gantt chart worksheet you use). But, the slider bar makes it easy to scroll through your entire project. The horizontal scroll bar simply adjusts the first date shown in the chart.

This approach lets you scroll through a project that spans many months and years. In Excel 2007, you can add columns to increase the time-span that is displayed.

Scroll bar for changing the range of dates displayed.

Version 2.2 introduces a brand new option for creating task dependencies. Normally, if you want to make a task start after the completion of another task, you would enter a formula for the Start date like =enddate+1 or =WORKDAY(enddate,1,holidays), where enddate references the End date of the Predecessor task. That isn't too hard, but you can tell by just looking at the gantt chart that there is a dependency defined.

We're experimenting with a new approach, where you create dependencies by referencing the WBS number of up to 3 Predecessor task(s). If you reference the WBS using a formula as shown below, the reference will remain correct even after you insert new tasks. But, if you really want to, you can enter the WBS number manually, as long as you enter the number as text, by adding an apostrophe before the number like '1.2 instead of just 1.2.

Figure 3. Creating task dependencies by referencing Predecessor tasks.

This approach is very new, and I didn't want to add confusion for those that are familiar with the free or older versions, so the Predecessor columns (D-F) are hidden by default. The template rows that provide the necessary formulas are also hidden. So, if you want to experiment with this new method, just unhide all the rows and columns, and copy the rows from the template rows at the bottom of the gantt chart.

In the pro version, a worksheet has been added where you can list holidays and other non-working days that you want to exclude. As long as you don't leave any blanks between the dates or delete row 10 (see below) you can copy/paste, insert/delete, sort, or append to the list.

Figure 4. Various ways to enter task dates and durations.

• Can I increase the number of columns displayed in the Gantt Chart if I'm using Excel 2007?
• Dates in UK format (dd/mm/yy)
• Can I import tasks into Microsoft Project?
• Green cells are inputs, but why do some contain formulas?
• How do I use the Percent (%) Complete?
• The red line marking Today's Date is not showing up.