“Free Project Plan Template for Excel (Download + Guide)”

How deep should the WBS go?

Stop decomposing when a work package can be estimated and owned by one person. The traditional heuristic is the 8–80 rule: no work package smaller than eight hours and none larger than eighty, because anything under a day is tracking overhead and anything over two weeks cannot be measured honestly at a weekly cut-off. A nine-week replatform typically lands at three levels and 40–90 rows. Deeper trees look thorough and rot faster, because every extra level is another row someone must update.

Why merged cells ruin a project plan

Merged cells look like an outline and behave like a trap. Sorting a merged range scrambles it, autofilter hides rows unpredictably, and SUMIFS cannot read a merged parent twice. Use Center Across Selection (Format Cells, Alignment) when you want the visual effect, and keep every parent row on its own real row with its own real ID.

The formulas that make rollups automatic

Three formulas do almost all the work:

1. Child ID generation. In the first child row, =$A6&”.”&COUNTIF($A$6:$A6,$A6&”.*”)+1 produces 1.2.1, 1.2.2 and so on as you fill down.
2. Budget rollup. =SUMIFS($F$6:$F$300,$A$6:$A$300,$A6&”.*”) sums every ID that begins with the parent ID plus a dot. The wildcard is why the ID must be text, not a number.
3. Leaf-only check. =IF(COUNTIF($A$6:$A$300,$A6&”.*”)=0,”Work package”,”Summary”) tells you whether a row may carry hours. Summary rows should never accept effort, or you will double count.

A fourth formula catches the most common structural error: =IF(SUMIFS($F$6:$F$300,$A$6:$A$300,$A6&”.*”)<>$F6,”Check”,”OK”) on a parent row compares the children’s total with the parent’s own estimate. It is not a proof of scope completeness, but it flags every place where the outline and the money disagree.

Worked example: a website replatform WBS

A marketing site replatform with a $160,000 approved budget, split into three phases:

Row 2 sums 46,000 + 34,000 + 20,000 = 100,000 with one SUMIFS, and row 1 returns 30,000. The total check is =SUM(F6:F300)/2 against the approved budget, because summary and leaf rows both carry values; a cleaner version sums only rows where the leaf-only check returns “Work package”. Either way the arithmetic is visible and auditable, which is the entire point of keeping the plan in a spreadsheet.

How do you handle duration and working days correctly?

Store duration as a number of working days and let WORKDAY produce the finish date. Typing a finish date by hand guarantees that a two-week holiday or a moved start silently breaks the plan.

The off-by-one trips up almost everyone. If a task starts on Monday and takes five working days, it finishes on Friday, so the formula is =WORKDAY(D6,E6-1,Holidays) — subtract one because the start day is already day one. Writing =WORKDAY(D6,E6) pushes the finish to the following Monday and stretches every downstream date by a day.

Dependencies need the same care. A successor that starts one working day after its predecessor finishes uses =WORKDAY(Finish,1,Holidays); with a two-day lag it becomes =WORKDAY(Finish,3,Holidays), because one step moves past the finish date and two more add the lag. Fix the whole plan’s convention in a single notes row: start plus duration minus one equals finish, and no task starts on a weekend.

Worked example: a five-day task across a holiday

Task 2.1 starts Monday 2 March 2026 with a duration of 16 working days. There is one public holiday on Thursday 12 March. WORKDAY(2 Mar 2026, 15, Holiday list) returns Friday 27 March 2026, not Wednesday 25 March, because the holiday consumes one working day. NETWORKDAYS(2 Mar, 27 Mar, holidays) returns 16, confirming the duration. If the start slips to 9 March, only the start cell changes; finish, the successor start and every Gantt bar update in one keystroke. That single dependency is the strongest argument for formula-driven dates over typed ones.

How do you build a Gantt chart with conditional formatting?

A Gantt chart in Excel is a date grid plus a formatting rule. You do not draw bars, insert shapes, or use a stacked bar chart — you give each timeline cell a rule that asks whether its date falls inside the task window.

Set up the grid in four moves:

1. Header row of real dates. Type 2 Mar 2026 in G4, then =G4+7 in H4 and fill right for weekly columns; use =G4+1 for daily columns. Format as d mmm so the column reads “2 Mar”.
2. Task rows. Columns A to F mirror the schedule: ID, Task, Start, Finish, % complete, Owner.
3. One rule, applied to the whole grid. Select G5:BJ80, open Home, Conditional Formatting, New Rule, Use a formula, and enter =AND(G$4>=$D5,G$4<=$E5). The mixed references are the trick: G is relative so the rule shifts across columns, $4 locks the header row, $D5 and $E5 lock the columns but move down rows.
4. A second rule for progress. =AND(G$4>=$D5,G$4<=WORKDAY($D5,ROUND($E5*$F5,0)-1,Holidays)) paints only the completed portion in a darker fill, so a bar shows planned length and delivered length at once.

Three practical details separate a readable Gantt from a wall of colour. Set the planned-bar rule to Stop If True so later rules do not repaint it. Shade weekends with a rule on =WEEKDAY(G$4,2)>5 to make the timeline legible without a legend. And resist whole-column references such as $G:$G inside the rule; conditional formatting evaluates every cell in the applied range, and full-column rules on a 60-row grid are one of the most common reasons a plan file becomes slow.

Worked example: does the formula paint the right weeks?

