Gantt chart dependencies in Excel: linking tasks without breaking the sheet

A Gantt chart dependency says one thing: this task cannot start (or finish) until that task has started (or finished). Dedicated scheduling software stores that rule as a link and redraws the plan when anything moves. Excel has no such link. In a spreadsheet, a dependency is a formula in one task's date cell that reads another task's date. Build it with a stable task ID, a predecessor column, a lookup, and WORKDAY.INTL, and the sheet will reschedule itself when a date slips. What Excel will not do is draw arrows that follow the bars, warn anyone when a formula is typed over, or tell you which chain decides the end date. The rest of this article shows the formulas for each dependency type, a column layout that survives editing, the checks that catch silent breakage, and the point where the file stops being the right home for the plan.

What a dependency is, and what Excel does with it

Project scheduling uses four dependency types. Each one ties an edge of one task to an edge of another.

Type	Rule	What Excel needs
Finish to Start (FS)	B starts after A finishes	B's start reads A's end
Start to Start (SS)	B starts when A starts	B's start reads A's start
Finish to Finish (FF)	B finishes when A finishes	B's end reads A's end
Start to Finish (SF)	B finishes when A starts	B's end reads A's start

In a scheduling tool, choosing one of these creates a stored relationship. Move task A and the tool shifts B, then everything after B. It also draws a connector between the bars so the reader can see why B sits where it does.

Excel sees none of this. A cell contains either a value or a formula. When task B's start cell holds a formula that reads task A's end cell, the result behaves like a Finish to Start dependency. When someone types a date into B's start cell, the formula is replaced and the dependency is gone. Nothing turns red. The bar still draws. The date simply stops following A.

That single fact explains most of the advice that follows. A spreadsheet dependency is only as durable as the formula holding it, so the work is less about writing the formula once and more about keeping it from being overwritten, misdirected by sorting, or orphaned by a deleted row.

Two terms come up repeatedly. A lag is a wait between linked tasks, such as two working days for a client to review a draft. A lead is the opposite: the next task begins before the previous one ends, written as a negative lag. Both are simple to add in Excel once the base formula is in place.

Finish to Start covers most small team plans. Start to Start shows up when two streams of work begin together, for example writing and design for the same page. Finish to Finish fits work that must wrap up together, such as testing and documentation. Start to Finish is rare enough that if a plan relies on it heavily, the plan probably needs a scheduling tool rather than a sheet.

The four dependency types as Excel formulas

Assume this layout, one task per row, starting at row 5:

• Column A: ID (a number assigned once and never reused)
• Column B: Task name
• Column C: Duration in working days
• Column D: Predecessor ID
• Column E: Type (FS, SS, FF, or SF)
• Column F: Lag in working days (negative for a lead)
• Column G: Start date (calculated)
• Column H: End date (calculated)

Name the ID column TaskID, the start column Starts, and the end column Ends using the Name Box, and name the holiday list Holidays. Named ranges make the formulas readable and keep them pointing at the right columns when rows are inserted.

Microsoft documents the syntax as WORKDAY.INTL(start_date, days, [weekend], [holidays]). The weekend argument is a number, where 1 (or omitted) means Saturday and Sunday, 7 means Friday and Saturday, and 11 means Sunday only. It also accepts a seven character string starting with Monday, where 1 is a day off, so "0000011" is a Saturday and Sunday weekend. This is how the same sheet can serve a team that works Sunday to Thursday.

The lookup is XLOOKUP(D5, TaskID, Ends), which returns the end date of whichever task has the ID typed in D5. The formulas for each type, written for row 5:

Finish to Start. Start: =WORKDAY.INTL(XLOOKUP(D5,TaskID,Ends),1+F5,1,Holidays). The next working day after the predecessor ends, plus any lag.

Start to Start. Start: =WORKDAY.INTL(XLOOKUP(D5,TaskID,Starts),F5,1,Holidays). With no lag, both tasks start together.

Finish to Finish. End: =WORKDAY.INTL(XLOOKUP(D5,TaskID,Ends),F5,1,Holidays). The start is then worked backwards: =WORKDAY.INTL(H5,-(C5-1),1,Holidays).

Start to Finish. End: =WORKDAY.INTL(XLOOKUP(D5,TaskID,Starts),F5-1,1,Holidays), with the start worked back from the end the same way.

For FS and SS rows, the end date is =WORKDAY.INTL(G5,C5-1,1,Holidays). The minus one matters. A one day task starts and ends on the same date, so adding the full duration would make every task one day too long.

Rather than keeping four formulas for four types, many sheets use a single IFS on column E that picks the right one. That keeps the column uniform, which makes it much harder for a stray edit to hide.

On versions of Excel without XLOOKUP, INDEX(Ends, MATCH(D5, TaskID, 0)) does the same lookup.

Building the sheet so it survives editing

Formulas are the easy part. Dependency sheets usually fail through ordinary editing: a teammate fixes a date by hand, someone sorts by owner, a task gets deleted. The following steps prevent most of that.

1. Give every task an ID that is not its row number. Row numbers change when rows are inserted. An ID typed once in column A does not. Lookups by ID keep working after sorting and inserting.
2. Separate inputs from outputs visibly. Duration, predecessor, type, and lag are inputs. Start and end are outputs. Fill the output columns with a light grey so people know not to type there.
3. Lock the output columns. Select the input columns, open Format Cells, clear Locked on the Protection tab, then use Review > Protect Sheet. The calculated dates can no longer be overwritten.
4. Allow a fixed date only where one exists. The first task in a chain, or a task pinned to an external date such as a trade show, needs a typed start. Add a Fixed Start column and wrap the start formula: =IF(K5<>"",K5, followed by the dependency formula ). The exception is now a visible input rather than a typed-over formula.
5. Draw the bars from the dates. Put a row of dates across the top of the chart area and use conditional formatting with a rule such as =AND(M$4>=$G5,M$4<=$H5). The bars redraw whenever any date moves.
6. Keep holidays on their own sheet. One list, named Holidays, used by every formula. Adding a public holiday then shifts the whole plan correctly.

Arrows between bars are the one thing to skip. Connector shapes can be placed by hand, but they are drawing objects, not linked to cells, and they stay put when the bars move. After the first slip they point at the wrong places. The predecessor ID in column D is the readable substitute.

Several predecessors, lags, and the end date that actually matters

Real tasks often wait on more than one thing. A launch might need both the final build and the approved copy. In Excel, the task should start after the later of its predecessors finishes.

The simplest approach is a second and third predecessor column, with the start formula wrapping the lookups in MAX:

=WORKDAY.INTL(MAX(XLOOKUP(D5,TaskID,Ends),XLOOKUP(L5,TaskID,Ends)),1+F5,1,Holidays)

If the second predecessor cell is empty, the lookup returns an error, so give XLOOKUP its if_not_found argument of 0. The MAX then ignores the missing one. This is manageable for two or three predecessors. Beyond that, the extra columns make the sheet hard to read, and that is a signal about the plan rather than about Excel.

Once tasks are linked, the next question is which chain controls the finish date. That chain is the critical path: if any task on it slips by a day, the end date slips by a day. Tasks off the critical path have float, meaning they can slip a little without moving the end.

A full critical path calculation in Excel needs a backward pass: for each task, the latest it could finish without moving the end date. That requires looking up successors rather than predecessors, which a simple predecessor column does not support well. A workable shortcut for small plans is to test it directly. Add one working day to a task's duration and watch whether the final end date moves. If it does, that task is on the critical path. Mark it with a column flag. For a plan of 20 or 30 tasks this takes a few minutes and gives the team the information that matters: which slips are dangerous and which are absorbed.

Catching broken links before they cost a date

The worst failure in a spreadsheet Gantt chart is silent. A formula gets replaced by a typed value, the date stops following its predecessor, and the chart looks exactly as before. These checks make that failure visible.

Flag cells that are no longer formulas. ISFORMULA returns TRUE when a cell contains a formula. A conditional format on the start column with the rule =AND($K5="",NOT(ISFORMULA($G5))) highlights any start date that has been typed over without a fixed start being declared.

Flag tasks that start before their predecessor finishes. For Finish to Start rows, =G5<=XLOOKUP(D5,TaskID,Ends) should never be true unless there is a lead. Highlight it in red.

Make missing predecessors loud. Use XLOOKUP's fourth argument to return text such as "MISSING" instead of the default #N/A. A deleted task then produces a word that someone will ask about.

Trace a single chain. On the Formulas tab, Trace Precedents draws arrows from the cells a formula reads. On a start date cell it shows which end date it is following. This is useful for checking one doubtful task. Remove Arrows clears the display.

See every formula at once. Show Formulas on the Formulas tab swaps every result for its formula. A column that is meant to be uniform should look uniform. Any cell showing a plain date instead stands out immediately.

Check before every status meeting. A short checklist: no highlighted cells in the start column, no MISSING values, final end date unchanged or changed for a known reason. Two minutes before the meeting is cheaper than discovering a wrong launch date in front of a client.

When the sheet stops being the right place

A formula-driven Gantt sheet is a good fit for one planner, a few dozen tasks, and dates that change weekly at most. It starts to cost more than it saves under specific conditions.

Several people need to update the plan. Shared workbooks on OneDrive or SharePoint allow co-authoring, but people are still editing cells, not tasks. Two people can adjust the same chain from different ends. Protection helps, but it also means one person ends up updating dates on behalf of everyone else.

The plan and the work have drifted apart. The Gantt sheet says what should happen. The actual status lives in chat, email, or a task board. Keeping a percent complete column current becomes a chore that is dropped during busy weeks, which is when the plan matters most.

Nobody can tell what changed. When a slip moves twenty dates downstream, the sheet records the new dates, not the reason. Version history can reconstruct it, slowly.

The chart no longer fits on a screen. Past around a hundred rows, scrolling between a task and its predecessor becomes the main activity.

At that point, the choice is between a full scheduling tool and a task management tool that includes a Gantt view. Full scheduling tools store dependency links, draw connectors, and calculate the critical path. Many project management tools take a lighter approach: the Gantt view shows each task's start and due date as a bar, dragging the bar moves the dates, and the same cards appear on a kanban board and a calendar, so the plan and the status are the same records. One such tool, free for up to five people and ten boards on its pricing page, switches its Gantt scale between days and weeks, marks today with a red line, and treats any unfinished bar to the left of that line as late. It does not draw dependency arrows, so a team that needs automatic rescheduling through linked chains should keep that logic in a dedicated scheduler or in the sheet. A team whose real problem is that the plan and the day to day work live in different places gets more from a shared board. The features page lists the board types, and the monday.com comparison covers a tool that does offer dependencies.

What to change first

Start with the IDs and the predecessor column, then lock the calculated date columns. Those two changes prevent most silent breakage. If the plan is already shared by more than a couple of people, try the same project on a board alongside the sheet for two weeks, for example in Pinateca, and keep whichever one the team actually updates.

Q1. Can Excel draw arrows between Gantt bars automatically?

No. Connector shapes can be drawn by hand, but they are not tied to cells and do not move when dates change. Listing the predecessor ID next to each task, and using Trace Precedents on the Formulas tab when checking one task, is the practical substitute.

Q2. How do I stop weekends from being counted in a dependency?

Use WORKDAY.INTL instead of adding days directly. Its weekend argument sets which days are off, for example 1 for Saturday and Sunday or 7 for Friday and Saturday, and its last argument takes a range of holiday dates to skip.

Q3. What happens to dependent tasks if I delete a task in Excel?

Any lookup that pointed at the deleted task's ID finds nothing and returns #N/A, which then flows through to every later task. Set XLOOKUP's if_not_found argument to a word such as MISSING so the gap is obvious, then re-point those tasks to a new predecessor.

Q4. Does Excel have a built-in Gantt chart with dependencies?

Excel has no Gantt chart type with dependency links. The common approaches are a stacked bar chart or conditional formatting driven by start and end dates, with dependencies expressed through formulas in the date columns as shown above.

Q5. How many tasks can a dependency sheet handle before it gets unreliable?

There is no fixed limit. Sheets with a few dozen tasks and one owner tend to stay accurate. Once several people edit dates and the plan passes around a hundred rows, broken links become hard to spot without the checks described in this article.