Google Sheets Gantt chart template

How to make a Gantt chart in Google Sheets

The cleanest way is conditional formatting on a grid of weeks: each cell colours itself when its week falls between a task's start and finish. It is what the template does, and it takes about ten minutes to build yourself.

1. List your tasks one per row, with a Start date in column D, a Finish date in column E and % Complete in column F.
2. Right of the table, in row 1, type the Monday of the first week (say in L1). In M1 type =L1+7 and drag it to the right for as many weeks as the plan needs.
3. Select the grid under those dates, from L2 to the last week column. Choose Format, Conditional formatting, and under Format cells if pick Custom formula is. Enter this formula and pick a light fill:=AND($D2<L$1+7,$E2>=L$1)
4. Choose Add another rule for the part that is done, with a darker fill:=AND($F2>0,$D2<L$1+7,$E2>=L$1,L$1<=$D2+($E2-$D2+1)*$F2-1)
5. Drag the done rule above the first one in the list. Sheets applies the first rule that matches, so a done week shows dark rather than light.
6. Optional: a rule for milestones, such as a Milestone column in I with Yes in it, placed at the top:=AND($I2="Yes",$E2>=L$1,$E2<L$1+7)

The other common method is a stacked bar chart from Insert, Chart: start dates as a first series with no fill and durations on top. It has to be re-ranged every time you add a task, and it shows no progress.

The shortcut is the template: all three rules, the week dates, a Duration column in working days and a Milestone list are set up already.