A project plan is only useful if people can see it. A list of 60 tasks with start and finish dates answers every question in theory and none in practice. A Gantt chart puts each task on a calendar as a bar, so overlaps, gaps and late work jump out in seconds. That is why it is still the standard picture of a project schedule, from a website rebuild to a warehouse rollout.
Microsoft Excel has no Gantt chart button, but it has everything you need to build one that keeps itself up to date. This guide walks through it step by step: structuring tasks into phases, calculating dates in workdays, linking tasks with the four dependency types, drawing the bars, tracking against a baseline and checking whether anyone is overbooked. A worked example with real dates shows what happens when a task slips.
What a Microsoft Excel Gantt chart needs
A Gantt chart is a table on the left and a calendar grid on the right. Each row is a task, and the grid shades the days between its start and finish. Everything else is detail, but the detail is what separates a drawing from a schedule:
- Phases and a WBS number, so 80 tasks read as five groups instead of one long list.
- Durations in workdays, so weekends and holidays are skipped automatically.
- Dependencies, so a late task pushes everything after it.
- Progress, shown as a darker part of each bar.
- A status date and a baseline, so you can tell late from merely busy.
- Resource load, so the plan does not quietly assume someone works 12-day weeks.
You do not need all of it on day one. But if you build the first three in from the start, the rest bolts on without rework.
Step 1: set up phases, tasks and WBS numbers
Create one row per task with these columns: ID, Level, Task, Owner, Duration, Predecessor, Link type, Lag, Start, Finish, % Complete.
The Level column holds 1 for a phase, 2 for a task and 3 for a subtask. From it you can build a WBS number such as 2.2.1 with a counter per level, and indent the task name with a formula like =REPT(" ", Level-1) & Name so the hierarchy is visible.
Phase rows should not be typed. They summarise the tasks under them:
- Phase start: the earliest start of its tasks,
=MIN(range of child starts). - Phase finish: the latest finish,
=MAX(range of child finishes). - Phase progress: weighted by duration,
=SUMPRODUCT(child durations, child % complete) / SUM(child durations).
The weighting matters. If wireframes (6 days) are finished and visual design (8 days) is half done, a simple average says the design phase is 75 percent complete. Weighted by duration it is (6 x 100% + 8 x 50%) / 14 = 71 percent, which is the honest figure, because more of the work sits in the longer task.
Step 2: calculate dates in workdays
Never type a finish date if you can calculate it. With a start date and a duration in workdays:
=WORKDAY(Start, Duration - 1, Holidays)
Holidays is a named range listing the dates to skip. The minus one is there because a task's first day counts: a 5-day task that starts on Wednesday 3 March finishes on Tuesday 9 March, not Wednesday 10 March.
If your team does not work Monday to Friday, use WORKDAY.INTL, which takes a weekend pattern. The pattern is a seven-character string for Monday to Sunday, where 1 means a day off:
=WORKDAY.INTL(Start, Duration - 1, "0000011", Holidays)
Change "0000011" to "0000111" for a four-day week, or to "0000001" for a team that works Saturdays.
A milestone is a task with a duration of zero. It has a date but no length, and it appears as a diamond rather than a bar.
Step 3: link tasks with the four dependency types
A dependency says when a task can happen relative to another one. Project tools such as Microsoft Project use four link types, and Excel can calculate all of them:
| Link | Meaning | Formula for the task's dates |
|---|---|---|
| Finish-to-start (FS) | Starts after the predecessor finishes | Start =WORKDAY(PredFinish, 1 + Lag, Holidays) |
| Start-to-start (SS) | Starts when the predecessor starts | Start =WORKDAY(PredStart, Lag, Holidays) |
| Finish-to-finish (FF) | Finishes when the predecessor finishes | Finish =WORKDAY(PredFinish, Lag, Holidays), then Start =WORKDAY(Finish, -(Duration - 1), Holidays) |
| Start-to-finish (SF) | Finishes when the predecessor starts | Finish =WORKDAY(PredStart, Lag, Holidays), then work the start back the same way |
Finish-to-start covers most tasks. The other three model real overlaps:
- Start-to-start with a lag: visual design can begin three days after wireframes begin, without waiting for every wireframe.
- Finish-to-finish: content entry should end when the front end ends, so both are ready together.
- Start-to-finish: rare, mostly for handovers, such as an old system staying live until the new one starts.
Lag is a delay in workdays. A negative lag, also called lead, lets a task start before its predecessor finishes.
To find the predecessor's dates, look them up by ID: =INDEX(FinishColumn, MATCH(PredID, IDColumn, 0)). Keep predecessors above the tasks that depend on them. A task that points at a later row, or at itself, creates a circular reference, and Excel will stop calculating.
When a task has two predecessors, calculate the start each link would give and take the later one with MAX. Step 6 shows why that second link can matter more than it looks.
Step 4: draw the bars with conditional formatting
Stacked bar charts can draw a Gantt chart, but a conditional formatting grid is easier to read, prints well and handles hundreds of rows. Put dates across a header row and let formatting rules colour the cells.
Say the dates run across row 4 from column M, and the first task is in row 5, with Start in column I, Finish in column J and % Complete in column K.
- 01Header dates. In M4 put the first day you want to show, then
=M4+1to the right. Format them asdwith the month above. - 02Bar rule. Select the grid and add a rule using a formula:
=AND(M$4>=$I5, M$4<=$J5), with a light fill. - 03Progress rule. Add a darker rule above it for the done part:
=AND(M$4>=$I5, M$4<=WORKDAY($I5, ROUND(NETWORKDAYS($I5,$J5,Holidays)*$K5,0)-1, Holidays)). - 04Weekends. Shade non-working days with
=WEEKDAY(M$4,2)>5, or match them againstHolidayswithCOUNTIF. - 05Status line. Add a coloured left border where
=M$4=StatusDate. - 06Milestones. In the grid cells,
=IF(AND($H5=0, M$4=$I5), "◆", "")puts a diamond on the date, where H is the Duration column.
The dollar signs do the work. M$4 locks the date row while the column moves; $I5 locks the start column while the row moves. Get those right and one rule covers the whole grid.
A weekly view for longer projects
For a plan that runs several months, a daily grid is too wide. Switch the header to weeks: start with the Monday on or before the project start, =Start - WEEKDAY(Start, 3), then add 7 to each column. The bar rule becomes "the task overlaps this week":
=AND(M$4<=$J5, M$4+6>=$I5)
A cell with a scale setting (Day or Week) and an IF in the header lets you switch between the two without rebuilding anything.
Step 5: status, progress and a baseline
Status. Compare actual progress with where the plan says the task should be on the status date. Planned progress is the share of the task's workdays that have passed:
=MIN(1, MAX(0, NETWORKDAYS(Start, StatusDate, Holidays) / Duration))
Then a status column can read: Done if progress is 100 percent, Late if the finish date has passed and the task is not done, Behind if actual progress is below planned progress, In progress if it has started, and Not started otherwise. Use a status date cell rather than TODAY(), so last week's report does not change when you reopen it.
Baseline. A baseline is a frozen copy of the agreed dates. When the plan is approved, copy the Start and Finish columns and use Paste Special, Values into Baseline Start and Baseline Finish. From then on, slip in workdays is:
=IF(Finish > BaselineFinish, NETWORKDAYS(BaselineFinish, Finish, Holidays) - 1, -(NETWORKDAYS(Finish, BaselineFinish, Holidays) - 1))
A positive number means late against the plan, a negative number early. On the chart, a thin bar or underline under each task shows the baseline, so the gap is visible as well as counted.
Step 6: a worked example with a slip
A fictional website rebuild starts on Monday 1 March 2027. The company has one holiday, Friday 26 March. Visual design follows wireframes with two links: start-to-start with a 3-day lag, and finish-to-finish with a 2-day lag.
| WBS | Task | Workdays | Link | Start | Finish |
|---|---|---|---|---|---|
| 1.1 | Kickoff workshop | 2 | n/a | Mon 1 Mar | Tue 2 Mar |
| 1.2 | Requirements | 5 | FS 1.1 | Wed 3 Mar | Tue 9 Mar |
| 2.1 | Wireframes | 6 | FS 1.2 | Wed 10 Mar | Wed 17 Mar |
| 2.2 | Visual design | 8 | SS 2.1 +3, FF 2.1 +2 | Mon 15 Mar | Wed 24 Mar |
| 2.3 | Design sign-off | 0 | FS 2.2 | Wed 24 Mar | Wed 24 Mar |
| 3.1 | Front-end build | 12 | FS 2.3 | Thu 25 Mar | Mon 12 Apr |
| 3.2 | Content entry | 6 | FF 3.1 | Mon 5 Apr | Mon 12 Apr |
| 3.3 | Testing | 5 | FS 3.1 | Tue 13 Apr | Mon 19 Apr |
| 4 | Launch | 0 | FS 3.3 | Mon 19 Apr | Mon 19 Apr |
The plan runs 35 workdays, from 1 March to a launch on 19 April. Save this as the baseline.
First slip: wireframes take 9 days instead of 6. Wireframes now finish on Monday 22 March, a slip of 3 workdays. The finish-to-finish link says visual design cannot finish until 2 workdays after that, which is Wednesday 24 March, exactly when it was due to finish anyway. Nothing else moves and launch stays on 19 April. The slip was absorbed by slack in the plan.
Second slip: wireframes take 12 days. Now wireframes finish on Thursday 25 March. Two workdays later is Tuesday 30 March, because the 26th is a holiday. Visual design is pushed to finish on 30 March, sign-off moves with it, the front end finishes on Thursday 15 April, testing ends on Thursday 22 April and launch moves from 19 April to 22 April, 3 workdays late against the baseline.
Now remove the finish-to-finish link and keep only start-to-start. Visual design would still show a finish of 24 March, sign-off would happen before the wireframes were done and the chart would show a launch date that cannot happen. A single start-to-start link describes when overlapping work can begin, but not when it can end. Add the second link whenever the follow-on work needs the first task to be finished.
Step 7: check the resource workload
A schedule can be correct on dates and still impossible, because the same person is on two tasks at once. In the example, one designer owns both wireframes and visual design. Count each person's assigned workdays per week and compare them with the workdays available:
| Week starting | Assigned days | Available days | Load |
|---|---|---|---|
| Mon 8 Mar | 3 | 5 | 60% |
| Mon 15 Mar | 8 | 5 | 160% |
| Mon 22 Mar | 3 | 4 | 75% |
The week of 15 March asks for 8 days of work in a 5-day week, because the start-to-start overlap puts both tasks on the designer at once. The week of 22 March has only 4 available days because of the holiday.
To build this, add one helper column per week next to the task list. Each cell counts the task's workdays inside that week:
=MAX(0, NETWORKDAYS(MAX(Start, WeekStart), MIN(Finish, WeekStart + 6), Holidays))
Then SUMIF each week's column by owner, divide by available days and highlight anything above 100 percent in red. The fix is a planning decision rather than a formula: a second designer, a longer lag or a later start.
Mistakes that break Excel Gantt charts
- Typed finish dates. They do not move when something slips. Calculate them from durations.
- Calendar days instead of workdays. A 10-day task that ignores weekends finishes four days early on paper.
- Phase rows typed by hand. Roll them up from the tasks, or the phase bar will drift away from its contents.
- Only finish-to-start links. Real work overlaps. Use start-to-start and finish-to-finish where they apply, and two links where a task depends on both.
- No baseline. Without one, "late" only means late against today's plan, which moves every time you edit it.
- `TODAY()` in status formulas. Use a status date so reports stay reproducible.
- Hundreds of formatting rules. Copying the grid row by row multiplies conditional formatting rules and slows the file. Apply one rule to the whole range.
A Gantt chart planner that does all of this
Building the grid, the four link types, rollups, a baseline and workload once is a good way to learn how schedules work. Maintaining it across projects takes longer. The Microsoft Excel Gantt Chart Template with Dependencies does it from a single task list.
On the Plan tab you list phases, tasks and subtasks in three levels with WBS numbers, and phase rows take their dates and duration-weighted progress from the tasks under them. Each task takes a duration in workdays or a finish date, and up to two predecessors with finish-to-start, start-to-start, finish-to-finish or start-to-finish links and a lag or lead. A task can follow a whole phase, and links to a missing task are flagged. Dates skip weekends and your holidays, and you choose which days are non-working.
The Gantt Chart tab draws every task by day or by week, with done work shaded darker, late tasks in coral, phase summary bars, milestone diamonds, the baseline as an amber underline and the status date as a line, and it prints on one landscape page. Paste your agreed dates into the baseline columns to see slip in workdays for every task and phase. The Resources tab shows each person's load week by week with over-allocation in red, the Look-ahead tab lists what is due in the next few weeks, and the Dashboard shows progress against plan and forecast finish against baseline. It holds 200 rows and 15 resources, locks its formulas without a password and contains no macros.
For simpler plans, a lighter template may be enough:
Common questions
How do I make a Gantt chart in Microsoft Excel?
List your tasks with a start date and a duration, calculate the finish with WORKDAY, put dates across a header row and use a conditional formatting rule such as =AND(M$4>=$I5, M$4<=$J5) to shade each task's days. Add dependencies so that start dates come from the tasks before them.
Can an Excel Gantt chart handle task dependencies?
Yes. Look up the predecessor's dates by ID with INDEX and MATCH, then calculate the start or finish for the link type: finish-to-start, start-to-start, finish-to-finish or start-to-finish, plus any lag. When a task has two predecessors, take the later result with MAX.
How do I skip weekends and holidays?
Use WORKDAY and NETWORKDAYS with a named list of holidays. For a different working week, use WORKDAY.INTL with a weekend pattern such as "0000011".
What is a baseline in a Gantt chart?
A frozen copy of the approved start and finish dates. Comparing the live plan against it shows how many workdays each task and phase has slipped.
How do I show a Gantt chart by week instead of by day?
Start the header on a Monday and add 7 days per column, then shade a cell when the task overlaps that week: =AND(M$4<=$J5, M$4+6>=$I5).
Does it work in Google Sheets?
The template is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over and charts may look different, so Excel is the better fit.
A Gantt chart that recalculates when a task slips gives you an honest launch date while there is still time to act on it. You can preview the Microsoft Excel Gantt Chart Template with Dependencies free; downloading it is included in the OpenWorksheet monthly plan, and you can also buy this template on its own. The project and dates in this guide are a fictional example.