How to make a Gantt chart in Excel, and when it stops being worth it
Excel will draw a Gantt chart. That part is not in question, and it has been true since long before anyone called it project management software. The harder question is whether the chart still matches reality in week six, when three dates have moved, one task has been split in two, and the person who built the formulas is on holiday.
Most guides stop at the drawing. This one covers the drawing quickly, then spends more time on the parts that decide whether the chart survives: how the dates are calculated, what happens when one task slips, and the specific signs that a spreadsheet has reached the edge of what it does well.
Three ways to draw it, and what each one costs
There are three common approaches, and they are not equivalent. Picking the wrong one for the situation is the usual reason a Gantt chart in Excel feels like more work than it is worth.
| Approach | How it works | Good for | Weak point |
|---|---|---|---|
| Conditional formatting | A grid of date columns, each cell shaded by a formula | Schedules under about six months, weekly or daily grain | The sheet gets very wide; printing needs care |
| Stacked bar chart | A bar chart with the start offset made invisible | Presenting to stakeholders, fitting on one page | Reordering tasks and adding rows means touching the chart series |
| Downloaded template | Someone else has built the formulas and formatting | Getting started the same afternoon | You inherit assumptions you did not choose |
The conditional formatting version is the one worth learning, because it is the one you can repair. When a stacked bar chart misbehaves, fixing it means understanding the chart engine. When a shaded grid misbehaves, fixing it means reading one formula.
Vertex42 publishes a free Gantt chart template that is widely used as a starting point, and Microsoft publishes Gantt chart templates through its template gallery. Both are reasonable places to start if the goal is a working chart today rather than a sheet you fully understand.
Getting the dates right before drawing anything
The chart is a view of the date columns. If those columns are wrong, a prettier chart just makes the wrong dates more convincing.
The rule that matters most is this: type either a start date or a duration, never an end date. End dates should be calculated. A typed end date will quietly disagree with its own start and duration within a fortnight, and nothing in the sheet will flag it.
For calendar days, the calculation is =Start + Duration - 1. The minus one exists because a one day task starts and ends on the same date, and forgetting it adds a day to every bar on the chart.
For working days, which is what most teams actually mean, use =WORKDAY(Start, Duration - 1, Holidays), where Holidays is a named range holding the dates the team does not work. WORKDAY skips Saturdays and Sundays by default. If the team works a different pattern, WORKDAY.INTL takes a weekend argument, so a Sunday and Monday weekend is WORKDAY.INTL(Start, Duration - 1, 2, Holidays).
Going the other way, when both dates are known and the duration is wanted, =NETWORKDAYS(Start, End, Holidays) counts the working days inclusive of both ends.
Two smaller habits save a lot of confusion later. Put the project start date in one cell at the top and refer to it everywhere, so shifting the whole plan is one edit. And keep a Holidays sheet even if it is empty at first, because adding it after the formulas are written means editing every formula.
Building the conditional formatting version
The layout is a task table on the left and a row of dates across the top.
In the header row, put the first date in the first chart column, then fill the rest with =previous + 1 for daily grain or =previous + 7 for weekly. Format those header cells as d or dd mmm and rotate the text if the columns are narrow. Narrow columns, around 20 to 24 pixels for daily grain, are what make the grid read as a chart rather than a table.
Select the whole grid of chart cells, then add a conditional formatting rule using a formula. For a daily grid with the first chart column in E and the task dates in C and D:
=AND(E$4>=$C5, E$4<=$D5)
The dollar signs matter. E$4 locks the row so every cell compares against its own column's date. $C5 locks the column so every cell compares against its own row's task. Set the fill colour and the bars appear.
From there, a few extra rules earn their place. A second rule with a different colour, tested first in the rule order, can shade the baseline dates so planned and actual sit side by side. A rule comparing the header date to TODAY() draws a today line, which is the single most useful marker on the chart. A rule that checks a Status column can grey out completed tasks so attention falls on what is live.
For weekly grain, the comparison changes to an overlap test rather than a containment test, because a task can start and end inside one week column: =AND($C5<=E$4+6, $D5>=E$4).
Dependencies are where Excel stops helping
A Gantt chart's real value is showing what moves when something slips. This is the part Excel does not do on its own.
A simple version works. Add a Predecessor column holding the ID of the task that must finish first, then calculate the dependent task's start with a lookup: =WORKDAY(XLOOKUP($F5, $A:$A, $D:$D), 1, Holidays), which places the start on the next working day after the predecessor's end. Lag can be added by changing the 1. For older versions of Excel, INDEX and MATCH do the same job.
This covers finish to start dependencies, which is most of what a small project needs. It stops short in three places, and the gaps are worth knowing before relying on it.
One predecessor per task. Handling several predecessors means taking the latest of their end dates, which needs a more involved formula and breaks when someone inserts a row.
No cycle detection. Point two tasks at each other and Excel reports a circular reference. It will not tell you which pair caused it in a plan of eighty rows.
No critical path. Knowing which chain of tasks determines the finish date is the main reason to record dependencies at all, and calculating it in a spreadsheet requires a forward and backward pass that is not practical to maintain by hand.
Dedicated tools handle these, though usually not on their entry tier. Microsoft's Planner Plan 1 lists a Timeline (Gantt) view and finish to start dependencies at $10.00 per user per month paid yearly, while baselines, critical path and dependencies with lead and lag sit in Planner and Project Plan 3 at $30.00 per user per month paid yearly. The comparison pages set out how other tools divide the same features between their plans.
Making it readable when the project runs long
A daily grid over six months is roughly 180 columns. It works, but nobody can take it in, and that is usually the point at which people stop opening the file.
Match the grain to the horizon. Daily columns suit a project of six to eight weeks. Beyond that, weekly columns with the overlap formula above keep the chart to a screen or two. A quarterly plan reads better in monthly columns, with the detail kept on a separate daily sheet for the current phase only.
Group tasks under phases. A summary row per phase, with its start from MINIFS across the child tasks and its end from MAXIFS, gives a collapsible outline. Excel's grouping feature then lets a reader open one phase and leave the rest closed. Summary dates must never be typed, because a hand entered phase end will disagree with its own tasks within a couple of weeks.
Freeze the panes. Freeze at the first chart column so the task names and owners stay visible while the dates scroll. Without this, scrolling right leaves a wall of coloured cells with no labels, which is the single most common complaint about Gantt charts built this way.
Decide about printing early. Set the print area to the task columns plus a defined window of dates, repeat the task columns on every page with Print Titles, and fit to one page wide. A chart that prints badly gets rebuilt in a slide deck before every steering meeting, which quietly doubles the maintenance work.
Add a scroll window if the plan is long. One cell holding the first visible date, with the header row calculated from it, turns the chart into a movable window over a longer schedule. It is a small amount of extra formula work and it removes the width problem entirely.
Keeping the chart honest after week one
A Gantt chart decays faster than a task list, because a task list that is out of date merely looks stale while a chart that is out of date looks authoritative.
Freeze a baseline at kickoff. Copy the agreed start and end columns and paste them as values into Baseline Start and Baseline End. Shade them differently on the chart. Without this, the plan can only ever say what the dates are now, never whether they have moved.
Add a Slip column. =NETWORKDAYS(BaselineEnd, End, Holidays) - 1 gives the working days of drift, signed. Conditional formatting that turns it red past a threshold makes the problem visible without reading the chart at all.
Lock the formula columns. Shade calculated cells, then protect the sheet leaving inputs open. Most broken Gantt charts were broken by someone typing a date directly into a calculated cell, and nothing about the sheet will announce that it happened.
Keep one owner and one file. Anyone can request a change, one person applies it. An emailed spreadsheet becomes several spreadsheets inside a week, and the version with the correct dates is rarely the one in the latest email.
Update on a fixed slot. Fifteen minutes in the weekly meeting, filtered to tasks due in the next fortnight, is enough once it is a habit. The failure mode is not that updates are hard. It is that nobody owns the moment they happen.
The signals that a spreadsheet has done its job
A Gantt chart in Excel fits well when one person maintains it, the team is small, and the schedule changes at a steady weekly rhythm. These are the conditions where it stops fitting.
| Signal | What it points to |
|---|---|
| Status is copied from chat and boards into the sheet each week | Work and plan live in different places |
| More than one person needs to move dates | A file edited by many drifts, and formulas get overwritten |
| The same project also exists on a board and in a calendar | Three copies of one schedule, each partly wrong |
| Someone asks which tasks are affected by a slip, and the answer takes an hour | The dependency model is not doing its job |
| Clients or contractors need to see part of the plan | A workbook shares all or nothing |
A project management tool addresses most of this by having people update their own tasks, with the board, the calendar and the timeline reading from one set of data. The trade-off is real: the free layout of a spreadsheet is replaced by the tool's structure, and not every tool puts a timeline view on its free or entry plan. Checking what the first board looks like before moving is worth the twenty minutes, and the getting started guide covers that.
Moving does not have to be all at once. The task list and dates usually move first, while scope, budget and decisions stay in a document for a while longer.
What to change first
Replace every typed end date with WORKDAY, add a Holidays range, and freeze a baseline before the next sprint starts. Run a month of weekly updates against that. If the update keeps turning into transcription from chat and boards, put the next project's task list in a shared tool such as Pinateca and keep the spreadsheet for the numbers it is genuinely good at.
Q1. Does Excel have a built in Gantt chart type?
No. Excel has no native Gantt chart type, so every method is a workaround: a stacked bar chart with the first series made invisible, or a grid of date cells shaded by conditional formatting. Downloaded templates use one of these two approaches underneath.
Q2. Which is better, the stacked bar chart or conditional formatting?
Conditional formatting is easier to maintain and handles a changing task list better, since adding a row needs no chart edits. The stacked bar chart prints and presents more cleanly and fits a long project on one page. Teams that update weekly usually end up preferring conditional formatting.
Q3. How do I exclude weekends and public holidays from the bars?
Calculate end dates with WORKDAY(start, duration - 1, Holidays) and keep the holiday dates in a named range. For a non standard week, WORKDAY.INTL takes a weekend pattern argument. To shade weekend columns on the chart itself, add a conditional formatting rule testing WEEKDAY on the header date.
Q4. Can Excel show which tasks are delayed by a slip?
Only for simple chains. With a Predecessor column and a lookup that sets each start from its predecessor's end, a slip does push the tasks behind it. Several predecessors on one task, circular dependency warnings, and critical path calculation are where the spreadsheet approach runs out.
Q5. How many tasks is too many for an Excel Gantt chart?
There is no hard limit, but past roughly a hundred rows most teams find the sheet slow to scan and slow to update, especially with daily columns over several months. Grouping tasks under phases with summary rows extends the useful range considerably.
