Article · Project management
How to Make a Gantt Chart in Excel: Step-by-Step Guide with Formulas
A Gantt chart shows at a glance which task runs when, what depends on what, and where a project is stuck. You don’t need expensive software: with one table, three formulas and conditional formatting, Excel draws the plan for you.
What is a Gantt chart?
A Gantt chart is a bar schedule: tasks on the left, a timeline of days or weeks across the top, and each bar shows when a task starts and how long it lasts. It works for anything with a deadline – moving a warehouse, rolling out software or opening a restaurant.
Step 1: Set up the task list
Create a table with these columns. Format it as an Excel table with Ctrl + T so formulas extend automatically.
| Column | Content |
|---|---|
| A – Task | e.g. “Order shelving” |
| B – Owner | Name or team |
| C – Start | Date |
| D – Duration (days) | Number |
| E – End | Formula |
| F – Progress | 0–100 % |
Step 2: Calculate the end date
The end date follows from start and duration. To skip weekends, use working days:
End (calendar days) = C2 + D2 − 1
End (working days) = WORKDAY(C2, D2 − 1)
If a task depends on another, set its start to the next working day after that task ends: =WORKDAY(E2, 1). When the first task moves, the whole plan moves with it.
Step 3: Build the timeline
From column H, enter the project start date in row 1 and next to it =H1 + 1 for days or =H1 + 7 for weeks. Drag the formula to the right. With the number format DD/MM and narrow columns (width 3–4) you get a compact timeline.
Step 4: Draw bars with conditional formatting
Select the area below the timeline (e.g. H2:AF30) and choose Home › Conditional Formatting › New Rule › Use a formula. This rule colours every cell whose date falls between start and end:
=AND(H$1 >= $C2, H$1 <= $E2)
The dollar signs matter: H$1 locks the timeline row, $C2 and $E2 lock the start and end columns.
Step 5: Show progress and delays
Two more rules turn the plan into a status report. The first colours the completed part darker:
=AND(H$1 >= $C2, H$1 < $C2 + $D2 * $F2)
The second marks open days that are already in the past – the delay – in orange:
=AND(H$1 >= $C2 + $D2 * $F2, H$1 <= $E2, H$1 < TODAY())
A fourth rule, =H$1 = TODAY() with a red border, marks today. Mind the rule order: delay and progress go above the base rule.
Common mistakes
- Text instead of dates: if a date is left-aligned, Excel doesn’t see it as a date and the formulas return nothing.
- Missing dollar signs: without
$the references shift when copied and the bars jump. - Timeline too fine: for projects longer than three months, weeks read better than days.
- Dependencies typed by hand: link start dates with formulas, or the plan breaks after the first change.
- No progress tracked: a Gantt chart without actual status is just a wish list.
When Excel is enough – and when it isn’t
Excel works well for projects with up to about 50–80 tasks and a small team. If you need resource planning across many projects, an automatically recalculated critical path or many people editing at once, a project management tool is worth it.
Free Excel template
The Project Gantt Manager has every step of this article built in: the Gantt chart draws itself, late tasks turn orange, and a dashboard compares progress and budget against the plan. No macros, 12 languages.