Starting at the top left corner there is a drop down box that allows you to select the Time Period – giving you control over the time frame that is shown within the chart area (choose daily, weekly, or monthly). Under the Gantt Chart dates you need to input the Beginning Date – I like to use a formula that is based on today’s date (i.e., =Today()-30…or however many days back you want it to be, that way you don’t have to change it often). Next is Today, this too should be a formula (i.e., =Today()) this is what dictates the red bar on the Gantt Chart. The Task Duration Calculation allows you to choose how you want duration calculated – all of these items can be changed at any time during the project.

Once the top level information has been input, you can begin to enter your work breakdown structure (WBS). Any information that is a formula, except for the WBS, will say formula when there is no data (also, formulas are denoted in either yellow text or are in the lightly shaded area in the Task Duration column).

If additional rows (i.e., sub-tasks) are needed within a given task, simply add the number of rows needed. All formulas can be copied down from another row – the WBS numbering will automatically update. The WBS is dependent on the level chosen, you can choose one of the following:

• P – Primary Task
• A – Sub-Level
• B – Sub-Sub-Level

If you like your different sub-levels indented, you can use the indent feature within Excel to do this quickly and accurately throughout the spreadsheet (this cannot be automatically done without adding macros to the workbook, so you’re on your own for this one). You can also copy and paste from a similar cell with the formatting you want.

The indent button is under the Home ribbon, as shown in the lower left hand corner of the picture below.

Note, If for some reason there is a problem with the level you’ve selected (or there is no level selected) you’ll get a reminder as shown in the first picture in this step. Simply go back to the first place it says “LEVEL?” and ensure you have the right level selected.

Because this is a template, we’ve only included 5 primary tasks; however, you will likely need more. To add another primary task, simply copy and paste a previous primary task along with a few sub tasks (you can always add more primary tasks later). This will capture the grouping as shown below.

The grouping allows you to expand and collapse primary tasks. You can use the button on the top left side of the spreadsheet to expand collapse all groupings.

Starting at a primary task, select the whole row and a few sub-tasks, and copy rows. Just like this…

Click anywhere you want to put the new primary task and sub-tasks, then select insert copied cells. You can see that all of the formulas, WBS numbering, grouping, etc is updated. The only thing you need to do is update the task specific information. A feature we think meets the definition of very cool!

You enter the percent complete; as the percentage increases cells will change from blue to green. The primary task captures the progress of all sub-tasks within just that task.

The formulas here are dynamic as well, so you won’t have to worry about it picking up data from other tasks.

Use the scroll bar at the top of the Gantt Chart to “look forward” or “look back” in time. This feature is best understood if you download the spreadsheet and try it. Just remember that the beginning date sets how far you can go back…you can go forward for years.

The Time Period allows you to modify the Gantt chart instantly. Choose from Daily, Weekly or Monthly – the body of the Gantt Chart will automatically update. The Time Period box is at the top of the chart, a drop down box will appear once you click on the Time Period cell – use the drop down to select the time period you want to use.