Gantt chart dependencies in Excel: linking tasks without breaking the sheet
The plan lives in a spreadsheet. Every task has a start date and an end date, the bars are drawn with conditional formatting, and it looked fine on the day it was built. Then the design review slipped by three days, and someone had to move eleven dates by hand. Two of them were missed. The launch date on the sheet is now wrong, and nobody noticed until the client asked.
That is the moment people search for a gantt chart with dependencies in Excel. The goal is simple: when one task moves, everything that waits on it should move too. Excel can do this. It does not do it on its own, and the way it is set up decides whether the sheet stays trustworthy or quietly drifts. This article covers the formulas that work, the layout that keeps them from breaking, and the signs that the plan has outgrown the file.
What a dependency actually means in a spreadsheet
In dedicated scheduling software, a dependency is an object. You draw a link from task A to task B, and the software stores that link and recalculates dates whenever something changes. Excel has no such object. A cell holds a value or a formula, and that is all.
So in Excel, a dependency is just a formula in the start date cell of the later task that reads the end date of the earlier one. If task B starts the day after task A ends, B's start cell contains something like =D10+1, where D10 is A's end date. When A's end date changes, B's start date follows. If B's end date is calculated from its start date plus a duration, B's end moves too, and anything that depends on B moves after it.
This is worth stating plainly because it explains every problem that comes later. The dependency is only as reliable as the cell reference. If someone types a date over the formula, the link is gone, and nothing on the sheet shows that it has gone. If rows are sorted, references can point at the wrong task. If a task is deleted, the formula that referenced it returns an error, or worse, points at whatever row slid into its place.
There are four classic dependency types in project scheduling:
| Type | Meaning | Excel formula shape |
|---|---|---|
| Finish to Start (FS) | B starts after A finishes | B start = A end + 1 |
| Start to Start (SS) | B starts when A starts | B start = A start |
| Finish to Finish (FF) | B finishes when A finishes | B end = A end |
| Start to Finish (SF) | B finishes when A starts | B end = A start |
Most small team projects only ever need Finish to Start, with the occasional Start to Start for work that runs in parallel. If the plan leans heavily on the other two, that is already a hint that a spreadsheet will be hard to maintain.
The formulas that hold up
The simplest version is =end_date+1. It works, but it counts weekends as working days, so a task that ends on Friday produces a successor that starts on Saturday. For almost every team this is wrong.
The fix is WORKDAY. The formula =WORKDAY(D10,1) returns the next working day after the date in D10, skipping Saturday and Sunday. It also accepts a third argument, a range of holiday dates, so =WORKDAY(D10,1,Holidays) skips public holidays listed in a named range. If the team does not work a standard Monday to Friday week, WORKDAY.INTL lets you define which days count as the weekend.
The end date should be calculated too, not typed. If durations are in working days, the end date is =WORKDAY(C10,E10-1,Holidays), where C10 is the start and E10 is the duration. The minus one matters: a one day task starts and ends on the same date.
Referencing a specific cell like D10 is where most sheets start to break, because the reference is tied to a position. A sturdier approach is to give every task a stable ID in its own column, add a Predecessor column where people type that ID, and look up the end date with a lookup formula. In current versions of Excel that looks like this:
=WORKDAY(XLOOKUP(G10,$A$5:$A$200,$D$5:$D$200),1,Holidays)
Here G10 holds the predecessor's ID, column A holds all task IDs, and column D holds all end dates. Now the dependency is expressed as data a person can read ("this task waits on task 7") rather than a hidden cell reference, and it survives sorting and inserted rows. For older versions of Excel, INDEX and MATCH do the same job. The Vertex42 free Gantt chart page describes this same INDEX and MATCH pattern for predecessors listed in a separate column.
For a task with more than one predecessor, wrap the lookups in MAX: the task starts after the latest of its predecessors finishes. Each additional predecessor needs its own column, which is manageable for two or three and awkward beyond that.
A lag is a fixed wait between tasks, such as three working days for a client to review a draft. Add a Lag column and change the formula to =WORKDAY(lookup_result, 1+H10, Holidays). A negative value gives a lead, where the next task starts before the previous one ends.
A layout that stops the sheet from breaking
Formulas are the easy part. Most dependency sheets fail because of how people edit them, not because the formulas were wrong. A few layout rules prevent most of the damage.
Separate input columns from calculated columns. Task name, duration, predecessor ID, and lag are inputs. Start date and end date are outputs. Shade the calculated columns a different color and protect them, so that the natural instinct to "just fix the date" does not overwrite a formula. Excel's sheet protection can lock specific cells while leaving the rest editable.
Allow a manual start date only where it is meaningful. The first task in a chain, or a task tied to a fixed external date, needs a typed start. A common pattern is a Fixed Start column: if it has a value, use it, otherwise calculate from the predecessor. =IF(F10<>"",F10,WORKDAY(...)) makes the rule visible instead of relying on someone remembering which cells are safe to type in.
Never rely on row position. IDs should not be row numbers. If a task is inserted between rows 12 and 13, row-based IDs shift, and every lookup that pointed at them is now wrong without any error. Assign an ID once and never reuse it.
Keep the chart area driven by the data. The bars should come from conditional formatting that compares each column's date to the task's start and end. Then the chart redraws itself whenever dates change, and nobody has to recolor cells.
Make broken links loud. Wrap lookups in IFERROR that returns a visible marker such as the text "CHECK" rather than a blank. A blank start date looks like an unscheduled task. A loud marker looks like a problem, which is what it is.
The sheet cannot draw arrows between bars in any practical way. Connector lines can be placed by hand, but they do not move with the bars and turn into noise after the first change. Listing the predecessor ID next to each task is the realistic substitute: the relationship is readable, even though it is not drawn.
Where Excel dependencies start to crack
A formula-based schedule works well within a certain size and a certain team shape. Past that, the effort to keep it correct grows faster than the plan does.
The first crack is concurrent editing. If the file sits on OneDrive or SharePoint, co-authoring lets several people edit at once, but each person is still editing cells, not tasks. Two people can change the same chain from different ends and both believe they are right. If the file is emailed around instead, there are soon several versions with different launch dates.
The second crack is visibility of what changed. When the design review slips three days, the dates downstream all move, which is the point. But the sheet does not record that they moved or why. A week later, someone looks at the launch date and does not know whether it has always been the 24th. Version history exists, but reading it to reconstruct a decision is slow.
The third crack is the gap between the plan and the work. The Gantt sheet says what should happen. The actual status of each task lives in chat, email, a kanban board, or someone's memory. Keeping the percent complete column current becomes a weekly chore that is skipped when things get busy, which is exactly when it matters.
The fourth crack is scale. Twenty tasks with dependencies are easy to reason about. Around a hundred, the chart no longer fits on a screen, the predecessor columns multiply, and a single wrong ID can shift a whole branch without anyone spotting it. Critical path analysis, which identifies the chain of tasks that directly determines the end date, is possible in Excel but requires a fully linked network and noticeably more formula work.
None of this means the spreadsheet was a mistake. It means the tool was chosen for a smaller version of the problem.
Spreadsheet, template, or a project tool
There are three realistic options for a team in this position, and each one suits a different situation.
| Option | Dependencies | Who updates status | Good fit |
|---|---|---|---|
| A self-built Excel sheet | Formulas you write and maintain | Whoever owns the file | One planner, under about 50 tasks, stable scope |
| A paid Excel template | Predecessor columns built in | Whoever owns the file | Same as above, less formula work |
| A project management tool with a Gantt view | Depends on the product | Each person on their own tasks | Several people changing work every day |
A ready-made template removes the formula work. Vertex42's Gantt Chart Template Pro, for example, lists up to three predecessors per task and an optional lead and lag column, and is sold as a one-time purchase rather than a subscription. The file still belongs to one owner, though, and the collaboration issues above do not change.
A project management tool changes who does the updating. Instead of one person maintaining dates in a grid, each person moves their own task, and the schedule view reflects it. Whether a given tool draws dependency links, recalculates dates automatically, or only shows bars varies a lot between products and between pricing plans, so it is worth checking the specific feature and the specific plan rather than assuming. The comparison pages for Trello and Asana show which schedule views sit on which plans.
The honest question is not which option is best in general. It is who changes the schedule and how often. If one person replans once a week, a well-built sheet is fine. If five people's work shifts daily, a sheet that only one person can safely edit becomes the bottleneck.
Getting the most out of the sheet you already have
If the decision is to stay in Excel for now, a short list of changes makes the existing file far more reliable.
Add an ID column and convert any cell references between tasks into ID lookups. This is the single change that prevents the most silent errors. It takes an afternoon for a sheet of a few dozen tasks.
Replace every +1 with WORKDAY, and create one named range for holidays that all formulas share. When a holiday is added, the whole plan adjusts in one place.
Add a Baseline Start and Baseline End column, and copy the calculated dates into them as values when the plan is agreed. From then on, a simple difference column shows how many working days each task has slipped. NETWORKDAYS counts working days between two dates, so the slip figure matches how the team actually counts time. Slippage becomes visible instead of being absorbed quietly.
Add a Notes column for why a date moved. One line is enough: "Client review took 4 extra days." It is the context version history does not give you.
Protect the calculated columns. Tell the team which columns are theirs to edit. Most broken dependency sheets were broken by someone trying to help.
Finally, decide on one owner. A formula-driven schedule is a piece of logic, and logic edited by several people without coordination drifts. If that single owner is becoming a bottleneck, that is the clearest sign the plan wants a tool built for shared editing. The features page lists what a board-based setup covers when it gets to that point.
What to change first
Add task IDs and switch every predecessor reference to an ID lookup with WORKDAY this week, because that removes the silent errors. Then add baseline columns so slips show up as numbers. If after a month several people are still waiting on one person to update dates, try the schedule in a shared board tool such as Pinateca alongside the sheet before deciding whether to move.
Q1. Can Excel draw arrows between Gantt bars to show dependencies?
Not in any way that updates automatically. Shapes and connector lines can be placed by hand, but they do not move when the bars move, so they become wrong after the first date change. Listing the predecessor task ID in a column next to each task is the practical way to show the relationship.
Q2. How do I make a task start the next working day after another task ends?
Use =WORKDAY(end_date,1,Holidays), where end_date is the predecessor's end date and Holidays is a named range of non-working dates. If the team's weekend is not Saturday and Sunday, use WORKDAY.INTL, which lets you specify which days are weekend days.
Q3. How do I handle a task that depends on two other tasks?
Look up the end date of each predecessor and take the later one with MAX, then add one working day with WORKDAY. Each predecessor needs its own column, so this is comfortable for two or three predecessors and becomes hard to maintain beyond that.
Q4. Why do my dates stop updating after someone edits the sheet?
Usually a person typed a date directly over a formula, which removes the dependency without any warning. Shade and protect the calculated start and end columns, and keep a separate column for fixed start dates so there is a safe place to enter a manual date.
Q5. Will the dependencies still work in Google Sheets?
WORKDAY, WORKDAY.INTL, NETWORKDAYS, INDEX, and MATCH all exist in Google Sheets, so the approach carries over. XLOOKUP is also available in Google Sheets today, but if the file will be opened in older versions of Excel, INDEX and MATCH are the safer choice.
