HiddenA Gantt chart is a calendar for a project: each task is a bar, and the bar starts and ends on dates. It is easy to draw one by hand. The useful kind is a chart whose bars move when something changes, because real projects slip. This guide explains how that works in a spreadsheet, with a worked example, and where a spreadsheet stops being enough.
What makes a Gantt chart useful
A chart you paint by hand is a picture. A chart drawn from dates is a model. In the second kind, each task has a duration and, if it depends on another task, the number of that task. The sheet works out when it starts and ends. Change one duration and every task that depends on it moves. You see at once what a delay does to the end date.
That is the whole idea. Everything else, such as colours, milestones and a weekly summary, rests on it.
The parts of a plan
- Task list: one row per task, with an ID, name, owner and duration.
- Predecessor: the ID of the task that must finish first. A lag adds waiting days, for example for paint to dry.
- Not-before date: the earliest day a task may start, for reasons outside the plan: a supplier delivers on the 9th, or a person returns from leave.
- Milestones: points with no duration, such as "client approves". They carry a target date and a forecast.
- Status and percent complete: what is done, so that the chart can show late and behind-plan tasks.
How a start date is worked out
In the simplest rule, a task starts the day after its predecessor finishes, plus any lag. If a not-before date is later than that, the task waits for it. With neither, it starts on the project start date. The finish date is the start plus the duration, minus one, because a task of one day starts and ends on the same day.
A chart is then drawn by conditional formatting. Microsoft's help describes conditional formatting as a way to colour cells when they meet a rule. Here, a cell in the chart grid is coloured if its date falls between a task's start and its finish. No chart object and no macro is needed, and the colours follow the dates.
A worked example
A small website project starts on Monday 2 November 2026. This version counts calendar days, which matters below.
| ID | Task | Days | After | Starts | Finishes |
|---|---|---|---|---|---|
| 1 | Draft content | 3 | none | Mon 2 Nov | Wed 4 Nov |
| 2 | Design pages | 2 | task 1 | Thu 5 Nov | Fri 6 Nov |
| 3 | Review and approve | 1 | task 2 | Sat 7 Nov | Sat 7 Nov |
The review lands on a Saturday, because the sheet counts every day of the week. A person would not schedule it there. There are two honest fixes. Either give the review a not-before date of Monday 9 November, or count only working days in your durations. A workbook that counts calendar days will not skip weekends or public holidays by itself, so say which rule you use and put weekend and holiday gaps in as not-before dates or lag.
Now suppose drafting takes 5 days, not 3. Task 1 finishes on Sat 6 Nov, task 2 runs Sun 7 to Mon 8 Nov, and task 3 would start on Tue 9 Nov. Two days of delay in the first task moved the others by two days, and you saw it without redoing the plan by hand. That is the value of a dependency-driven sheet.
Late and behind
Two words are worth keeping apart. A task is late when its finish date has passed and it is not done. A task is behind when it is not yet late but its percent complete is below where it should be at the status date, by more than a tolerance you choose. Late is a fact. Behind is a warning. A weekly status page that lists late tasks, tasks due in the next 7 days and progress against plan turns the chart into a routine.
Workload by owner
A schedule can look fine and still be impossible if one person has six tasks at once. A workload table counts how many tasks each person has active in each week. Mark the weeks above a limit you set, for example three tasks at a time, and you see the clash before it happens. This counts tasks, not hours, so it is a rough check. It does not move tasks for you. You decide what to move.
Why plans run late
Buehler, Griffin and Ross (1994) studied why people underestimate how long their own tasks will take. They found that people tend to predict completion times that are too short, even when they know that similar tasks took longer in the past. Their name for this is the planning fallacy. The practical lessons are modest. Compare your estimates with how long similar tasks really took, add a buffer for the tasks you are least sure of, and update the plan weekly, because an old plan only gives false comfort.
What a spreadsheet does not do
Be clear about the limits.
- One predecessor per task. Many real tasks wait for two things. In a simple sheet, use a not-before date for the second.
- No critical-path analysis. The sheet does not tell you which delays move the end date and which do not.
- No resource levelling. It shows overload, but it does not rearrange the work.
- No live teamwork. A workbook is one file. Several people editing copies will disagree.
- Calendar days. As in the example, weekends and holidays are your job.
If you need those features, or hundreds of tasks, use project-management software. If you plan a project with a few dozen tasks and one or two people, a spreadsheet you understand is often better than a tool you do not.
A simple routine
Set a status date each week. Update percent complete and status for each task. Look first at the late list, then at what is due in the next seven days, then at the milestones, where target and forecast are compared. If a milestone's forecast is later than its target, tell the people who depend on it that day.
Tool that fits
The Project Planner & Gantt Chart (Excel, EUR 17, paid once) takes up to 100 tasks with owner, duration, predecessor, lag, an optional not-before date, status and percent complete. Start and finish dates are calculated by the rule above, counting calendar days, with one predecessor per task. The Gantt chart is drawn by conditional formatting, 12 weeks by default and up to 26, with progress shading, red for late tasks, amber for behind and diamonds for milestones. The Milestones sheet compares target and forecast with an On track or At risk status. Workload by owner and week marks weeks over your limit, and a weekly status page summarises progress, late tasks and the next 7 days. An example project is included, and it works in Excel 2016 and later, Google Sheets and LibreOffice, with no macros. It does not do critical-path analysis or resource levelling, and its dates are only as good as the numbers you enter.
What to do next
The Project Planner & Gantt Chart takes up to 100 tasks and works out start and finish dates from each task's predecessor, then draws the Gantt chart, milestones, workload by owner and a weekly status page. Excel file, €17 once; no macros.
Questions people ask
Can Excel draw a Gantt chart that moves when a task slips?
Yes, if the start dates are formulas. Each task starts the day after its predecessor finishes, and the chart is drawn from the dates with conditional formatting. When a duration changes, the dates after it change and the bars move. A coloured strip typed by hand does not do this.
What is a predecessor?
A predecessor is a task that must finish before another task can start. If review depends on design, design is the predecessor of review. The most common kind is finish to start: the next task begins when the earlier one ends.
Is a spreadsheet Gantt chart enough to manage a project?
For a small project with simple dependencies, often yes. It does not do critical-path analysis, resource levelling or live team editing. If you need those, or hundreds of tasks, use project-management software.
Sources
- Buehler, Griffin and Ross (1994), Exploring the planning fallacy: why people underestimate their task completion times, Journal of Personality and Social Psychology 67(3), 366-381
- Microsoft Support, Use conditional formatting to highlight information in Excel (the feature used to draw the bars from dates)
We checked each source on 17 October 2026. If a fact here is out of date, tell us at hello@hiddenway.info.