A Gantt Chart is a type of bar chart that is useful for showing the scheduled and actual progress of a project. Developed by Henry Gantt in the 1917, the Gantt Chart is now regarded as the standard project management charting technique. Its simplicity allows it to be easily understood by many people and with the use of a computer software, a Gantt Chart can be generated and modified very quickly and easily.
Gantt Chart Spreadsheet
This spreadsheet provides a flexible and extensible Gantt Chart on a 12-months schedule. It is very simple to use. The user simply keys in a few inputs like task name, start date, duration in days and percent completed, after which, a Gantt Chart will be generated automatically for tracking the project. The spreadsheet is arranged in a manner to be printed easily in Landscape mode or it can be saved to a PDF file using Excel to be emailed to the relevant parties. The diagram below shows a sample of the spreadsheet Gantt Chart.
Gantt Chart Inputs
All the worksheets that require inputs to generate the Gantt Charts are marked with the word "(Input)". On the worksheet, fields that require inputs are marked with "*" for easy identification.
The diagram above shows the input worksheet "Month 1 (Input)". All the input fields required are marked with "*" and described below.
- Project Start Date* - The start date of the project. The spreadsheet will be generating 12 output worksheets for tracking a project over a 12 months period. The start date allows the spreadsheet to determine which month to start generating the schedule.
- ID* - A two digit number for identifying the item/task of the project.
- Items* - The names of the items or tasks of the project.
- Start Date* - The start date of the specific item/task. The date is in MM/DD/YY format.
- DY* - The duration of the item/task in days. The start date and duration will be used to calculate the End Date as shown in the diagram above. It is important to note that the calculation of the End Date takes into account weekends and holidays like Christmas and New Year. Holidays can be defined in the "Holidays (Input)" worksheet.
- DID* - The predecessor's ID. The predecessor item needs to be completed before the start of this item. In this spreadsheet, the DID is used for the user's own tracking purposes. The spreadsheet does not enforce the dependency.
- PCT* - Percent Completed. The spreadsheet will use this field to determine the actual progress to be displayed on the Gantt Chart. The amount of work that is completed is shown as a darker shade of black while the work in progress is shown a lighter shade of black.
The Gantt Chart spreadsheet allows weekend and holidays to be defined and encompassed into the schedules. Weekend and holidays are defined in the "Holidays (Input)" worksheet.
In the diagram below, the first section shows the definition of a weekend. A "Y" will indicate the particular day of week is a weekend holiday.
The second section allows holidays to be defined. It is important to define all the holidays within the 12-months schedule period. This will allow the Gantt Chart to incorporate all the holidays and generate an accurate schedule. The holiday's date is in "MM/DD/YY" format. The second column is used for describing the holidays and is optional. A maximum of 40 dates are supported, beyond which the holidays will be ignored.
Gantt Chart Outputs
This spreadsheet outputs a 12-months schedule in 12 different worksheets. Each worksheet displays the schedule for a particular month. The worksheets are named "Month 1", "Month 2" and "Month 3" etc. In each of the worksheet, a Gantt Chart is included to show the progress of the project.
The diagram above shows the Gantt Chart generated in one of the output worksheets. The top left hand corner displays the month of this Gantt Chart. Each worksheet contains a unique month over a 12-months period. The next two rows show the days of week and days of the month of the Gantt Chart.
The darker shade of black shows work that are completed and the lighter shade of black shows work that is still in-progress. The "x" shows the holidays defined in the "Holidays (Input)" worksheet.
These spreadsheets can be printed quickly and easily in Landscape mode in Microsoft Excel. Simply set the "% to normal size" option to include all the fields in the worksheets.
To print several worksheets at once, simply click the tab for the first sheet in Excel, then hold down CTRL while you click the tabs of the other sheets that you want to select.
Extending the Gantt Chart spreadsheet.
The spreadsheet can be easily extended to support a longer period and more number of items/tasks.
To support more items/tasks in each of the months, simply copy the cells in each of the output worksheets from column A to column AL in row 34 to the next. The row to be copied is shown in the diagram below. The rows for each of the worksheets named "Month 1..12" needs to copied.
Extending the schedule period beyond 12 months
To support a longer schedule period, simply copy and paste the "Month 12" worksheet and rename the new worksheet to be "Month 13". Only the cell in H2 needs to be modified. The original and new formula in this cell is shown below. Simply add 1 to the "months" field below.
The change allows the new worksheet to generate the Gantt Chart for the 13th month of the project. Repeat the steps above to modify the schedule period to the one that is required.
Professional Project Management Package
License PDF Specifications
USD29.90 - Purchase
Microsoft® Windows® 2000 ,Microsoft® Windows XP® and 2003, Microsoft® Windows Vista®, Windows 7, Mac® OS X
512 MB RAM
5 MB of Hard Disk space
Excel 2002, 2003, XP or Excel 2007 or Office for Mac OS 2011
See the Gantt Chart spreadsheet on Mac OS X
Back to Excel Templates and Add-Ins.