“Free Project Tracker Template for Google Sheets (Download + Guide)”

What tabs should a project tracker template have?

project tracker template for Google Sheets is a key topic in modern project management and teamwork. Five tabs, each with one job. Separating the time log from the task list is the decision that makes everything else work, because it means actual hours accumulate instead of being overwritten every Friday.

Two rules keep this structure honest. First, the dashboard is never edited; it is a view built from `01_Tasks` and `03_TimeLog`. Second, the time log is append-only — corrections are new rows with a note, never silent edits. The moment people can overwrite history, the log stops being evidence.

What columns does the Tasks tab need?

Twelve working columns plus two system columns. Anything beyond this should be argued for, because every extra column is a field somebody has to fill in and a reason to avoid the sheet.

The `% Complete` column is the one people argue about. Derive it from status so the sheet stays consistent, and allow a manual override only for long tasks that genuinely sit at 60% for a week:

=IFS($G2="Done",1,$G2="Blocked",0.25,$G2="In progress",0.5,$G2="Not started",0,TRUE,0)

That single line is why the dashboard works. Without it, every report needs a human to interpret “in progress,” and interpretations differ.

Which formulas make the tracker self-updating?

Six formulas cover 90% of the useful behaviour. Paste them into row 2 and fill down.

1. Traffic-light status (column N). Order matters: done first, then blocked, then date logic, then priority.

=IFS($G2="Done","Green",$G2="Blocked","Red",$F2="","",$F2<TODAY(),"Red",$F2-TODAY()<=3,"Amber",$H2="P1","Amber",TRUE,"Green")

2. Days remaining. A plain subtraction, but excluding completed rows keeps the dashboard honest.

=IF(OR($F2="",$G2="Done"),"",$F2-TODAY())

3. Forecast finish date. Converts remaining effort into working days at a stated daily capacity. Eight hours a day is the calendar assumption; adjust to six if your team loses two hours to meetings.

=IF(OR($F2="",$G2="Done"),"",WORKDAY(TODAY(),ROUNDUP(MAX(0,$I2*(1-$K2))/8,0)))

4. Remaining hours per workstream. Weighted by estimate, not by task count.

=IFERROR(SUMPRODUCT(($B$2:$B$500=$A2)*$I$2:$I$500*(1-$K$2:$K$500)),0)

5. Owner load. Hours still owed by a person, ignoring finished and unestimated rows.

=IFERROR(SUMIFS($I$2:$I$500,$D$2:$D$500,$A2,$G$2:$G$500,"<>Done")-SUMIFS($J$2:$J$500,$D$2:$D$500,$A2,$G$2:$G$500,"<>Done"),0)

6. On-time completion rate. Needs `Completed on` and `Due` to be real dates, which is why column L exists.

=IFERROR(SUMPRODUCT(($G$2:$G$500="Done")*($L$2:$L$500<=$F$2:$F$500))/COUNTIF($G$2:$G$500,"Done"),"")

Then the dashboard counts, which are the numbers your weekly review actually reads:

=COUNTIFS($F$2:$F$500,"<"&TODAY(),$G$2:$G$500,"<>Done")     // overdue tasks
=COUNTIFS($G$2:$G$500,"Blocked")                            // blocked tasks
=COUNTIFS($D$2:$D$500,"<>",$G$2:$G$500,"<>Done",$K$2:$K$500,"<1")  // open assigned tasks

One warning about ranges. Use `$2:$500` rather than whole columns (`$A:$A`) once the sheet passes a few hundred rows, because whole-column SUMIFS recalculate on every edit and are the most common cause of a tracker that feels slow.

How do you build the owner view without duplicating rows?

Use `QUERY`, which filters live data into a separate tab. Put the person’s name in `B1` of a tab called `05_MyTasks`, and the list rebuilds itself whenever the task table changes.

=QUERY(Tasks!$A$2:$O,"select A,C,E,F,G,I,N where D = '"&$B$1&"' and G <> 'Done' order by F asc",0)

Two constraints keep this reliable. The range must be a contiguous block with the header row excluded, and every date column must be a real date, not text — `QUERY` sorts text dates alphabetically, which produces a list that looks sorted and isn’t. If you need the header labels to be human-readable, add a small label row above the query output rather than changing the select clause.

For the cross-project view, avoid merging files. Instead, add a `Project` column to a single master task tab and use `QUERY` per project. Linked spreadsheets (`IMPORTRANGE`) look elegant and break silently when permissions change, which is the last thing you want in a status report.

How does data validation stop the sheet from rotting?

Dropdowns are the cheapest data quality tool in Sheets. Create them from `02_Lookups`, not from a hand-typed comma list, so a status rename propagates everywhere.

1. Select `02_Lookups!A2:A6`, choose Data → Named ranges, name it `StatusList`.
2. On `01_Tasks!G2:G500`, choose Data → Data validation → Dropdown (from a range) and enter `=StatusList`.
3. Repeat for `OwnerList`, `WorkstreamList`, and `PriorityList`.
4. Turn on Reject the input rather than showing a warning. Warnings get clicked through; rejections get fixed.

Then add conditional formatting on `A2:O500` with custom formulas, so the board answers questions before anyone reads a cell:

• `=$N2=”Red”` → light red fill across the row
• `=$G2=”Blocked”` → bold red text on the task name
• `=AND($G2<>“Done”,$F2-TODAY()<=3,$F2>=TODAY())` → amber fill on the due date
• `=$K2=1` → strikethrough and grey text

Conditional formatting is a formula, not a colour choice: the fill is generated from the same logic as the RAG column, so a row cannot contradict itself.

Worked example 1: what is the real completion percentage?

Three workstreams, six hundred estimated hours.

