Project management tools cost $10–20 per user per month and still get abandoned. Meanwhile, the Gantt chart you actually need — bars showing when each task starts and ends — can live in Google Sheets for free, using nothing but conditional formatting.
Here’s how to build one that updates itself when dates change. This is the same technique our Project & Task Tracker uses.
The idea: dates become colored cells
A Gantt chart is just a calendar grid where each task row paints the cells between its start and end date. Three ingredients:
- A Tasks sheet with start and end dates.
- A Gantt sheet with one column per day.
- A conditional formatting rule that colors a cell when its date falls between the row’s start and end.
Change a date on Tasks, and the bar moves. No drawing, no add-on.
Step 1 — The Tasks sheet
Create Tasks with these columns:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| ID | Task | Phase | Owner | Status | Start | End | Days |
Fill in your tasks. Then add a Days column that validates the data:
=IF(AND(G2<>"",H2<>""),H2-G2+1,"")Add dropdowns so the sheet stays clean: Data → Data validation on Owner and Status (Todo / Doing / Done / Blocked). Add an auto-filter (row 1 → filter icon) so you can view one owner or phase at a time.
Step 2 — The Gantt date header
On a new sheet called Gantt, put task names in column A, start in B, end in C —
all referencing the Tasks sheet:
- A5:
=IF(Tasks!B5="","",Tasks!B5) - B5:
=IF(Tasks!G5="","",Tasks!G5)(format as date) - C5:
=IF(Tasks!H5="","",Tasks!H5)(format as date)
Now the date columns. In D4 (the first day column), seed from the earliest task:
=MIN(Tasks!$G$5:$G$54)In E4 and every column after it:
=D4+1Copy across 30–45 columns — that’s your timeline window. Format row 4 as day
numbers (d), and add a month label in row 3 that only appears when the month
changes:
=IF(MONTH(E4)<>MONTH(D4),TEXT(E4,"mmm"),"")Shrink the day columns to ~3 width so the chart reads at a glance.
Step 3 — The conditional formatting that draws the bars
Select the whole date range (D5 through the last day column and row). Add this formula rule (Format → Conditional formatting → Custom formula):
=AND(D$4<>"",D$4>=$B5,D$4<=$C5)Fill it with a solid color — indigo is our brand color, but any works. The rule reads: color this cell if its date is on or after the row’s start, and on or before the row’s end.
That’s the entire Gantt engine. Every bar is drawn by the spreadsheet.
Two upgrades worth adding
Weekend shading (add this rule first, so bars paint over it):
=WEEKDAY(D$4,2)>5Fill light gray. Suddenly the timeline shows working days vs. weekends.
Done tasks in green (add this rule before the main one):
=AND(D$4<>"",D$4>=$B5,D$4<=$C5,Tasks!$E5="Done")Fill green. Now the chart shows progress, not just schedule — the single most useful view in a project review.
Step 4 — The views around it
A Gantt alone answers “when”. Add three small sheets and it answers everything:
Workload — tasks per teammate, using COUNTIFS:
=COUNTIFS(Tasks!$D$5:$D$54,A2,Tasks!$E$5:$E$54,"Done")Add a completion bar (conditional formatting → data bar on the % column) and you can see at a glance who’s overloaded before someone burns out.
Milestones — key dates with a countdown:
=B2-TODAY()plus a status formula:
=IF(C2<0,"Passed",IF(C2<=7,"Due soon","Upcoming"))Conditional formatting turns “Due soon” amber. Milestones stop slipping quietly.
Status filters — back on Tasks, the dropdown filter on the Status column turns the sheet into a simple board: filter to “Doing” for standup, “Blocked” for what to unblock, “Todo” for what’s next.
Common mistakes
- Typing dates as text.
"Oct 5"breaks every comparison. Use real dates (Format → Number → Date). - Absolute references in the bar rule.
D$4and$B5must be mixed — the row locks, the column floats. If your bars don’t move across columns, check your dollar signs. - Too many day columns. 45 days covers most projects. Beyond that, split the project into phases rather than widening forever.
Skip the build
The whole system — 50-row task sheet with dropdowns and filters, a self-drawing 45-day Gantt with weekend shading and done-bars, workload view, milestone countdown — is pre-built in our Project & Task Tracker for both Google Sheets and Excel. Edit one date and every view updates.
The best project tool is the one your team actually opens. A Gantt in a spreadsheet they already use beats a $15/seat tool nobody logs into.
Related reading: