Every tutorial on building a Gantt chart in Excel stops in the same place. You end up with a task list, a Start column, a Days column, and a conditional-formatting rule that paints a bar across a grid of dates. It looks like a schedule. It is a picture of dates you typed in yourself, and it will keep looking correct long after it has stopped being true.
I spent a weekend building the version I actually wanted — one where Start is calculated, weekends are skipped, and a row underneath tells me how many people the plan is quietly assuming I have. Here is what went into it and where the spreadsheet honestly runs out of road.
What a spreadsheet Gantt chart usually is
Strip the formatting off almost any free Gantt chart Excel template and you find three things: a date you entered, a number of days you entered, and a rule that says if this column’s date falls between Start and Start plus Days, fill the cell. That is the whole machine. The chart is a rendering layer over manual input.
Which is fine right up to the first change. Excavation slips four days. Now every downstream date is wrong, and nothing in the sheet knows it, because nothing in the sheet knows that framing waits for the slab. You go and retype eleven dates, and you do it again next week. The template has moved the work rather than removing it.
The fix is not a nicer bar. It is making Start a formula.
Making Start calculated instead of typed
The rule is simple to state: a task starts the working day after the last of its predecessors finishes, or on the project start date if it has none. Getting a spreadsheet to say that takes three pieces.
First, working days. Excel’s WORKDAY function takes a date and a number of days and returns the date that many working days later, skipping Saturdays and Sundays. Finish becomes WORKDAY(Start, Days - 1) — the minus one because a one-day task finishes the day it starts, which is the off-by-one everybody writes at least once.
Second, resolving predecessors. Each task lists the IDs it waits on, and Start has to find those rows and take the latest finish among them. I did this with scalar INDEX/MATCH through a few hidden helper columns rather than one clever array formula. Array formulas in a template are a liability: they behave differently depending on the Excel version, and they are the first thing to break when somebody opens the file somewhere else.
Third — and this is the constraint nobody warns you about — a task has to be listed below its predecessors. A spreadsheet resolves top to bottom. If row 8 waits on row 20, row 8 has nothing to read when it calculates. My template has a Check column that says predecessor is below this row rather than silently returning a date from 1900, which is what an unguarded lookup will hand you.
Predecessors are more than “task 3 comes first”
Most Excel Gantt templates that do handle dependencies handle exactly one kind: finish-to-start, no lag. Task B starts when task A ends. Real schedules need more than that, and the notation for it is already standard in Primavera and MS Project, so there is no reason to invent one:
3— start when task 3 finishes (finish-to-start, the default)3FS+2— start two working days after task 3 finishes; concrete curing, paint drying, a permit window5SS— start when task 5 starts, not when it ends; two crews working the same stretch7FF-1— finish one day before task 7 finishes
The lag one earns its keep faster than you expect. Every schedule I have ever seen has at least one line that is really “wait for the concrete”, and without lag people fake it by inventing a task called wait for the concrete, which then shows up in the resource count as if somebody were standing there watching it.
The column that turns a picture into a plan
Here is the part I care about, and the reason I stopped using the templates I downloaded. Add two columns — Resource and Crew per day — and a panel underneath that adds up, for each day in the grid, how many people every in-progress task needs. Then colour the cell red when that total goes above what you actually have.
The first time I did this to a plan I thought was fine, four days in late October lit up red. Nothing was over-allocated on paper. No task asked for more than three people. It was just that five tasks overlapped in one week and quietly assumed five bodies, on a job with three. The dates were achievable one at a time and impossible together, and the Gantt chart had been perfectly happy about it all along.
That red row is the entire argument for putting crew in a spreadsheet schedule. It costs two columns and one conditional format, and it is the difference between a chart that records your intentions and one that checks them.
Where Excel genuinely stops
The sheet can tell you that you are short-handed. It cannot fix it. Rescheduling tasks so that nothing exceeds the crew you have — while respecting every dependency and finishing as early as possible — is a resource-constrained project scheduling problem, and it is NP-hard. There is no formula for it. There is not a clever formula for it either. Excel does not have the machinery, and neither does the leveling button in the commercial packages: they run a priority rule and hand you a schedule without telling you how far off the best one it landed. When somebody finally measured that, the answer was a median 3.6% above optimal and considerably worse when crews are tight — which is exactly when you press the button.
So the split I settled on is: the spreadsheet is where the plan lives and where you see the problem, and the solving happens somewhere that can actually solve. Upload the same file to the free project scheduler and it answers two questions with a constraint solver — work within my crew, when does it finish? Or keep my date, how many people would that take? It writes the answer back into the same workbook, so the chart you built redraws itself and the red row clears.
It is the same shape as the other tools I have put up here. My free shift scheduler does it for who works which shift; this one does it for what happens on which day.
Take the template
The workbook is free to download, no email, no signup. Room for 60 tasks across 95 days. Five tabs: a read-me, tasks, resources, parameters, and an empty solution sheet. Start and Finish are formulas, the bars draw themselves, weekends shade out, the load panel goes red, and typing a date over a Start cell pins that task in place if you need to hold something.
One honest caveat about the format. Every Gantt chart Excel template is a set of assumptions dressed up as a grid, and mine is no exception: 5-day weeks by default, whole-day durations, no partial allocation of a person across two tasks. If your work is billed in half-days or your crews split across jobs, you will want to change the parameters sheet before trusting the load panel. The file is unlocked. That is deliberate.
Things that I use, like, and am affiliated with:
Mint Mobile offers great cell phone service for $15 flat, get $15 off using the link. Get discounted phones with service activation and no contract.
I never spend money before I check Mr Rebates or Rakuten to get cashbacks, rebates, discounts, coupons or cheaper gift cards.