Weighted progress is 300 ÷ 600 = 50%. Now count tasks instead: 9 of 14 tasks are marked Done, which reads as 64%. The task-count average is 14 points optimistic because the finished tasks are the small ones and the 400-hour build is half done.

This is not a rounding difference. A steering group told “64% complete” in week six of twelve will not authorise help, and the delivery lead will spend the next month explaining why the last 36% takes longer than the first 64%. Weight by estimate, always.

Worked example 2: is the team actually able to finish on time?

Five people, 40 contracted hours a week, minus 8 hours of meetings and support: 32 productive hours each, or 160 per week.

Remaining effort across open tasks: 620 hours. The committed window is three weeks, so capacity is 160 × 3 = 480 hours. Shortfall: 140 hours, which is 4.4 person-days per person across the team, or roughly one extra full-time person for 4.4 weeks.

The dashboard formula for this is a subtraction of two `SUMIFS` results, and the decision it triggers is a scope conversation, not a heroic one. Either cut 140 hours of P3 work, extend by one week, or add capacity. The sheet’s job is to surface the number early enough that all three options still exist.

Worked example 3: when will this task really finish?

A task has 26 hours remaining and is assigned to someone with 6 usable hours a day (four hours of meetings, one of support).

`ROUNDUP(26/6,0)` = 5 working days. If today is Monday 9 November 2026, `WORKDAY(TODAY(),5)` returns Monday 16 November, because `WORKDAY` skips the weekend. Add a two-day review buffer and the committed date is Wednesday 18 November.

The lesson is the divisor, not the function. Using 8 instead of 6 produces a date three days early, and a tracker that is consistently three days optimistic is worse than no tracker, because people learn to add their own padding and the forecast loses all authority.

Worked example 4: what does the “free” spreadsheet cost?

Four inputs: 40 minutes a week updating statuses and chasing owners, 25 minutes a month rebuilding the summary for a stakeholder, and a loaded rate of $45 an hour.

• Weekly: 0.67 h × $45 = $30.15
• Monthly update time: 2.9 h × $45 = $130.50
• Report rebuild: 0.42 h × $45 = $18.90
• Monthly total: $149.40, or $1,792.80 a year

A paid project tool at $10 per seat for eight people is $960 a year. The spreadsheet is not free; it is unpaid work. That does not automatically make the tool the better buy — the sheet has no onboarding cost and no adoption risk — but it does mean “we’ll just use Sheets” is a budget decision, and should be written down as one.

What are the limits of a Google Sheets tracker?

Google Sheets allows up to 10 million cells per spreadsheet and 18,278 columns, so raw capacity is not the constraint. The real limits arrive earlier:

No dependency engine. Column M records that a task depends on another; nothing enforces the order or recalculates dates when the predecessor slips. You are the engine.

No notifications. Status changes do not ping anyone. You can add Apps Script triggers, and then you own a small application nobody else can maintain.

File-level permissions. You can protect ranges, but you cannot give a client read access to one workstream without giving them the file. That is usually the first thing that breaks in agency work.

Recalculation lag. Past roughly 5,000 task rows with heavy `SUMIFS` and `QUERY`, typing a status starts to feel slow, and people stop updating in real time.

Version drift. Offline edits and copies create forks. Google’s version history helps for about 30 days, but it cannot merge two divergent truths.

No audit trail worth the name. The time log is append-only by convention only. Nothing stops an edit, and the change log is a discipline, not a control.

When should you move the tracker into a project management tool?

Three triggers, in order of reliability. First, more than one person edits the sheet daily — that is when conflicts and stale rows start. Second, when a missed dependency causes a real slip, because that is proof you need sequencing rather than a list. Third, when the weekly update takes longer than the weekly review, which for most teams happens somewhere between 8 and 15 people.

Doitify is our product, so read this as disclosure rather than a review. It covers the same structure the template describes — projects, tasks and sub-tasks, owners, due dates, Kanban boards, Gantt charts, workload views, and reports — with status derived from the work rather than typed into a cell. The reason teams move is not that the sheet was badly built. It is that the sheet has no way to know that a task moved, and a tool does.

The migration itself is a copy, not a rebuild: export `01_Tasks` as CSV, import it as a project, map Status and Owner to your tool’s fields, and keep the Sheet read-only for one sprint so nobody loses their safety net.

What mistakes break most trackers?

Too many columns. Every field beyond fourteen is a reason to not open the file. Add a column only when a decision depends on it.

Status as free text. Without dropdowns, you get “In progress,” “in-progress,” “WIP,” and “ongoing,” and every `COUNTIFS` silently undercounts.

Estimates in days, actuals in hours. Pick one unit. Mixing them is the most common cause of a forecast that is wrong by a factor of eight.

One tab for everything. Time entries written into the task row get overwritten by the next update, and history disappears.

No owner for the template. A tracker with 200 rows and no maintainer becomes an archive within a quarter.

Dates stored as text. `TODAY()` comparisons return nonsense, `QUERY` sorts alphabetically, and nothing warns you.

Conclusion

A project tracker is not a spreadsheet with a date column. It is three linked calculations: status derived from dates and progress, progress weighted by effort, and a forecast derived from remaining hours against real capacity. Get those three right and the sheet earns its place; skip them and you have built a form that people fill in until they stop.

Start with the five tabs, paste the formulas, and run one weekly review from the dashboard. If the review takes ten minutes and surfaces a real risk, keep going. If it takes forty minutes because you are reconciling four copies, that is the signal — the work has outgrown the file.

If this post on project tracker template for Google Sheets was helpful, you might also enjoy What Is Definition of Done in Scrum? A Complete Guide with Examples and Meeting Minutes vs Meeting Notes: Key Differences and When to Use Each.