How do you make a Gantt chart in Excel?

Build a Tasks Table with Task, Owner, Start, Duration, and % Complete columns, derive an End column with WORKDAY, then layer a date grid to the right and use conditional formatting to fill cells where the column date sits between each row's Start and End. The resulting matrix is the Gantt visual and updates the moment you change any task.

Should I use a Stacked Bar chart or conditional formatting for the Gantt visual?

Conditional formatting on a date-grid scales better for real planning. The Stacked Bar trick (invisible Start series, visible Duration series) looks clean for screenshots but cannot show today's line, weekends, or progress shading without heavy workarounds. The grid handles all three with one extra rule each.

How do I exclude weekends from an Excel Gantt timeline?

Use WORKDAY(start, duration_days, [holidays]) to compute End so weekends are not counted as project days. Then add a conditional formatting rule on the date grid that greys out cells where WEEKDAY(date, 2) > 5 — that visually marks Saturday and Sunday columns even though they are still drawn.

What are the limits of an Excel Gantt chart compared with project management software?

Excel does not auto-resequence dependent tasks, does not level resources across people, and gets visually crowded past about 40–50 tasks or 16 weeks. For dependency-heavy or multi-project work, move to a real PM tool. For a small team plan with under 50 tasks and a simple schedule, Excel is enough.

Excel

How to Make a Gantt Chart in Excel for Real Project Planning

By Sagnik Bhattacharya 20 Feb 2026 14 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for making a Gantt chart in Excel, with project-timeline visuals.

A Gantt chart in Excel can be either genuinely useful or quietly painful. The difference is not the chart itself. It is whether the underlying task structure, dates, and dependencies are clear enough to make the visual worth maintaining.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

If the sheet is only a pretty bar chart with unstable dates behind it, it will not help the project much.

Requires Microsoft 365 (desktop or web). The Gantt grid uses SEQUENCE, WORKDAY, LET, and structured Table references — all available there. A pre-365 fallback is noted on each step. For broader Excel context see the Excel formulas hub.

Setup time is about 45 minutes for the first chart. Subsequent projects are a 5-minute clone — every formula references the Tasks Table and the project-start cell by name, so blanking the rows and changing one date resets the whole timeline.

What we are building

One sheet, three zones, left to right:

Tasks Table (columns A–G), named tblTasks. Columns: Task, Owner, Status, Start, Duration (workdays), End, % Complete. End is a formula — never typed.
Summary panel (rows 1–4 above the Tasks Table). Project start, project end, days remaining, % complete, late count. Single cell formulas.
Gantt grid (columns I onwards). One column per calendar day across the project. Conditional formatting fills the cell when the column's date sits between that row's Start and End. Today's column is highlighted, weekends are greyed, and the filled portion of each bar shades darker by % Complete.

The discipline: Tasks Table is the single source of truth. Bars and summary numbers are derived. If a date or status looks wrong, fix the Tasks Table — never paint a cell to "correct" the visual. That is the rule that stops Gantt charts becoming pretty lies.

Why the conditional-formatting grid beats the Stacked Bar trick

The classic "Excel Gantt" tutorial uses a Stacked Bar chart with the Start series formatted to "No fill" so only the Duration bars show. It looks clean in screenshots. It breaks down the moment you ask three real questions:

Where is today on the chart? Stacked Bar: a manual vertical line you redraw weekly. Grid: =I$3=TODAY() on one CF rule.
Which days are weekends? Stacked Bar: invisible. Grid: =WEEKDAY(I$3, 2)>5 greys the Saturday/Sunday columns.
How much of each task is actually done? Stacked Bar: not without rebuilding the chart with three series and brittle formulas. Grid: a second CF rule shades the completed portion darker.

If you want a screenshot for a stakeholder slide, the Stacked Bar wins on aesthetics. For a chart you actually plan against, build the grid.

Step 1 — Create the Tasks Table

On a fresh sheet, leave rows 1–4 empty for the summary. In row 6, type the headers across columns A–G:

A6: Task    B6: Owner   C6: Status   D6: Start
E6: Duration (workdays)   F6: End   G6: % Complete

Add one sample row in row 7 — anything, you will overwrite it. Click anywhere inside A6:G7, press Ctrl+T, confirm "My table has headers", and click OK. Excel converts the range to a Table.

