How to Build a Project Tracker in Excel That Stays Tidy

Coding Liquids blog cover featuring Sagnik Bhattacharya for building a project tracker in Excel, with task and status visuals.
Coding Liquids blog cover featuring Sagnik Bhattacharya for building a project tracker in Excel, with task and status visuals.

A tidy Excel project tracker is one sheet built on an Excel Table, with four-value status dropdowns, conditional formatting for colour and a summary panel made only of formulas. Nothing on the summary is typed by hand, so the tracker stays readable and holds up when six people update it.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

This 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 Does a Tidy Excel Project Tracker Include?

A tidy Excel project tracker has three zones on one sheet, top to bottom: a formula-only summary panel, optional live filtered views, and a single Tasks Table that is the only place anyone edits.

  1. 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.
  2. Live filtered views (rows 12–20, optional). Two FILTER spills: "due this week" and "blocked right now". They update automatically as task statuses change.
  3. 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: Notes

Add 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: How Do You Add Dropdowns for Status and Priority?

You add Status and Priority dropdowns with Data Validation set to a List, using four Status values and High, Medium, Low for 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,Done

Tick "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: How Do You Colour the Tracker with Conditional Formatting?

Conditional formatting colours the tracker from its Status, Due and Priority columns, so nobody colours cells by hand and the colour always matches the value.

Five 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: How Do You Build the Summary Panel?

You build the summary panel at the top of the sheet from formulas that read the Tasks Table, so no number there is typed by hand. 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 priority

Format 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 % complete

This 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.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Step 6: How Do You Show Tasks Due This Week?

You show tasks due this week with a live filtered view below the summary that lists only the tasks not yet Done and due within the next 7 days. 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 0

Expected 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.

If you want to build trackers like this with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

Which Formulas Does the Tracker Use?

The tracker uses COUNTIFS and SUMPRODUCT for the summary, FILTER for the live views, and GROUPBY or UNIQUE for the by-owner breakdown, all referencing tblTasks by name.

WhereFormulaWhat 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/B7Done 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

How Do You Adapt the Tracker for Your Team?

You adapt the tracker by keeping its skeleton of tblTasks, four-value status, summary panel and filtered views identical, and changing only the extra Table columns and the extra summary metric.

Use caseExtra Tasks columnsExtra summary metric
Software sprintStory points, Sprint numberVelocity = sum of points Done this sprint
Marketing launch planChannel, Asset link% complete by channel
Client engagement / consultingClient, Billable hoursHours logged this week vs budget
Operations checklistRecurrence (weekly / monthly), Last doneItems overdue for recurrence
Personal weekly reviewEnergy (low/med/high), Context (home/work)High-energy items still pending
Multi-project portfolioProject, RAG statusProjects 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$23 instead 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 + COUNTIFS as 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 Is an Excel Project Tracker the Wrong Tool?

An Excel project tracker is the wrong tool with more than about 200 active tasks, dependencies between tasks, time tracking, five or more concurrent editors, or a sales pipeline.

  • 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.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Frequently asked questions

How do you build a project tracker in Excel that stays tidy?

Build the tracker on one sheet around an Excel Table named tblTasks, add Data Validation dropdowns for Status and Priority, let conditional formatting do the colouring, and put a summary panel of formulas at the top. Use structured references such as tblTasks[Status] so every count extends when someone adds a row.

What columns should an Excel project tracker have?

Use eight columns: Task, Owner, Status, Priority, Start, Due, % Complete and Notes. Give every row a single owner, even when the work is shared, because if everyone owns a task, nobody does. Add other columns only when your use case needs them, such as story points and sprint number for a software sprint.

Why should a project tracker have only four status values?

Four status values (Not started, In progress, Blocked and Done) keep the summary meaningful, because extra values such as Awaiting review, On hold or Cancelled splinter the data. Put that detail in Notes instead. One 12-person product team that grew to 14 status values went back to four, lost no information and cut its weekly status meeting from 40 minutes to 12.

Should you keep the summary separate from the task rows?

Yes. Keep the summary panel at the top of the sheet, made only of formulas, and edit only the task rows in the Table below. If a summary number looks wrong, fix the task rows rather than typing over the summary cell, because overwriting the formula breaks future updates and hides the real issue.

Related tutorials on this site