A Gantt chart is used to plan and track the progress of a project. Although Excel does not contain a Gantt chart feature (maybe one day), its tabular structure and wealth of tools provide us with the means of creating one.
A Gantt chart can be created in many ways to match your requirements.
Using the Excel Gantt Chart Template
This Excel Gantt chart template uses fixed scheduling on its tasks and provides a timescale of 1 full year from the project start date. To use the template;
- Enter the project start date in cell E1.
- Enter the ID and name for the tasks of your project.
- Enter the task’s estimate start dates and durations.
- Enter the % completion to update the chart with the progress of the project.
Download the Excel Gantt chart template.
Let’s look at what was used to build this Gantt chart in Excel.
A Thermometer Chart
A thermometer chart has been used at the top of the sheet. This chart is used to visualise the progress of the project easily. It uses the data stored in cell E4 which calculates percentage completion.
The freeze panes feature is used to ensure that the project overview section and timescale at the top of the sheet, and also the table of task data to the left are both always visible as you navigate around the sheet.
Conditional Formatting has been used extensively in this Excel Gantt chart template to display the task progress, task % completion, non working days and today’s date.
There are 5 Conditional Formatting rules in total. To view the Conditional Formatting rules;
- Click the Home tab on the Ribbon.
- Click the Conditional Formatting button and select Manage Rules.
- Select This Worksheet from the Show formatting rules for list at the top of the dialog box.
Some of the rules used are quite advanced. Learn more about Conditional Formatting.
- Highlight Saturday and Sunday in a list
- Conditional Formatting with dates – 5 examples (video)
- Conditional Formatting with multiple criteria
Format as a Table
The Format as Table table feature found on the Home tab has been applied to range A6:F18. The table will grow automatically has new tasks are entered into the list. The table is named Entry.
A table is also used on the non working dates on the holidays sheet to automatically change in height if more holidays are added.
The WORKDAY function is used to calculate the date a specified number of working days before or after a specified date.
This function has been used in the Gantt chart to calculate the finish date of each task. Non working days are entered on the Holidays sheet and included in the calculation.
Learn more about how to use the Workday function.