Gantt chart template (Excel)
A Gantt chart lays a project's tasks out as bars against a calendar, so you can see what happens when, what overlaps and what's behind. In this free Excel template you type each task's start date and the number of working days it takes. The end date, a status and the bars across a 26-week timeline fill in by themselves, with the finished share of each bar shaded darker.
Running a company of 30 to 200 people? Documents like this are now worth money.
It's built from ordinary formulas and conditional formatting, with no macros to enable. The download has a blank chart with room for 40 tasks, a filled-in example and a read-me sheet.
What's in the template
That's the template's Example sheet, drawn here for the first 16 of its 26 weeks: a fictional finance team moving to a new accounts system. The first two tasks are finished, so their bars are dark all the way along. Choosing the system is half done and mapping the current processes a fifth done, so their bars start dark and turn light. Nothing is scheduled over the two Christmas weeks.
The columns
| Column | What goes in it |
|---|---|
| Task | You type it: one task per row |
| Owner | You type it: the one person responsible |
| Start | You type it: the task's first day |
| Days | You type it: working days needed, counting the start day |
| End | Calculated with Excel's WORKDAY function, which skips Saturdays and Sundays |
| % done | You type it: from 0% to 100% |
| Status | Calculated: Done, Late, In progress or Not started |
| 26 week columns | Calculated: each headed with the date its week starts; dark for done, light for still to do |
Cell C3, labelled "Timeline starts (a Monday)", sets the first week. Change it and every week heading and bar moves with it. Task, owner, start, days, % done and C3 are the pale-yellow input cells; everything else is a formula. The task columns and week headings stay on screen as you scroll.
How to fill it in
- Set the timeline start. Type the Monday of your project's first week into C3. The 26 week headings re-date themselves.
- List the tasks in the order they'll happen. Aim for tasks lasting a day to a few weeks, and split anything longer so that progress shows.
- Enter an owner, a start date and the days for each. Days means working days including the start day, so a task that starts on a Monday and takes 5 days ends that Friday.
- Leave End and Status alone. They're formulas, and so are the bar cells.
- Add milestones as one-day tasks, named so they stand out: "Milestone: contract signed".
- Update % done once a week, before the project meeting. Base it on finished output, not time spent.
- When a task moves, move the tasks that depend on it. The template doesn't link tasks, so a later start for one doesn't push the next one back (the FAQ below has a formula that does). Note the reason for the slip in your RAID log.
How the bars and status work
Each of the 26 timeline columns is one week, headed with the date it starts. A week is shaded if the task runs on any day in it, so the chart shows weeks rather than days: a two-day task fills one whole week's cell.
The shading follows % done. The template finds the date that share of the way through the task; weeks that begin before that date are dark and the rest are light. Because the columns are weeks, the dark part shows progress to the nearest week rather than the exact day.
Status compares the dates with today's date, so it changes as time passes:
- Done (green): % done is 100%.
- Late (red): the end date has passed and the task isn't 100% done.
- In progress: the start date has arrived and the end date hasn't passed.
- Not started: the start date is still ahead.
Working days, weekends and holidays
The End column uses Excel's WORKDAY function. It counts Monday to Friday as working days and skips weekends, and it only skips public holidays if it's given a list of them. This template doesn't give it one, so a task that runs over a holiday really ends a working day later than the chart shows.
There are two easy fixes: add a day to the task's Days for each holiday it spans, or start the task after the holiday. The example does the second, so nothing runs in the weeks of 21 and 28 December and the next task starts on 4 January.
UK bank holidays differ between England and Wales, Scotland and Northern Ireland; GOV.UK lists them for each. In the US, the Office of Personnel Management lists the federal holidays; add the ones your company closes for.
Common mistakes
- Too many rows. A chart with 150 tasks stops being read. Keep it at the level the sponsor cares about and let teams keep their own task lists.
- Calendar days in the Days column. Two weeks of work is 10 working days, not 14.
- Expecting later tasks to move by themselves. Here they don't, so check every task that waits on one you've moved.
- % done by guesswork. "About half" for weeks on end usually means stuck. Tie progress to something finished: a document approved, a system configured, a room cleared.
- No baseline. Before the first update, copy the sheet and name it Baseline, so you can show later how the dates moved.
- Tasks without an owner. Nobody updates them, and nobody chases them when they turn red.
Frequently asked questions
What if my project runs longer than 26 weeks?
Move the timeline start in C3 forward to see later weeks, or split the project into phases with a sheet each: right-click the Gantt chart tab, choose Move or Copy, and tick Create a copy. The copy keeps all the formulas and shading.
Can tasks be linked so one starts when another finishes?
Not automatically, but one formula does it. In the Start cell of the later task, type =WORKDAY(E7,1), replacing E7 with the End cell of the task it waits for. It then starts on the next working day after that task ends, and moves whenever that task moves.
Is a Gantt chart the same as a project plan?
No. The Gantt chart is the schedule: tasks, dates and progress. A project plan also covers scope, budget, people, risks and who approves changes, and points to the Gantt chart for the dates.