Project trackers go bad slowly. They start as a helpful list, then attract extra columns, inconsistent statuses, manual colour signals, and unclear ownership until nobody is quite sure what the sheet is actually telling them. The fix is not a fancier template — it is a smaller, stricter pattern that holds up when six people are editing it at once.
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreThis tutorial builds a one-sheet tracker from scratch: an Excel Table for tasks, a four-value status dropdown, conditional formatting that does the colour work for you, and a summary panel at the top that derives every number from the task rows below. Nothing on the summary is hand-edited. Anyone joining the team in week six can open the sheet and read it in 30 seconds.
Requires Microsoft 365 (desktop or web). The summary uses FILTER, COUNTIFS, and SUMPRODUCT — all available there. Excel 2019 alternatives are noted where each modern function appears, so a 2019 reader can still build the same tracker with a slight loss of dynamism. For broader Excel context see the Excel formulas hub.
Setup time end to end is about 30 minutes for the first tracker. Every tracker after that, you can clone the sheet, blank the task rows, and ship in 5 minutes — the formulas, validation, and conditional formatting all carry over because they reference the Table by name, not by cell address.
What we are building
One sheet, three zones, top to bottom:
- Summary panel (rows 1–10). Single cell totals: tasks open, blocked, due this week, overdue, % complete, plus a tiny "by owner" mini-table. Every cell is a formula. Nobody types here.
- Live filtered views (rows 12–20, optional). Two
FILTERspills: "due this week" and "blocked right now". They update automatically as task statuses change. - Tasks Table (row 22 onwards), named
tblTasks. Columns: Task, Owner, Status, Priority, Start, Due, % Complete, Notes. This is the only place anyone edits.
The discipline is that nothing in zones 1 and 2 is hand-edited. If a number on the summary looks wrong, the fix is in the task rows below, not the summary cell. This split is the single change that stops trackers rotting.
Step 1 — Create the Tasks Table
On a fresh sheet, leave rows 1–21 empty for now (we will fill the summary later). In row 22, type the headers across columns A–H:
A22: Task B22: Owner C22: Status D22: Priority
E22: Start F22: Due G22: % Complete H22: NotesAdd one sample row in row 23 (anything — you will overwrite it later). Click anywhere inside A22:H23, press Ctrl+T, confirm "My table has headers", and click OK. Excel converts the range to a Table.
Now name it. Click inside the Table → Table Design tab → Table Name box (top-left) → type tblTasks → Enter. Every formula below references tblTasks[Status], tblTasks[Due], etc. — never raw cell ranges. That is what makes the tracker survive someone deleting or inserting rows.
Beginner pitfall. Skipping the Table conversion and leaving the task list as a plain range. SUMIFS and COUNTIFS against a plain range stop working the moment someone appends a row past the original last row, and the summary silently under-reports. Always Ctrl+T first.
Step 2 — Add data validation dropdowns for Status and Priority
Status discipline is the next-biggest decision after the Table. Four values, no more: Not started, In progress, Blocked, Done. Anything richer (Awaiting review, On hold, Deferred, Cancelled) splinters the dataset and the summary becomes meaningless.
Select the Status column header drop arrow → Select Column. Then Data tab → Data Validation → Settings → Allow: List → Source:
Not started,In progress,Blocked,DoneTick "In-cell dropdown" and click OK. Repeat for Priority with the source High,Medium,Low. Now anyone clicking a Status or Priority cell gets a dropdown — typos are impossible, which is what keeps COUNTIFS counting honestly.
Real-world scenario. A 12-person product team's tracker grew to 14 status values over six months: "In review", "In QA", "QA passed", "Awaiting deploy", "Deployed but unverified", and so on. The "% done" number stopped matching anyone's gut feel. Collapsing back to four values lost zero information (the nuance lived in the Notes column where it belonged) and the weekly status meeting went from 40 minutes to 12.
Step 3 — Conditional formatting for status, overdue, and priority
Three rules, applied to the whole Table body. Select the body cells of tblTasks (click inside the Table, then Ctrl+A once to select the data, twice to include headers — use once here), then Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format":
- Status = Done → green strike-through. Formula:
=$C23="Done". Format: light green fill, grey text, strikethrough. Replace 23 with the first data row of your Table. - Status = Blocked → red. Formula:
=$C23="Blocked". Format: light red fill, dark red text. - Status = In progress → yellow. Formula:
=$C23="In progress". Format: light yellow fill. - Overdue (Due before today, not Done) → amber border. Formula:
=AND($F23<TODAY(), $C23<>"Done", $F23<>""). Format: thick amber left-border. The$F23<>""guard stops empty Due cells flagging. - Priority = High and not Done → bold. Formula:
=AND($D23="High", $C23<>"Done"). Format: bold text.
The rules stack: a Blocked High-priority overdue task ends up with red fill, bold text, and an amber border simultaneously. That is what you want — visual triage by glance.
Beginner pitfall. Mixing absolute ($C$23) and relative references in the formula. The status-column reference must be locked at $C23 (column locked, row free) so each row evaluates against its own Status. Locking the row too ($C$23) makes every cell in the Table check row 23 only.
Step 4 — Build the summary panel
Move to the top of the sheet. In rows 1–10, lay out the headline numbers. Use tblTasks structured references throughout — they auto-extend as the Table grows.
A1: PROJECT TRACKER
A3: Status counts
B3: =COUNTIFS(tblTasks[Status], "Not started") Label: Not started
B4: =COUNTIFS(tblTasks[Status], "In progress") Label: In progress
B5: =COUNTIFS(tblTasks[Status], "Blocked") Label: Blocked
B6: =COUNTIFS(tblTasks[Status], "Done") Label: Done
B7: =ROWS(tblTasks) Label: Total tasks
B8: =B6/B7 Label: % Complete (format as %)
A10: Risk
B10: =COUNTIFS(tblTasks[Due], "<"&TODAY(), tblTasks[Status], "<>Done") Label: Overdue
B11: =LET(today, TODAY(),
COUNTIFS(tblTasks[Due], ">="&today, tblTasks[Due], "<="&today+7, tblTasks[Status], "<>Done"))
Label: Due in next 7 days
B12: =COUNTIFS(tblTasks[Status], "Blocked", tblTasks[Priority], "High") Label: Blocked + High priorityFormat B10:B12 with conditional formatting: red text if value > 0. Now you have a four-number traffic light. Open the workbook on Monday morning, glance at B10–B12, know whether the week starts calm or on fire.
For weighted progress (giving each task a weight by % Complete rather than just done/not-done):
B9: =SUMPRODUCT(tblTasks[% Complete]) / ROWS(tblTasks) Label: Weighted % completeThis treats a task that is 60% done as 0.6 of a finished task, which is closer to the truth on long-running work than a binary done count.
Step 5 — Add a "by owner" breakdown
Beside the headline counts, build a small mini-table of tasks per owner. The modern way uses GROUPBY (Microsoft 365, rolled out 2024); the classic way uses a UNIQUE-driven helper column.
Modern (GROUPBY). In cell D3, single formula:
=GROUPBY(tblTasks[Owner], tblTasks[Task], COUNTA, 0, 0)This spills a two-column table: each unique owner and the count of their tasks. Add a third dimension by passing more columns to the values argument; =GROUPBY(tblTasks[Owner], tblTasks[Status], COUNTA, 0, 0) gives a status-by-owner cross-tab.
Classic (UNIQUE + COUNTIFS). If GROUPBY is not available:
D3: =UNIQUE(tblTasks[Owner])
E3: =COUNTIFS(tblTasks[Owner], D3#)The D3# spill reference makes E3 expand to match D3's spill range. As new owners appear in the Table, both columns extend automatically.
Pre-365 fallback. Manually list owners in column D and use =COUNTIFS(tblTasks[Owner], D3) beside each. You lose the auto-extend behaviour but the pattern is otherwise identical.
Step 6 — Add a "due this week" live view
Below the summary, build a filtered live view that shows only the tasks that need attention this week. Microsoft 365's FILTER makes this one formula:
A14: DUE THIS WEEK
A15: =LET(today, TODAY(),
soon, (tblTasks[Due]>=today)*(tblTasks[Due]<=today+7),
open, tblTasks[Status]<>"Done",
FILTER(tblTasks[[Task]:[Due]], soon*open, "Nothing due this week"))The result spills across columns A–F under row 15. Tasks not Done with a Due date inside the next 7 days appear; everything else is hidden. The third argument is the friendly fallback for an empty result. As task rows change in the Table below, this live view updates instantly — no refresh button.
Why LET? LET names sub-expressions inside one formula. Here it computes TODAY() once and gives readable names (soon, open) to the two boolean arrays that drive the FILTER. Without LET the formula reads as a wall of tblTasks[Due]>=TODAY()-style fragments; with LET, the intent is in the variable names. The plain version still works — LET is a clarity upgrade, not a correctness fix. (M365 only; pre-365 users keep the un-named version above.)
Repeat for blocked tasks at row 19:
A19: BLOCKED RIGHT NOW
A20: =FILTER(tblTasks[[Task]:[Owner]],
tblTasks[Status]="Blocked",
"No blockers")Pre-365 fallback. Use a PivotTable with a slicer for Status and a Due-date filter, refreshed manually. It works, but it does not update live.
Step 7 — Test with a real project
Drop in 8–10 tasks and walk through the cycle once.
Task Owner Status Priority Start Due % Notes
Brief stakeholders Alex Done High 2026-04-20 2026-04-22 100
Draft requirements doc Priya In progress High 2026-04-22 2026-04-29 70 Awaiting legal sign-off
Wireframes Marcus Not started High 2026-04-30 2026-05-06 0
API contract Priya Blocked High 2026-04-25 2026-04-30 30 Vendor not responding
Database migration plan Alex In progress Medium 2026-04-26 2026-05-02 40
Marketing one-pager Sara Not started Low 2026-05-04 2026-05-10 0
Internal demo prep Marcus Not started Medium 2026-05-08 2026-05-12 0
Release notes Sara Not started Low 2026-05-15 2026-05-18 0Expected summary (assuming today is 2026-04-30): Status counts 4 / 2 / 1 / 1 (Not started / In progress / Blocked / Done). Total tasks 8. % complete 12.5%. Weighted % complete ≈ 30% (sum of % column 240/8). Overdue 1 — the Draft requirements doc, due yesterday and still In progress (the API contract is due today, so it is in the next-7-days bucket, not overdue). Due in next 7 days 3 — the API contract (today), the Database migration plan (May 2), and Wireframes (May 6). Blocked + High 1 (API contract).
The "Due this week" view spills three rows: the API contract, the Database migration plan, and Wireframes. The "Blocked right now" view spills only the API contract. Mark the API contract as Done by changing its Status to "Done" — every number above and both spills update without a single click anywhere else.
The full formula reference
| Where | Formula | What it does |
|---|---|---|
| B3 Status count | =COUNTIFS(tblTasks[Status], "Not started") | Count of tasks at this status (repeat for each value) |
| B7 Total tasks | =ROWS(tblTasks) | Row count of the Table — auto-extends |
| B8 % Complete | =B6/B7 | Done divided by total; format as percentage |
| B9 Weighted % Complete | =SUMPRODUCT(tblTasks[% Complete]) / ROWS(tblTasks) | Treats partial progress as fractional completion |
| B10 Overdue count | =COUNTIFS(tblTasks[Due], "<"&TODAY(), tblTasks[Status], "<>Done") | Past-due tasks that are not Done |
| B11 Due in 7 days | =LET(today, TODAY(), COUNTIFS(tblTasks[Due], ">="&today, tblTasks[Due], "<="&today+7, tblTasks[Status], "<>Done")) | Upcoming-week workload; LET names today so TODAY() is computed once |
| B12 Blocked + High | =COUNTIFS(tblTasks[Status], "Blocked", tblTasks[Priority], "High") | The single most important number on the sheet |
| D3 By-owner (modern) | =GROUPBY(tblTasks[Owner], tblTasks[Task], COUNTA, 0, 0) | Spills owner + count in one formula (M365) |
| D3 By-owner (classic) | =UNIQUE(tblTasks[Owner]) + =COUNTIFS(tblTasks[Owner], D3#) | Same result without GROUPBY |
| A15 Due-this-week view | =LET(today, TODAY(), soon, (tblTasks[Due]>=today)*(tblTasks[Due]<=today+7), open, tblTasks[Status]<>"Done", FILTER(tblTasks[[Task]:[Due]], soon*open, "Nothing due this week")) | Live filtered spill of upcoming work; LET names the two boolean filters |
| A20 Blocked-now view | =FILTER(tblTasks[[Task]:[Owner]], tblTasks[Status]="Blocked", "No blockers") | Live filtered spill of current blockers |
| CF rule (Done) | =$C23="Done" | Green strikethrough on completed rows |
| CF rule (Blocked) | =$C23="Blocked" | Red highlight on blocked rows |
| CF rule (Overdue) | =AND($F23<TODAY(), $C23<>"Done", $F23<>"") | Amber border on past-due, not-done rows |
| CF rule (High priority) | =AND($D23="High", $C23<>"Done") | Bold on open high-priority rows |
Variations: customise for your team
The skeleton (tblTasks + four-value status + summary panel + filtered views) stays identical. What changes is which extra columns you keep on the Table and which extra metric you add to the summary.
| Use case | Extra Tasks columns | Extra summary metric |
|---|---|---|
| Software sprint | Story points, Sprint number | Velocity = sum of points Done this sprint |
| Marketing launch plan | Channel, Asset link | % complete by channel |
| Client engagement / consulting | Client, Billable hours | Hours logged this week vs budget |
| Operations checklist | Recurrence (weekly / monthly), Last done | Items overdue for recurrence |
| Personal weekly review | Energy (low/med/high), Context (home/work) | High-energy items still pending |
| Multi-project portfolio | Project, RAG status | Projects at Red status |
For a portfolio view, add a Project column and use a slicer (Table Design → Insert Slicer → Project) so the same single tracker can flip between projects without rebuilding. The summary panel formulas can be wrapped to honour the active filter using SUBTOTAL(2, ...) or AGGREGATE.
Common mistakes
- Plain range instead of an Excel Table. COUNTIFS stops counting new rows; the % complete number drifts; nobody notices for two weeks.
- Free-text Status column. "in progress", "In Progress", "WIP", "in-progress" are four different statuses to COUNTIFS. The dropdown solves this in 30 seconds.
- Hand-coloured status cells instead of conditional formatting. The day someone changes a status, the colour stops matching, and now the colour and the cell value disagree silently.
- Editing summary cells when a number "looks wrong". Destroys the formula, breaks future updates, hides the actual issue. Never edit the summary; only edit task rows.
- Status sprawl. Adding "On hold", "Cancelled", "Awaiting review" turns four-bucket reporting into 12-bucket reporting. The information belongs in Notes, not in Status.
- No owner column, or one owner is "the team". If everyone owns it, nobody owns it. Single owner per row, even if the work is shared.
Troubleshooting
- Summary count shows 0 for a status that obviously exists. Trailing space in the dropdown source (
"Done "vs"Done"). Re-enter the data validation source without the space, then Find & Replace any existing rows. - FILTER returns #CALC! "Empty array". The third argument (the friendly fallback) is missing or empty. Add
"Nothing due this week"as the fallback. - FILTER returns #SPILL!. A neighbouring cell is occupied. Clear the spill range or move the formula.
- Conditional format only colours the first row. Absolute reference on the Status column (
$C$23instead of$C23). Edit the rule and remove the row-anchor dollar sign. - GROUPBY returns #NAME?. You are on Excel 2019/2021 — fall back to
UNIQUE+COUNTIFSas shown in Step 5. - "Due in next 7 days" includes Done tasks. The COUNTIFS criteria are missing the
tblTasks[Status], "<>Done"pair. The<>operator is the not-equals comparator.
When this is not the right tool
- More than ~200 active tasks. Performance is fine, but the cognitive load of one big table outweighs the simplicity. Move to a real PM tool (Asana, Linear, Jira, ClickUp) or split into multiple project sheets.
- Dependencies between tasks matter. Excel does not natively render "Task B starts when Task A is Done". Use a Gantt chart as the visual layer, or move to a real PM tool.
- Time tracking against tasks. Possible in Excel but unergonomic. Use Toggl, Harvest, or your PM tool's native timer.
- Concurrent editing by 5+ people. Excel's locking model fights you. SharePoint/OneDrive co-authoring helps for 2–3 editors; beyond that, move to a database-backed tool.
- Sales pipelines specifically. Use the sales pipeline tracker instead — the schema and metrics are different.
Related tutorials on this site
- How to Make a Gantt Chart in Excel for Real Project Planning — the visual schedule layer that pairs with this tracker for date-heavy projects.
- How to Build a Sales Pipeline Tracker in Excel for Small Teams — sibling pattern for revenue-side workflows.
- How to Create a Calendar in Excel That Updates Automatically Every Month — the calendar view when dates need to be plotted, not listed.
- How to Build an Attendance Tracker in Excel for Teachers, Trainers, and Team Leads — the people-side companion: same Table-driven discipline, different schema.
- Excel Tables: Structured References, Growth, and Cleaner Models — why
tblTasks-style references are non-negotiable for trackers. - How to Add a Dropdown List in Excel Using Data Validation — deeper dive on the technique behind Step 2.
- Conditional Formatting Tips That Actually Save Time — more patterns for Step 3.
- The Complete Excel Guide 2026 — hub for every Excel tutorial on the site.