Name it. Click inside the Table → Table Design tab → Table Name box (top-left) → type tblTasks → Enter. Every formula below references tblTasks[Start], tblTasks[End], etc. — never raw cell ranges. That is what makes the chart survive someone inserting or deleting task rows.

Add a data-validation dropdown on the Status column. Select the column header drop arrow → Select Column → Data tab → Data Validation → Allow: List → Source: Not started,In progress,Blocked,Done. Tick "In-cell dropdown" and click OK. Status discipline matters because the conditional formatting in Step 4 keys off these exact strings.

Beginner pitfall. Skipping Ctrl+T and leaving the task list as a plain range. Every COUNTIFS, FILTER, and structured reference below stops working the moment someone appends a row past the original last row. Always convert to a Table first.

Step 2 — Compute End from Start + Duration with WORKDAY

End is never typed. It is derived from Start plus Duration, skipping weekends. In F7 (the first data row of the End column), type:

=WORKDAY([@Start], [@[Duration (workdays)]] - 1)

The [@...] syntax is Excel's structured reference for "this row's value of that column". The - 1 is the off-by-one: a 5-workday task that starts Monday ends Friday, not the following Monday. WORKDAY(start, n) returns the date that is n workdays after start, so a 1-day task with no offset would jump to the next day. Subtracting 1 keeps "1 day" meaning "today".

If your project has fixed holidays — bank holidays, company shutdown — list them in a small range (say K2:K10), then pass that as the third argument:

=WORKDAY([@Start], [@[Duration (workdays)]] - 1, $K$2:$K$10)

For non-standard weekends (e.g. Friday-Saturday weekend in some regions), use WORKDAY.INTL with the weekend pattern argument. Pattern 7 = Friday/Saturday, 11 = Sunday only, and so on.

Beginner pitfall. Using =[@Start] + [@[Duration (workdays)]] with simple addition. That counts weekends as workdays and your end dates drift by one day per weekend the task spans. WORKDAY is the fix.

Step 3 — Build the Gantt date grid

Move to column I. The grid needs three rows of headers, then one column per project day.

In I3, type:

=SEQUENCE(1, $B$1 - $B$2 + 1, $B$2, 1)

This spills horizontally: one date per column from $B$2 (project start) to $B$1 (project end). We will fill those summary cells in Step 5; for now type provisional dates so the spill works — say $B$2 = 2026-05-01 and $B$1 = 2026-06-30. The spill widens or narrows automatically when the project end date changes.

Format the spilled row with a narrow column width — Home → Format → Column Width → 3. The column should be just wide enough to hold a single-character cell colour, not a full date. To keep the day-of-month visible, set the cell number format to d (day-of-month only) via Home → Number → More Number Formats → Custom → d. The full date stays in the cell value (so formulas work) but only the day digit shows.

In I2, add a month label that only shows when the month changes:

=IF(MONTH(I3)<>MONTH(H3), TEXT(I3, "mmm yyyy"), "")

Drag (or use a spilled BYCOL) so each column gets the same logic. The first day of each month shows "May 2026", "Jun 2026"; every other column is blank, giving a clean month-band header without repeating the month 30 times.

Pre-365 fallback. If SEQUENCE is unavailable, fill the date row manually: type the project start in I3, then in J3 type =I3+1 and drag right to the project end. Slower, but the rest of the chart still works.

Step 4 — Conditional formatting: bars, progress, today, weekends

Five rules, applied to the Gantt body cells. Select I7: (open-ended down to a generous row, e.g. I7:CZ60), then Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format". Apply each in this order — order matters, because rules higher in the list win on the same cell.

