There is a great, free Excel Gantt chart template on the Vertex42 web site at https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html
The free version lacks a few features that could make project planning easier. We can update our version of the template to make it smarter. Make these changes before you start to use the template, because inserting task rows can multiply the number of conditional formatting formulas to an unmanageable number.
Skip the weekends in the task end date calculation
When the template calculates the task end dates, it includes the weekends as worked days in the calculation. To make sure the task end date falls on a work day and the weekends are not included in the task duration, update the calculation. Update the cell in the first task row and copy the updated cell down to every row.
Change the formula in F9 from
=IF(ISBLANK(E9)," - ",IF(G9=0,E9,E9+G9-1))
to
=IF(ISBLANK(E9)," - ",IF(G9=0,E9,WORKDAY(E9,G9-1)))Short tasks or headers with no task duration
There are task rows that should not be planned as a full day of work. If
- a row is a group header
- the task execution time is shorter than a day
- multiple tasks can be done the same day
This change will start the task on the same day if the prior task has zero or empty “Days” value.
Update the start time formula in one cell and copy the updated formula down to every task row.
Change he formula in F10 from
=WORKDAY(F9,1)
to
=WORKDAY(F9,IF(G9=0,0,1))Extend the range of the GantChart Excel template
The GantChart Excel template is configured to display the blue blocks only in an 8 week range.
If your project runs longer, you need to extend the range of the display area.
Copy the calendar blocks
- Copy paste the calendar block on the top of the page
- Select the columns of the newly created calendar headers and set the column width of all of them to 1.67
Extend the conditional formatting range
- On the Home tab in the Conditional Formatting dropdown select Manage Rules
- On the top select This Worksheet, and replace “$BN” with “$zz” in every formula.
