Build a Gantt Chart in Excel or Google Sheets: Worked Table and Formulas
Shusaku Yosa
A Gantt chart displays each task’s start and finish as a horizontal bar. Excel and Google Sheets can create a simple version with date columns and conditional formatting. A readable bar is useful, but the plan also needs completion conditions, predecessors, and realistic availability.
The following tables are a copyable starting point, not a downloadable product or an automatic scheduler. Dates, people, and progress are fictional. Formulas use English function names and comma separators; check date values, separators, and formatting for your spreadsheet’s locale.
What a Gantt chart shows, and what still needs planning
A chart shows overlap and sequence in the dates you enter. It cannot establish whether those dates are feasible. Three bars for one person may represent three half-hour activities or three full days. Elapsed duration and person-hours are different quantities.
Item | What this chart records | What still needs checking |
|---|---|---|
Duration | Start-to-finish bar | Work, review waits, and non-working time |
Dependency | Predecessor ID | Consequences when predecessor dates change |
Capacity | Overlapping bars by owner | Estimated person-hours and available time |
Completion | Task and exit condition | Evidence of acceptance or review |
Use the WBS examples to identify the work and scheduling method to establish order and feasible dates. This chart communicates that plan. The formulas below do not calculate a critical path or automatically reschedule dependent tasks.
Copyable columns, dates, and progress examples
Put headings in row 1 and tasks from row 2. Use columns A through K as below, then place consecutive date values from L1 to the right. Keep optional non-working dates in A2:A20 of a separate sheet named Holidays, maintained for your organization’s calendar.
Columns | Fields | Input rule |
|---|---|---|
A–D | ID / category / task and completion condition / owner | Keep IDs stable and name the required output |
E–F | Start / finish | Use date values with finish on or after start |
G | Working days | Use the NETWORKDAYS formula below |
H | Progress | 0% to 100%, stored as 0 to 1 |
I–K | Status / predecessor ID / notes | Agree status words and dependency review |
L onward | Dates in row 1 | Consecutive date values, not text |
This fictional snapshot is dated October 16, 2026. For illustration only, its Holidays sheet contains October 13, and weekends are Saturday and Sunday. It is not a national holiday calendar. The working-day counts are 2, 2, and 1 under those assumptions.
A:ID | B:Category | C:Task / completion | D:Owner | E:Start | F:Finish | G:Working days | H:Progress | I:Status | J:Predecessor | K:Notes |
|---|---|---|---|---|---|---|---|---|---|---|
T-01 | Draft | Submit first draft for review | A | 2026-10-12 | 2026-10-14 | 2 | 100% | Complete | Exclude illustrative holiday on the 13th | |
T-02 | Review | Return review findings | B | 2026-10-15 | 2026-10-16 | 2 | 50% | In review | T-01 | One of two equally weighted checks complete |
T-03 | Publish | Publish reviewed content | A | 2026-10-19 | 2026-10-19 | 1 | 0% | Not started | T-02 | Requires review completion |
The 50% entry means one of two equally weighted checks is complete in this example. Do not impose 25% steps on every kind of work. If no meaningful percentage exists, track status and remaining work. A displayed 100% is stored as 1; entering 100 and applying percentage formatting is different.
Draw task bars from start and finish dates
For this example, select L2:AP100 and create a formula-based conditional formatting rule with a fill color. Change the range for your required dates and task rows. Use:
=AND(ISNUMBER($E2),ISNUMBER($F2),$F2>=$E2,ISNUMBER(L$1),L$1>=$E2,L$1<=$F2)
The dollar signs keep the start and finish columns fixed and the timeline header in row 1. Moving right changes the timeline date; moving down changes the task. The bar includes both dates and all calendar days between them, including holidays. In G2, use a separate working-day calculation and copy it down:
=IF(AND(ISNUMBER(E2),ISNUMBER(F2),F2>=E2),NETWORKDAYS(E2,F2,Holidays!$A$2:$A$20),"")
The Microsoft NETWORKDAYS reference and Google NETWORKDAYS reference describe start, finish, and optional holidays. Two working days do not imply sixteen person-hours or two person-days of effort. A blank result caused by missing or reversed dates is an input problem to resolve, not evidence that the work has disappeared.
Show holidays, completion, and overdue work
Separate formatting areas to reduce conflicts. Highlight today and weekends in L1:AP1, draw bars in L2:AP100, and highlight overdue tasks in A2:K100. The following rules respectively test today, a weekend, and an unfinished task whose finish date has passed:
=L$1=TODAY()
=AND(ISNUMBER(L$1),WEEKDAY(L$1,2)>5)
=AND(ISNUMBER($E2),ISNUMBER($F2),$F2>=$E2,$F2<TODAY(),ISNUMBER($H2),$H2<1,$I2<>"Cancelled")
Place the today rule above the weekend rule if both colors could apply. Google’s conditional formatting guidance explains rule order; Microsoft’s guidance covers priority and Stop If True. Check whether that setting applies to the Excel rule you use instead of assuming identical controls. Complete rows with H=100% are excluded from the overdue test; Cancelled is the exact excluded status word in this English version.
Input to test | Expected result | Correction if needed |
|---|---|---|
Blank start | No bar; G is blank | Confirm the missing date |
Finish before start | No bar; G is blank | Correct date order |
Text instead of date | Do not use until recognized as a date value | Check locale and input format |
Finish is today | Not overdue | Use F<TODAY() |
H is 100% / 1 | Not overdue | Check stored percentage |
Add a holiday | G decreases; calendar bar stays the same | Check holiday date values |
Change predecessor finish | Successor does not move automatically | Review predecessor IDs |
TODAY provides a date comparison, not a notification. The overdue formula excludes blank progress cells, so review those separately. Agree status vocabulary and validate percentage inputs before relying on color as a work queue.
Check predecessors and capacity before changing dates
If T-01 slips, check whether T-02’s reviewer can work on the new dates and whether T-03 is affected. A predecessor ID alone does not move later dates. Compare a plan worked backward from the deadline with one built forward from actual availability.
For example, four person-hours of review against two available person-hours produces a two-hour shortfall. This fictional calculation assumes the same period and scope. Extending the date by one day does not necessarily solve it: other commitments, reviewer availability, and rework still matter.
- Record the changed task and reason.
- Check predecessor and successor IDs and fixed commitments.
- Compare required effort with the owner’s available time.
- Agree options for scope, owner, or dates with the responsible decision maker.
- Update affected rows manually and communicate the change.
Define acceptance conditions for publication or handover using the milestone examples. Review contingency against waiting, uncertainty, and past variation instead of applying a universal percentage buffer.
Update rules and when to review your spreadsheet setup
Agree who updates the chart and when. Change forecasts when dates move, outputs are submitted, or reviews return. In regular reviews, inspect missing dates, overdue work, and inconsistent dependencies. Retain the reason for significant changes so the plan can support a useful retrospective.
Collaboration depends on version and storage. Microsoft documents Excel co-authoring in supported environments, so editor count alone is not a reason to reject it. Check permissions, version history, and the required sharing scope.
If successor adjustments are repeatedly missed, capacity must be re-entered elsewhere, or access requirements cannot be met, review both the process and the tool. Specify the dependency calculations, permissions, history, and export you need, then evaluate representative work against those requirements.