Project Management Spreadsheet Template for Excel and Google Sheets
Someone asks how the project is going. You say “good — we’re about halfway.” You are not lying, exactly. You looked at the calendar, saw you were roughly midway between kickoff and launch, and reported the calendar back.
The calendar is not the project. In the worked example below, the calendar is 57% used and the work is 33% done, and the difference between those two numbers was visible in a spreadsheet three weeks before anyone said the word “delay” out loud.
Here is what a project spreadsheet actually needs to hold, the four views it produces for free, and a complete worked project you can compare your own against.
Seven Columns, Then Everything Else Is a View
The single most common mistake in a project spreadsheet is building a timeline tab, a status tab and a report tab, then maintaining all three by hand. Within two weeks they disagree, and the moment they disagree nobody trusts any of them.
There is only one list. It has seven columns that you type into:
| Column | Why it earns its place |
|---|---|
| Project | Lets one file hold every project instead of one file per project |
| Task name | The unit of work — small enough to finish, big enough to name |
| Owner | One name. Not a team, not two people |
| Status | To Do / In Progress / Done / Blocked / On Hold |
| Start date | What makes a Gantt possible at all |
| Due date | What makes “overdue” a fact rather than a feeling |
| % complete | The only column that tells you about partial work |
Two more columns are optional but change what the sheet can answer: Budget and Actual. Without them you can report schedule. With them you can report whether the schedule is being bought with money.
Everything after this point is a formula reading those columns:
- Duration —
=IF(OR(G4="",H4=""),"",H4-G4+1)for calendar days, or=NETWORKDAYS(G4,H4)for working days. The+1matters: a task starting and ending on the same day is one day of work, not zero. - Variance —
=IF(C4="","",L4-M4), budget minus actual. Positive is underspent. - Overdue flag —
=AND($C4<>"",$H4<TODAY(),$F4<>"Done"). Put that in a conditional formatting rule across the row and late work colours itself in. The$C4<>""guard is not decoration: on an empty row the due-date cell reads as zero, zero is less than today, and the status is not “Done” — so without it every unfilled row in the range turns red.
An owner column with one name in it is worth arguing about. Two names in an owner cell is how a task becomes nobody’s. If two people genuinely both have work, that is two tasks.
The Four Views You Get for Free
Once the list exists, four different questions get four different answers off the same rows — with no re-typing.
The Gantt chart answers when. It is the start and due dates drawn horizontally across a row of day columns, with conditional formatting doing the drawing. The full build, with the exact conditional formatting formulas, is here.
The Kanban board answers what is moving right now. It is the status column, sorted. It carries no dates at all, which is precisely why it is calming to look at on a Tuesday morning and useless for planning a launch. When to use which, and why you can run both off one list.
The milestone list answers what does the outside world see. Not every task is a milestone. A milestone is a date somebody who is not on the team has an opinion about — a client sign-off, a go-live, a launch. Five or six per project, each with a days-left countdown — on a Milestones tab with the target date in column C, that is =IF(C4="","",C4-TODAY()).
The dashboard answers are we OK. Counts and averages, nothing clever:
- Total tasks —
=COUNTA(Tasks!$C$4:$C$200) - Completed —
=COUNTIF(Tasks!$F$4:$F$200,"Done") - Overdue —
=SUMPRODUCT((Tasks!$H$4:$H$200<TODAY())*(Tasks!$H$4:$H$200<>"")*(Tasks!$F$4:$F$200<>"Done")) - Workload by owner —
=COUNTIFS(Tasks!$D$4:$D$200,A5,Tasks!$F$4:$F$200,"<>Done")
The <>"" term in the overdue formula is doing the same job as the guard above. An empty due-date cell reads as zero, zero is less than today, and a blank status is not “Done” — so without that term every unfilled row in the range reports itself as overdue. Whether you write it with SUMPRODUCT or as =COUNTIFS(Tasks!$H$4:$H$200,"<"&TODAY(),Tasks!$H$4:$H$200,"<>",Tasks!$F$4:$F$200,"<>Done") matters less than remembering the guard at all.
A Worked Project
A twelve-task website relaunch. Five people, an $11,000 budget and a fifty-four day calendar. We are looking at it on day 31 of 54.
| Task | Owner | Priority | Status | Start | Due | Days | % | Budget | Actual |
|---|---|---|---|---|---|---|---|---|---|
| Discovery & requirements | Jordan | High | Done | Mar 2 | Mar 6 | 5 | 100% | $1,200 | $1,150 |
| Content audit | Taylor | Medium | Done | Mar 2 | Mar 11 | 10 | 100% | $600 | $640 |
| Sitemap & wireframes | Sam | High | Done | Mar 7 | Mar 13 | 7 | 100% | $800 | $920 |
| Visual design | Sam | High | In Progress | Mar 14 | Mar 25 | 12 | 60% | $1,500 | $700 |
| Copywriting | Taylor | Medium | In Progress | Mar 16 | Mar 29 | 14 | 40% | $900 | $380 |
| Front-end build | Alex | Critical | To Do | Mar 26 | Apr 11 | 17 | 0% | $3,000 | $0 |
| CMS setup | Alex | High | To Do | Mar 26 | Apr 4 | 10 | 0% | $1,000 | $0 |
| Content migration | Taylor | Medium | To Do | Apr 1 | Apr 15 | 15 | 0% | $600 | $0 |
| SEO redirect map | Jordan | High | Blocked | Mar 30 | Apr 6 | 8 | 0% | $400 | $0 |
| QA & browser testing | Morgan | High | To Do | Apr 12 | Apr 19 | 8 | 0% | $700 | $0 |
| Client review round | Jordan | Critical | To Do | Apr 16 | Apr 22 | 7 | 0% | $0 | $0 |
| Launch & DNS cutover | Alex | Critical | To Do | Apr 23 | Apr 24 | 2 | 0% | $300 | $0 |
That is the whole input. Here is what the dashboard returns from it.
The dashboard
| Metric | Value |
|---|---|
| Total tasks | 12 |
| Completed | 3 |
| In progress | 2 |
| Blocked | 1 |
| To do | 6 |
| Overdue | 2 |
| Overall % complete (simple average) | 33.3% |
| Budget | $11,000 |
| Actual to date | $3,790 |
| Variance | $7,210 underspent |
Two tasks are overdue — Visual design, due Mar 25 and sitting at 60%, and Copywriting, due Mar 29 at 40%. Neither has moved to Done and both due dates are behind us.
The number three ways
“Overall % complete” is where project reporting quietly goes wrong, because the same twelve rows produce three defensible answers:
| Method | Result | What it ignores |
|---|---|---|
| Simple average of % complete | 33.3% | Size — a 2-day task counts as much as a 17-day one |
| Tasks done ÷ total tasks | 25.0% | Partial progress — the two in-flight tasks count as zero |
| Weighted by budget | 35.1% | Nothing much; this is the honest one |
Weighting is one formula: =SUMPRODUCT(J4:J15,L4:L15)/SUM(L4:L15) — each task’s % complete multiplied by its budget, divided by total budget. If your tasks vary wildly in size, use it and say so. Quoting the simple average of a list where one task is 17 days and another is 2 is how a project reports 33% and delivers 20%.
The reading that actually matters
None of the three numbers above is the finding. This is:
| Measure | Day 31 of 54 |
|---|---|
| Calendar elapsed | 57.4% |
| Work complete (simple average) | 33.3% |
| Work that should be complete | 54.6% |
The third row is the one nobody calculates. Take each task, ask what fraction of its own window has passed by today, and average those the same way you averaged actual progress. A task whose window closed is 100% planned regardless of what happened. A task not yet started is 0%.
That gives 54.6% planned against 33.3% actual — a 21.3 point gap, on day 31, with the largest and most expensive task in the project not yet begun.
Task by task, the gap has a shape:
| Task | Planned by day 31 | Actual | Gap |
|---|---|---|---|
| Visual design | 100% | 60% | −40 |
| Copywriting | 100% | 40% | −60 |
| CMS setup | 70% | 0% | −70 |
| Front-end build | 41% | 0% | −41 |
| SEO redirect map | 38% | 0% | −38 |
| Content migration | 7% | 0% | −7 |
Front-end build is the $3,000 task. Its window opened on Mar 26. It is now Apr 1. It is seven days into a seventeen-day window at zero percent, and it is not flagged overdue by anything, because its due date is still ten days away. That is the single most useful thing on the sheet and no standard dashboard tile shows it. The seven warning signs that are visible before a deadline is missed.
The budget reading
$3,790 spent of $11,000 is 34.5% of budget. Compare that against the budget-weighted 35.1%, not the simple average — a dollar figure belongs beside a dollar-weighted one. Within a point of each other is the healthy pattern, and it is the check worth doing monthly. Spend running ahead of progress means the work is costing more than planned. Spend running behind progress usually means work has not started, which is this project’s actual problem wearing a reassuring disguise.
Note also that all three finished tasks came in close to estimate — $2,710 actual against $2,600 budget, 4% over. The estimating is fine. The sequencing is not.
Multi-Project, Which Is the Whole Point
A Project column turns a task list into a portfolio. The roll-up needs five formulas per row, all reading the same task list:
| Formula | Returns |
|---|---|
=COUNTIF(Tasks!$B:$B,A5) |
Tasks in this project |
=COUNTIFS(Tasks!$B:$B,A5,Tasks!$F:$F,"Done") |
Tasks done |
=IFERROR(C5/B5,0) |
% of tasks done |
=B5-C5 |
Open items |
=SUMIF(Tasks!$B:$B,A5,Tasks!$L:$L) |
Budget |
Four projects, four rows of formulas, one screen that answers where everything stands. The alternative — a file per project — makes the cross-project question unanswerable without opening four files and doing arithmetic in your head, which is a task nobody ever does on a Friday afternoon.
What a Spreadsheet Will Not Do
Being clear about this is what stops a spreadsheet becoming a bad version of software:
- It will not reschedule downstream tasks. A Depends On column documents that Front-end build waits on Visual design. It does not move Front-end build’s dates when Visual design slips. You move them, and the Gantt redraws.
- It will not notify anyone. Overdue turns red. Red does not email.
- It will not hold files or conversations per task. A Notes column is not a comment thread.
- It will not tell you who changed what — at least not readily. Version history in Google Sheets or OneDrive is file-level. Google Sheets does add a per-cell Show edit history on right-click, which gets closer, but neither is an audit log you can report from.
If your project fails because of any of those four, buy software. Most projects that fail do not — they fail because nobody could see, on day 31, that 57% of the time had produced 33% of the work.
Excel or Google Sheets?
The file is the same either way, which is the point. Excel is better for large grids, offline work and printing. Google Sheets is better the moment more than one person needs to update a status without asking whose turn it is to have the file open. What works, what changes and what to check after importing.
Start With One Project and Thirty Minutes
Do not build the Gantt first. Build the task list — project, task, owner, status, start, due, % — for one project you are already running. Twelve to twenty rows. Then add the duration formula, the overdue rule and the two counts, and read the calendar-elapsed against work-complete gap once.
If those two numbers are within a few points of each other, you have a project that is fine and now you can prove it. If they are twenty points apart, you have just bought yourself three weeks of warning.
Featured on ReadySheetGo
Project Management & Gantt Chart Tracker — $14.99
Eight tabs, built exactly as above. A Settings tab holds editable dropdown lists for statuses, priorities, owners and project names and drives every other tab. The Task List is the single source of truth — project, owner, priority, status, start and due dates, automatic duration, % complete, a Depends On (ID) column and budget, actual and variance per task. The Gantt Chart draws its own bars from those dates across a rolling day-by-day timeline, shaded to separate completed work from remaining. The Kanban Board re-sorts itself into To Do, In Progress and Done as statuses change. Milestones carries key deadlines with a days-left countdown and at-risk highlighting. Projects rolls up task count, done, % complete, open items and budget versus actual across every project side by side. The Dashboard returns total tasks, completed, in progress, overdue, overall % complete, budget versus actual and workload by owner — with overdue tasks turning red automatically.
Sample data is pre-filled across four example projects so you can see it working — type over it with your own. Works in Excel and Google Sheets, no macros and no add-ons.
Get the Project Management & Gantt Chart Tracker →
Frequently Asked Questions
What should a project management spreadsheet include?
Seven columns on one task list — project, task name, owner, status, start date, due date and % complete — plus optional budget and actual columns. Everything else is a view built from those seven. A Gantt chart is the start and due dates drawn horizontally, a Kanban board is the status column sorted into columns, a milestone list is the due dates that matter to someone outside the team, and a dashboard is counts and averages of the same rows. If you find yourself typing the same fact into two places, one of them should be a formula.
Is a spreadsheet good enough for project management?
For a single team running a handful of projects with a known scope, yes — a spreadsheet does task tracking, timelines, budget versus actual and reporting without a per-seat fee. It stops being enough when you need automatic rescheduling of downstream tasks, notifications when a due date passes, per-task file attachments and comment threads, or an audit trail of who changed what. Those are the four things a spreadsheet genuinely cannot do, and none of them matter until a project is big enough to hurt.
How do you calculate overall project percent complete?
Three ways, and they disagree. The simple average of every task's % complete treats a two-day task the same as a three-week one. Tasks-done divided by total tasks ignores partial progress entirely. Weighting each task's % complete by its budget or its duration is the most honest, because it credits progress in proportion to the size of the work. In the worked project in this guide the same twelve tasks read 33.3% by simple average, 25.0% by tasks done and 35.1% weighted by budget — so state which one you are quoting.
Can the same spreadsheet run more than one project at once?
Yes, and it is the main reason to use a spreadsheet rather than a per-project document. Add a Project column to the task list and every view filters by it — a roll-up tab can then return task count, tasks done, % complete, open items and budget versus actual for each project side by side with COUNTIF and SUMIF. The failure mode to avoid is one file per project, which makes the question you most often need to answer — where is everything, across everything — the one question nothing can answer.