Today's column → vertical highlight. Formula: =I$3=TODAY(). Format: thick left border in a strong colour (red or navy). The I$3 mixed reference (column free, row locked to 3) means every column checks its own header date against today.
Weekend columns → grey. Formula: =WEEKDAY(I$3, 2)>5. Format: pale grey fill. Mode 2 in WEEKDAY makes Monday=1, Sunday=7, so anything >5 is Saturday or Sunday.
Progress bar (completed portion) → dark fill. Formula: =AND(I$3>=$D7, I$3<=$D7 + ($F7-$D7) * $G7). Format: dark blue fill. The right edge of the dark band is Start + (End − Start) × % Complete, so a 50% complete task fills half its bar in dark blue.
Bar (full task duration) → light fill. Formula: =AND(I$3>=$D7, I$3<=$F7). Format: light blue fill. This rule sits below the progress rule, so the completed portion stays dark and the uncompleted portion shows light.
Done tasks → green strike. Formula: =AND(I$3>=$D7, I$3<=$F7, $C7="Done"). Format: solid green fill. Place this rule above the bar rule so finished tasks override the blue.

The mixed references are the trick. I$3 = "the date in this column's header". $D7 = "this row's Start". $F7, $G7, $C7 = this row's End, % Complete, Status. Together they let one rule cover every cell in the grid.

Beginner pitfall. Writing I3 instead of I$3, or D7 instead of $D7. Without the dollar signs, each cell evaluates against an offset row/column and the bars draw in the wrong places. Type the formula carefully, then test on one cell before applying to the whole range.

Step 5 — Build the summary panel

In rows 1–4 above the Tasks Table, derive the project-level numbers:

A1: PROJECT END        B1: =MAX(tblTasks[End])
A2: PROJECT START      B2: =MIN(tblTasks[Start])
A3: DAYS REMAINING     B3: =MAX(0, NETWORKDAYS(TODAY(), B1))
A4: % COMPLETE         B4: =SUMPRODUCT(tblTasks[% Complete] * (tblTasks[End] - tblTasks[Start] + 1)) / SUM(tblTasks[End] - tblTasks[Start] + 1)
D1: LATE TASKS         E1: =COUNTIFS(tblTasks[End], "<"&TODAY(), tblTasks[Status], "<>Done")
D2: BLOCKED            E2: =COUNTIFS(tblTasks[Status], "Blocked")
D3: STARTING THIS WEEK E3: =COUNTIFS(tblTasks[Start], ">="&TODAY(), tblTasks[Start], "<="&TODAY()+7)

Format B4 as a percentage. B4 is the duration-weighted % complete: each task's progress weighted by its length in days. A 10-day task at 50% counts more than a 1-day task at 100%, which matches how stakeholders read overall progress.

Format E1 with conditional formatting: red fill if value > 0. The same for E2. These are the two numbers a project lead glances at first thing on Monday.

Beginner pitfall. Using =TODAY() - B2 for "days elapsed". That counts weekends as elapsed project days. NETWORKDAYS(B2, TODAY()) is the correct version — and the MAX(0, ...) wrap stops it going negative when the project hasn't started.

Step 6 — Add a "needs attention" live view

Beside the date grid, a one-formula spill of tasks that need a decision today: anything blocked, or anything past its End date but not Done. In a free cell — say I65:

I64: NEEDS ATTENTION
I65: =FILTER(tblTasks[[Task]:[End]],
            (tblTasks[Status]="Blocked") +
            ((tblTasks[End]<TODAY()) * (tblTasks[Status]<>"Done")),
            "All clear")

The + between the two condition arrays is logical OR (true if either is true). The result spills task name, owner, status, start, duration, and end for any row meeting either condition. The third argument is the friendly fallback for an empty result.

Pre-365 fallback. Use AutoFilter on the Tasks Table — Data → Filter — and filter Status to "Blocked", or use a custom date filter on End. Two clicks instead of one formula, but it works in any Excel version.

Step 7 — Worked example: 12-task website relaunch

Drop these 12 tasks into the Table and walk through the chart. Project start 2026-05-04 (Monday), project end derived from the longest End.

Task                       Owner    Status        Start       Duration  %
Stakeholder kickoff        Alex     Done          2026-05-04  1         100
Information architecture   Priya    Done          2026-05-05  4         100
Wireframes                 Marcus   In progress   2026-05-11  5         60
Visual design              Marcus   In progress   2026-05-18  8         25
Content audit              Sara     In progress   2026-05-11  6         50
Content rewrite            Sara     Not started   2026-05-25  10        0
Frontend build             Dev1     Not started   2026-06-01  15        0
CMS integration            Dev2     Not started   2026-06-08  10        0
QA pass 1                  Priya    Not started   2026-06-22  4         0
Stakeholder review         Alex     Not started   2026-06-26  2         0
Fixes from review          Dev1     Blocked       2026-06-30  5         0
Launch                     Alex     Not started   2026-07-07  1         0

