This more advanced Gantt Chart in Excel has the basic scrolling Gantt Chart and custom weekends and holidays, but it also includes alerts for percent complete, email links to task leads, Work Breakdown Structure numbering (WBS), and filters to show only the tasks and date ranges you want. If you are managing personal tasks or small projects this is a more useable tool than a large tool like Microsoft Project.

You can just download this puppy and use it for your tasks and projects, or you can go through this and the earlier tutorials to learn how it works.

You can use as much or as little of these features as you need to help you with time and project management. You may want to download this file and just start using it or you can take it apart and see how it works using the tips in the rest of this article. Many readers are already using one of the earlier versions. If you are, you can just copy and paste your data over the green shaded areas in this template and start taking control of your time and projects.

Download the template for the most advanced Gantt Chart in Excel. This template includes the Excel tutorial worksheets from the first two Gantt Charts in Excel.

The first tutorial for the basic Gantt Chart in Excel shows you how to create a scrolling Gantt Chart using a scroll bar and conditional formatting. Click here to go to How to Build a Basic Gantt Chart in Excel.

The second tutorial for a Gantt Chart in Excel with holidays shows how to use conditional formatting and the WORKDAYS.INTL function to display custom weekends and holidays in the Gantt Chart. By using the amazing WORKDAYS.INTL function the chart adjusts for custom weekends and custom holidays and calculates the end date without including non-work days. 

Preparing the Previous Tutorials for Enhancement

This third tutorial has modified the second tutorial slightly before making the changes below. Rows 8 through 11 have been hidden since they normally won’t be used except in initial setup. In addition, the data rows and conditional formatting have been copied down to include a more realistic number of rows for your tasks. I’ve used a similar Gantt Chart in Excel to manage projects with as many as 12 people and about 100 tasks.

Freeze the View to Keep Gantt Chart Data in Sight when you Scroll

With all the additional rows in this robust version you need to be able to scroll while keeping the headings in sight. To do this, select the first row of data under the titles, then in the View ribbon, in the Window group, select Freeze Panes, Freeze Panes.

This freezes the titles and above so you can scroll the data are down while keeping the headers and above in sight.

Insert a Row to Separate the Calendar Rows from the Single Title Row

If you are using the template from the previous tutorial, before inserting additional columns for more header titles you must insert one blank row between the headers and the row containing dates. This blank row will allow the lower portion of the Gantt Chart to become a database. (Excel databases can have only a single header row.)

If you are using the template from the previous tutorial, select row 16 and insert a row. Your headers should now be in row 17 as shown in the picture above.

Download the template for the most advanced Gantt Chart in Excel. This template includes the Excel tutorial worksheets from the first two Gantt Charts in Excel.

Add Additional Titles for Filtering, Reporting, and Alerting

Depending upon the types of tasks and projects you manage you may want to add additional headers to the original four: WBS, Start, Days, End. These additional headers can help you manage by sorting or filtering on a more robust Work Breakdown Structure, identifying the Task Leader, sending email to a Task Leader, tracking the % complete, tracking % resources used, checking task priority, and so on.

Be careful though. I recommend staying lean. Only add columns that will help you manage your tasks or the Gantt Chart will become to burdensome.

Tip: To keep your Gantt Chart in Excel looking neat and clean, hide columns you only use occasionally, then unhide them when you need them. To hide a column, select the column, right-click, and choose Hide. To unhide a column, select across the hidden column headings, right-click, and choose Unhide.

Adding Task ID or Work Breakdown Structure Numbering to the Gantt Chart in Excel

The Task ID or WBS columns will help you sort, resort to original order, and filter to show specific Task ID groups.

The Task ID in column B is created with data entered in the hidden columns C and D. Columns B, C, and D also use custom numeric formatting so numbers will appear in the formats 000.000 .

Adding % Complete and a Warning Alert to the Gantt Chart in Excel

It is a good idea to create an alert that warns you when a task is falling behind. The alert described here compares the entered value for % Complete against the % of workdays that have passed. If the % Complete is less than the workdays passed, then conditional formatting highlights both the Complete cell and the Task cell for that task.

A TRUE/FALSE condition tests whether to format for an alert.

Spent Days in I18 uses the formula,

=NETWORKDAYS.INTL($F18,$H$6,$H$9,rngHolidays)

Copy this down.

Complete % in J18 is a data entry cell where you can enter your best estimate of the percentage complete. Don’t cheat! You will regret it later.

Complete % and Task columns are formatted with conditional formatting that examines the TRUE or FALSE results in the AlertFormat column. To see the conditional formatting, click in one of the cells and open the Conditional Format Manager.

AlertFormat in K18 calculates whether conditional formatting should be applied to show an alert.

=IFERROR(AND($J18<1,$J18<($I18/$G18)),FALSE)

Compares the percentage of Spent Days to the entered Complete %. If the percentage of Spent Days is greater than the Complete % then conditional formatting colors the Complete % cell and the Task cell. Copy this down.

Adding Other Titles in the Header to Make Management Easier

Other columns and headings you may want to consider are,

Sorting and Filtering by Tasks, Dates, and Priorities

You may have noticed that by adding additional titles in the header information your Gantt Chart in Excel is beginning to look like an Excel list or database. In fact, it is!

Your Gantt Chart now has the power of an Excel database or list with the added bonus of a scrolling calendar on the right. You now can filter and sort to see just the information you want and see the visual calendar for that information.

Download the template for the most advanced Gantt Chart in Excel. This template includes the Excel tutorial worksheets from the first two Gantt Charts in Excel.

To add filters and sorting to your Gantt Chart,

1. Make absolutely sure there is a blank row between the title headers and the dates in rows 14 and 15.

2. Select a cell in the data area.

3. On the Data ribbon, in the Sort & Filter group, click Filter. Small filter drop-down arrows appear next to each header.

You can filter or sort on any header. However, one of the most common ways of filtering is to filter so that only one major Task ID is shown. This is especially true if you have a large number of tasks.

To show a single Task ID group and hide other,

1. Select column headers B:E so that the hidden columns C and D are selected.

2. Right-click the selection and choose Unhide.

3. Select the filter drop-down and click on only the Task ID group(s) you want to show.

4. Click Ok.

Only the Task ID you selected will show. In the illustration below the filter was set to show only Ordinate numbers of 010. To return to showing all tasks, clear the filter.

Sort on any column by clicking the filter icon next to each header and selecting ascending or descending sort. This can be helpful when searching for near-term dates, alerts, completed tasks, etc.

Return to the original order by sorting on the TaskID header. (See, there are a couple of good reasons for having a WBS or Task ID.)

Hide the Ordinate and Subordinate columns when you don’t need them to keep the Gantt Chart looking clean.

Download the template for the most advanced Gantt Chart in Excel. This template includes the Excel tutorial worksheets from the first two Gantt Charts in Excel.