How do you make a Gantt chart in Excel or Google Sheets?
Short answer
Use one row per task and one column per week, with a formula in each cell that returns a block when the week overlaps the task dates. Start dates come from the finish dates of predecessor tasks, so the chart moves when a date changes. A stacked bar chart is another method in both apps.
A Gantt chart shows each task as a bar across time, with its start and finish, and any dependencies between tasks. The method below builds one from formulas in cells. It works the same way in Excel, Google Sheets and LibreOffice, because it uses only IF, AND, MAX and plain date arithmetic. The bars are text blocks, so the chart updates whenever a start date or duration changes.
How do I lay out a Gantt chart by task and week?
- One row per task, with the duration in days.
- The start of each task is a date. The start of a dependent task is the day after the latest finish among its predecessors.
- The finish is the start plus the duration, less one day.
- The columns after the dates are weeks. Each header holds the first day of its week.
- Each cell in the grid tests whether the week overlaps the task. If it does, the cell shows a block.
What does a worked Gantt chart example look like?
A workshop fit-out has six tasks. Durations are in calendar days, and the project starts on Monday, 2 November 2026.
| Task | Days | Start | Finish | Depends on |
|---|---|---|---|---|
| Site survey | 3 | Nov 2 | Nov 4 | none |
| Design layout | 5 | Nov 5 | Nov 9 | Site survey |
| Order equipment | 10 | Nov 10 | Nov 19 | Design layout |
| Prepare floor | 4 | Nov 10 | Nov 13 | Design layout |
| Install equipment | 3 | Nov 20 | Nov 22 | Order equipment, Prepare floor |
| Commission and sign-off | 2 | Nov 23 | Nov 24 | Install equipment |
Install equipment cannot start until both Order equipment and Prepare floor have finished. Order equipment finishes on Nov 19, later than Prepare floor, so the install starts on Nov 20 (the later finish plus one day). The project finishes on Nov 24.
Each task then spans the weeks that begin on the Mondays in the header:
| Task | Week of Nov 2 | Nov 9 | Nov 16 | Nov 23 | Nov 30 |
|---|---|---|---|---|---|
| Site survey | █ | ||||
| Design layout | █ | █ | |||
| Order equipment | █ | █ | |||
| Prepare floor | █ | ||||
| Install equipment | █ | ||||
| Commission and sign-off | █ |
A week is marked when any day in it falls inside the task. Design layout runs from a Thursday to the following Monday, so it is marked in two weeks.
How do I shade the current week on a Gantt chart?
Put the status date in a cell, such as B9. On the status date of Nov 17, the week of Nov 16 is the current week. Site survey, Design layout and Prepare floor are finished. Order equipment is in progress, and Install equipment has not started. To shade the current week, add a conditional formatting rule to the grid with the custom formula below, and apply it to the week columns:
=AND(E$1<=$B$9,E$1+6>=$B$9)
Can I draw a Gantt chart as a stacked bar chart?
Both Excel and Google Sheets can draw a Gantt-style chart as a stacked bar chart. The start dates form the first series, with no fill, and the durations form the second series, which is the visible bar. The vertical axis is set to show the first task at the top. Its data ranges have to be extended by hand when tasks are added.
How do I set up the Gantt formulas in a spreadsheet?
Put the headers in row 1, with the task names in A2:A7, durations in B2:B7, starts in C2:C7 and finishes in D2:D7. The weeks run across E1:I1.
- Project start, C2: type the date 2026-11-02
- Finish, D2:
=C2+B2-1(fill down to D7) - Start of Design layout, C3:
=D2+1 - Start of Order equipment, C4:
=D3+1 - Start of Prepare floor, C5:
=D3+1 - Start of Install equipment, C6:
=MAX(D4,D5)+1 - Start of Commission and sign-off, C7:
=D6+1 - First week, E1:
=$C$2, then F1:=E1+7filled across to I1 - Grid, E2:
=IF(AND(E$1<=$D2,E$1+6>=$C2),"█","")(fill across to I2, then down to I7)
To skip weekends, replace the +1 with a WORKDAY formula, such as =WORKDAY(MAX(D4,D5),1). Excel and Google Sheets both support WORKDAY.
The project Gantt chart template sets up these columns already. The earned value management tracker compares progress with the same schedule, and the SPI and CPI question explains the two indices.