Expected summary (assuming today is 2026-05-25): Project end 2026-07-07, project start 2026-05-04, days remaining ≈ 32 (workdays). % Complete ≈ 17% (the duration-weighted average — the two short Done tasks count for very little against the unstarted long tasks). Late tasks 2 (Wireframes ended 2026-05-15 and Content audit ended 2026-05-18 are both past End and still In progress — exactly the rows the chart is meant to surface). Blocked 1 (Fixes from review — the dependency on Stakeholder review needs to land first). Starting this week 2 (Content rewrite starts today, Frontend build starts on day 7 — the COUNTIFS bound is inclusive at TODAY()+7).

The Gantt grid shows a green band on the first two rows (Done), a half-dark / half-light band on the in-progress rows reflecting the % Complete, light bands on the Not started rows, and the today column highlighted vertically. Saturday and Sunday columns are pale grey. The "needs attention" spill shows three rows: Wireframes, Content audit, and Fixes from review — the two overdue-but-not-Done plus the one Blocked.

Now mark "Wireframes" as Done by changing its row to 100% and Status to Done — its bar turns solid green, the late-tasks count drops to 1, % Complete in the summary ticks up, and Visual design's downstream date does not shift (Excel does not auto-resequence — see the limits section below).

The full formula reference

Where	Formula	What it does
F7 End column	`=WORKDAY([@Start], [@[Duration (workdays)]] - 1)`	End date that skips weekends; the `- 1` aligns "1 day" with "today"
F7 End with holidays	`=WORKDAY([@Start], [@[Duration (workdays)]] - 1, $K$2:$K$10)`	Same, also excluding listed holiday dates
I3 Date row	`=SEQUENCE(1, $B$1 - $B$2 + 1, $B$2, 1)`	Spills one calendar date per column from project start to end
I2 Month label	`=IF(MONTH(I3)<>MONTH(H3), TEXT(I3, "mmm yyyy"), "")`	Month name on the first column of each month, blank otherwise
CF: today line	`=I$3=TODAY()`	Vertical highlight on today's column
CF: weekend grey	`=WEEKDAY(I$3, 2)>5`	Greys Saturday and Sunday columns
CF: progress (dark)	`=AND(I$3>=$D7, I$3<=$D7 + ($F7-$D7) * $G7)`	Dark fill for the completed portion of each bar
CF: bar (light)	`=AND(I$3>=$D7, I$3<=$F7)`	Light fill across the full task duration
CF: done (green)	`=AND(I$3>=$D7, I$3<=$F7, $C7="Done")`	Solid green for finished tasks; rule sits above the bar rule
B1 Project end	`=MAX(tblTasks[End])`	Rightmost End date in the Table
B2 Project start	`=MIN(tblTasks[Start])`	Earliest Start date in the Table
B3 Days remaining	`=MAX(0, NETWORKDAYS(TODAY(), B1))`	Workdays between today and project end; floor at 0
B4 Weighted % complete	`=SUMPRODUCT(tblTasks[% Complete] * (tblTasks[End] - tblTasks[Start] + 1)) / SUM(tblTasks[End] - tblTasks[Start] + 1)`	Each task's progress weighted by its duration in days
E1 Late tasks	`=COUNTIFS(tblTasks[End], "<"&TODAY(), tblTasks[Status], "<>Done")`	Tasks past End and not Done
E2 Blocked	`=COUNTIFS(tblTasks[Status], "Blocked")`	Count of currently blocked rows
E3 Starting this week	`=COUNTIFS(tblTasks[Start], ">="&TODAY(), tblTasks[Start], "<="&TODAY()+7)`	Tasks whose Start falls in the next 7 calendar days
I65 Needs attention	`=FILTER(tblTasks[[Task]:[End]], (tblTasks[Status]="Blocked") + ((tblTasks[End]<TODAY()) * (tblTasks[Status]<>"Done")), "All clear")`	Live spill of blocked OR overdue tasks

Variations: customise for your project shape