Weekly headers begin Monday 2 March 2026, so W1 is 2 Mar, W2 is 9 Mar, W3 is 16 Mar, W4 is 23 Mar. Task 2.1 runs from 9 March to 27 March.

Three weekly cells fill for a task that spans three calendar weeks. If a bar looks one week too long, the cause is almost always a header cell that contains text such as “W2” instead of a date — the comparison then evaluates against zero and everything fills or nothing does.

How do you track progress, cost and slippage with formulas?

Compare delivered percent against time elapsed, and earned value against actual cost. Two ratios expose almost every problem a plan can have, and both are one formula away.

• Planned percent at a cut-off date: =IF(Cutoff=Finish,1,NETWORKDAYS(Start,Cutoff,Holidays)/NETWORKDAYS(Start,Finish,Holidays))).
• Actual percent: entered by the task owner, or rolled up from subtask weights.
• Schedule variance in days: =NETWORKDAYS(Finish,BaselineFinish,Holidays)-1. Positive means late, negative means early.
• Earned value: =SUMPRODUCT(Budget range, Actual % range).
• Cost performance index: =EV/AC, with estimate at completion =Budget/CPI.

Worked example: phase 2 of the replatform at cut-off

At the end of week 5, phase 2 has a $100,000 budget and should be 60% complete. The team reports 52%, and $58,000 has been invoiced.

Two numbers, one conclusion: the phase is roughly 13% behind plan and heading for an 11% overrun if nothing changes. A dashboard row with conditional formatting on CPI below 0.95 turns that into a colour the sponsor notices before you speak — which is exactly what a plan template is for.

How do you plan capacity by owner inside the same workbook?

Effort per task is not the same as load per person. A plan that ignores this produces the familiar illusion of an on-time schedule staffed by one person doing three tasks at once. Add a small capacity block: one row per owner, one column per week, and a SUMIFS for assigned effort.

The formula is =SUMPRODUCT(($C$6:$C$300=$A20)*($D$6:$D$300<=H$4)*($E$6:$E$300>H$4)*$G$6:$G$300), where column C holds the owner, D the start, E the finish and G the effort spread evenly. It answers a simple question per cell: how many effort days does this person owe in this week? Compare the result with their available days, typically four and a half after meetings and support. A conditional format on values above availability turns the schedule from a wish into a staffing decision.

How do you baseline the plan and control changes?

A baseline is the version you promised: dates, effort and cost frozen at approval. Without it, every conversation becomes an argument about what “late” even means, because the target quietly moves with the plan.

Keep two columns per measure — Finish and Baseline finish, Budget and Baseline budget — and never type over the baseline. When a change is approved, copy the new values into a fresh baseline version and keep the previous one on a Version sheet with a date and a one-line reason. Two columns of history are enough for most projects; a version log that says “v3, approved 14 April, client added Arabic locale” is enough for an auditor.

The change rule itself is worth writing down in the workbook: any change that moves the finish date by more than three working days, or the budget by more than five percent, needs a named approver. Below those thresholds the planner updates the row and mentions it in the weekly note. Thresholds stop small edits from becoming silent scope creep without turning every two-hour task into a change request.

What does a 15-minute weekly update look like?

A plan only stays true if updating it is cheap. Reserve the same fifteen minutes every week, before the status meeting rather than during it, and follow the same five steps in order.

1. Set the cut-off date cell and refresh the dashboard; every planned percentage recalculates from that one cell.
2. Ask each owner for percent complete on their work packages only, not on summary rows.
3. Enter actual cost from the finance export, matched to the same period as the work.
4. Read the exception list — CPI under 0.95, schedule variance over three days, capacity over 100% — and write one sentence of cause for each.
5. Decide one action per exception and put it on the task list with an owner and a date.

The discipline is what makes the template valuable, not the formatting. A workbook refreshed weekly with honest percentages will outperform a beautiful plan updated monthly, because the forecast is only as good as the last measurement.

What are the most common mistakes in Excel project plans?

Most broken plans fail for structural reasons rather than arithmetic ones. The symptoms are recognizable, and each has a cheap fix.

The deeper mistake is treating the workbook as the plan. The workbook is a model of the plan; if the team never updates percent complete, the Gantt becomes decoration within two weeks. Assign one owner for the schedule, set a weekly cut-off, and record a one-line reason whenever a baseline moves.

When is Excel the right tool, and when is it not?

Excel wins on transparency, offline access, cost, and flexibility. It loses on concurrency, audit trails, and mobile updates — the three things that matter most once a project has more than a handful of contributors.

If you need the structure without the version conflicts, Doitify keeps the same WBS logic — tasks, owners, dates, dependencies, percent complete — in a shared workspace with per-person views and a status feed, so the schedule stops being a file that only one person can safely open. Disclosure: this article is published by Doitify, and the template guidance above is tool-agnostic on purpose.

Conclusion

A project plan template for Excel earns its place by making three things automatic: rollups from a hierarchical ID, finish dates from working-day math, and a Gantt that is a formatting rule rather than a drawing. Build the four sheets, keep dates in one place, add a baseline column you never overwrite, and the workbook becomes a decision tool instead of a status ornament. When the team outgrows single-file editing, move the same structure into a shared workspace rather than adding more tabs to the spreadsheet.

If this post on project plan template for Excel was helpful, you might also enjoy Critical Path vs Critical Chain: What’s the Difference? and Best Project Management Software for Small Business in 2026.