The skeleton (tblTasks + WORKDAY end + SEQUENCE date row + the five CF rules) stays identical. What changes is the columns you keep on the Table and the metrics on the summary.

Use case	Extra Tasks columns	Extra summary metric
Software sprint plan	Story points, Sprint number	Points done this sprint vs committed
Construction / fit-out schedule	Trade, Predecessor, Float	Critical-path slack remaining
Marketing campaign timeline	Channel, Asset link	Assets shipped per channel
Client engagement / consulting	Client, Billable hours	Hours logged this week vs budget
Event production plan	Venue, Vendor, Confirmation status	Days to event; unconfirmed vendor count
Multi-project portfolio Gantt	Project, RAG status	Projects at Red status; portfolio % complete

For a portfolio Gantt, add a Project column and use a slicer (Table Design → Insert Slicer → Project) so the same chart can flip between projects without rebuilding.

Common mistakes

Plain range instead of an Excel Table. WORKDAY in F7 stops dragging into new rows; the End column drifts; the bars draw against stale data.
Manual End dates instead of a formula. The moment a task slips by two days, every downstream End needs hand editing. Drive End from Duration and the slip propagates correctly.
Adding without WORKDAY. =Start + Duration counts Saturday and Sunday as project days. A 5-day task starting Monday "ends" Saturday — wrong by 2 days, and worse the longer the task.
Conditional format using I3 not I$3. The row anchor matters. Without the dollar on the row, each cell evaluates against an offset header date and bars appear in the wrong places.
One CF rule that tries to do too much. Five small rules in the right order beat one big nested-AND rule. Each rule is independently debuggable; a megarule fails silently.
Treating the Gantt as the source of truth. The Table is the source. The chart is a derived view. If a bar is in the wrong place, the fix is in the row's Start, Duration, or Status — never paint the cell.
Hand-drawing dependencies as arrows. Excel does not auto-update those arrows when dates shift. Either keep dependencies in a separate Predecessor column and live without arrows, or use a real PM tool.

Troubleshooting

End dates show as a 5-digit number like 46000. The cell is formatted as Number, not Date. Select the End column → Home → Number format → Short Date.
SEQUENCE returns #SPILL!. A neighbouring cell in the spill range is occupied. Clear the range to the right of I3 or move the formula.
The bars do not draw at all. The CF rule is referencing the wrong cells. Click any grid cell, Home → Conditional Formatting → Manage Rules → check that "Applies to" covers the full grid (e.g. =$I$7:$CZ$60) and that the formula uses I$3 and $D7, not absolute references.
The bars draw on weekends too. Working as designed — the bar represents calendar days the task is "alive", but the weekend grey rule should overlay it. Check rule order: weekend grey must be above the bar rule, or change the bar formula to also exclude weekends with WEEKDAY(I$3, 2)<6.
Today's vertical line moves to the wrong column. The I$3 reference is mistyped (often as $I$3, which locks every cell to column I). Edit the rule and remove the column-anchor dollar sign.
WORKDAY returns #NAME?. Older Excel without the analysis ToolPak. In M365 this never happens; in 2010 or earlier, enable the ToolPak via File → Options → Add-ins.
"Days remaining" shows a negative number. Project end is in the past. The MAX(0, ...) wrap fixes the display but not the underlying issue — your end date needs revising.

When this is not the right tool

Dependencies that auto-resequence. Excel does not natively shift Task B's Start when Task A's End slips. If your project has >10 dependencies that change weekly, use a real PM tool (Asana Timeline, Smartsheet, MS Project, ClickUp Gantt). For 1–3 hand-managed dependencies, Excel is fine.
Resource levelling across people. "Marcus is double-booked in week 24" — Excel will not flag it without a custom check. PM tools do this natively.
More than ~50 tasks or longer than 16 weeks. The grid becomes unwieldy: too many columns to fit on a screen, too many rows to glance at. Either split the project into phases (one Gantt per phase) or move to a PM tool.
Stakeholders who want a clickable web view. Excel Gantts are screenshots in a deck. If stakeholders need to drill into tasks, use a tool with a shareable web view.
You actually want a project tracker, not a timeline visual. Use the project tracker in Excel instead — same Table-driven discipline, optimised for status and ownership rather than dates.