Project Tracker in Excel — Build a Live Tracker with Progress Bars & RAG Status (2026)

A spreadsheet full of tasks is not a project tracker — a tracker tells you, at a glance, what is on track, what is slipping, and how far along the whole project is. In 45 minutes you’ll build a self-updating Excel tracker with a status dropdown, automatic RAG flags, in-cell progress bars, an effort-weighted completion percentage, and a one-click Gantt timeline. No add-ins, no macros, works in Excel 2016 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Project Tracker?

A project tracker is a structured spreadsheet that records every task in a project alongside its owner, dates, status, and completion, and then turns that raw list into an at-a-glance picture of project health. The difference between a to-do list and a tracker is calculation: a tracker derives new information — days remaining, overall progress, which tasks are overdue, how the workload is distributed — from the data you enter, and it keeps that information current automatically.

In Excel, a good project tracker rests on three pillars. The first is a clean task table: one row per task, consistent columns, no merged cells. The second is a layer of formulas that compute duration, days left, RAG status, and percent complete without you touching them. The third is a visual layer — in-cell data bars, conditional-formatting colours, and a Gantt timeline — that lets a stakeholder understand the project in five seconds without reading a single number.

The reason Excel remains the most popular project-tracking tool on earth, despite hundreds of dedicated apps, is flexibility. You can model any methodology — waterfall phases, sprints, a simple checklist — with the same building blocks. There are no per-seat licences, no rigid templates, and no learning curve for the people who will actually read the tracker. The trade-off is that Excel does not enforce structure for you: the moment someone types “in prog.” instead of “In Progress”, your counts break. This guide builds in the guard-rails — data validation, structured Tables, and formula-driven status — that keep a tracker reliable as it grows.

By the end you will have a tracker that updates its own status flags every morning, shows a live progress bar for the whole project, highlights overdue work in red automatically, and renders a Gantt timeline that redraws itself whenever a date changes. Everything is built with native Excel features, so it opens and works identically for anyone you share it with.

Before You Start — Columns & Data Rules

Five minutes of structure now saves hours of broken formulas later. Follow these rules before you type a single task.

Rule 1: One row = one task

Every row must describe a single, atomic piece of work. Resist the urge to write “Design & build & test the login page” in one row — split it into three. Atomic tasks are easier to assign, estimate, and mark complete, and they make your progress percentage meaningful.

Rule 2: Every column has a unique header, with no gaps

Put headers in row 1 with no blank header cells and no blank columns inside the data block. Excel Tables and formulas treat a blank column as the edge of your data, so a stray empty column will silently cut off half your tasks.

Rule 3: Dates must be real dates

Start Date and Due Date must be genuine Excel dates, not text. A quick test: select a date cell and look at the status bar or change the format to General — a real date becomes a 5-digit serial number like 45901. Text dates will not subtract, sort, or feed the Gantt. Fix text dates with Data → Text to Columns → Finish.

Rule 4: Use a number, not text, for % Complete

Store progress as a decimal between 0 and 1 (so 0.5 means 50%) and format the column as a percentage. Decimals let you average, weight, and feed data bars; the word “half done” cannot do any of those.

The recommended column set

1.Step 1 — Design the Task Table

Open a blank worksheet and type your headers in row 1. A dependable starting set is: Task ID, Task, Owner, Phase, Start Date, Due Date, Status, % Complete, Effort, and Notes. Enter three or four real tasks underneath so you have something to test against.

Now the single most important move in this whole tutorial: click any cell inside your data and press Ctrl+T. Tick “My table has headers” and click OK. In the Table Design tab, rename the table ProjectTasks. This converts your range into a structured Excel Table.

Why this matters so much: a Table makes every formula you write auto-fill down the entire column, lets you reference data by name (ProjectTasks[% Complete] instead of H2:H50), and — critically — grows automatically when you add a task, so your dashboard never misses new rows.

Add a Task ID you can reference

A short, stable Task ID (T-001, T-002…) is invaluable once you add dependencies. Type the first one, then in the next row use =ROW()-1 formatted to look like an ID, or simply fill a sequence. IDs let one task point to another (“blocked by T-004”) without relying on the task name, which often changes.

2.Step 2 — A Status Dropdown with Data Validation

Free-text status fields are where trackers go to die. “In Progress”, “in prog”, “WIP”, and “ongoing” all mean the same thing to a human but four different things to COUNTIF. Lock the field down.

Select the entire Status column, then go to Data → Data Validation → Settings → Allow: List. In the Source box, type your statuses separated by commas:

Click OK. Now every Status cell shows a dropdown arrow and rejects anything off the list. Your counts and colour-coding will stay reliable forever because the underlying values can only ever be one of four exact strings.

Colour the statuses

Select the Status column → Home → Conditional Formatting → Highlight Cells Rules → Text that Contains. Add one rule per status: green for Complete, blue for In Progress, red for Blocked, grey for Not Started. The colour now follows the dropdown automatically.

3.Step 3 — Duration and Days Left

These two columns turn static dates into live signals. Add a Duration column and, in the first data cell, type:

Because you are inside a Table, the structured reference [@[Due Date]] means “the Due Date in this row”, and the formula fills down every row automatically. Format the result as a number, not a date, or Excel will show you a date in 1900.

Now add a Days Left column that compares each due date to today:

A positive number is days remaining; zero is due today; a negative number means the task is overdue by that many days. Because TODAY() recalculates every time the file opens, this column is your tracker’s heartbeat — everything downstream (RAG status, overdue highlighting) depends on it.

Only count working days

If weekends should not count, swap the subtraction for NETWORKDAYS:

Add a third argument pointing to a range of public-holiday dates — =NETWORKDAYS(TODAY(),[@[Due Date]],Holidays) — to exclude bank holidays too.

4.Step 4 — An Automatic RAG Status Column

RAG (Red / Amber / Green) is the universal language of project status. The trick is to derive it from data so it can never go stale. Add a RAG column with this nested IF:

Read it top to bottom: if the task is 100% complete it is Green (done is done). Otherwise, if it is past its due date it is Red. Otherwise, if it is due within three days it is Amber. Everything else is Green. The logic mirrors how an experienced PM scans a plan — finished, late, or about-to-be-late.

A smarter, progress-aware RAG

The version above only looks at dates. A more sophisticated flag compares expected progress (how far through the schedule we are) against actual progress. Add an Expected % helper:

Then flag Red when actual lags expected by more than 15 points: =IF([@[% Complete]]>=[@[Expected %]]-0.15,"Green","Red"). This catches tasks that are technically on-date but quietly falling behind.

Turn the word into a colour

Select the RAG column → Home → Conditional Formatting → Highlight Cells Rules → Text that Contains. Add “Red” (red fill), “Amber” (yellow fill), and “Green” (green fill). Or use an Icon Set of traffic lights driven by a numeric version (1/2/3) of the RAG.

5.Step 5 — In-Cell Progress Bars

Numbers tell; bars show. A column of percentages is far easier to scan when each cell also contains a little bar. Select the % Complete column (data cells only), then go to Home → Conditional Formatting → Data Bars → Solid Fill. Pick a colour that matches your theme.

By default Excel scales the bars to the largest value in the range, which means a 40% task can look “full” if it is the highest. Fix the scale: go to Conditional Formatting → Manage Rules → Edit Rule, and set Minimum to Number 0 and Maximum to Number 1 (use 100 if your values are whole percentages). Now a 50% bar always fills exactly half the cell, and bars are comparable across rows.

Show the bar only

In the same Edit Rule dialog, tick Show Bar Only to hide the number and leave a clean visual gauge — useful on a dashboard where the precise figure is shown elsewhere. Untick it on the working sheet where people need the exact value.

6.Step 6 — Overall Project Completion

Stakeholders want one number: how done is the whole thing? The naive answer averages every task’s percentage:

This is fine when tasks are roughly equal in size. But it lies when they are not: finishing ten trivial tasks while the one enormous task sits at 0% would report “91% complete” even though most of the real work remains. The honest figure weights each task by its effort:

SUMPRODUCT multiplies each task’s completion by its effort, sums those products, then divides by total effort — the textbook formula for a weighted average. This is the number you should put on the dashboard, because it tracks how much of the work, not the task count, is finished.

Count tasks by status

Pair the headline percentage with status counts so people see the shape of the work:

Repeat for “In Progress”, “Blocked”, and “Not Started”, and add =COUNTIFS(ProjectTasks[% Complete],"<1",ProjectTasks[Due Date],"<"&TODAY()) for a live count of overdue tasks.

7.Step 7 — An Automated Gantt Timeline

A Gantt timeline shows when each task happens. You can build a chart, but the lightest method uses conditional formatting and lives right next to your table. To the right of your data, create a row of dates (one column per day or week) starting at your project start. Put the first date in, say, K1, then fill across with =K1+7 for weekly buckets.

Select the empty grid under those date headers (the same number of rows as your tasks), then add a new conditional-formatting rule → Use a formula → enter:

Here $E2 is Start Date and $F2 is Due Date for the task in that row. The mixed references (K$1 locks the row, $E2 locks the column) make the rule shade a cell whenever its column’s date falls inside that task’s window. Pick a fill colour and Excel paints a bar across the calendar for every task at once — and redraws it the moment any date changes.

Shade progress within the bar

Add a second rule that uses a darker shade where the date is also before Start + Duration × % Complete, giving you a Gantt bar that visually fills as work is done. For a true chart-based Gantt with milestones and dependencies, see our dedicated Gantt chart in Excel tutorial.

8.Step 8 — A Summary Dashboard

Put the headline numbers on their own clean area — a separate sheet or the top of your tracker. The essentials are: the weighted completion percentage as a large KPI, a progress bar driven by that cell, the status counts from Step 6, and the overdue count. Drive a big visual progress bar from the completion cell by merging a few cells and applying a single-cell data bar, or use a bar chart with one series.

If your data is in a Table, add a slicer (Table Design → Insert Slicer) on Owner or Phase. Slicers give stakeholders one-click filtering: click “Sam” and the whole table — and any PivotTables you have connected — narrows to Sam’s tasks. Connect a slicer to multiple PivotTables via Report Connections so one click filters the entire dashboard.

Roll up multiple projects

If you added a Project column, a portfolio view is just SUMIFS and COUNTIFS by project. Build a small table listing each project with its weighted completion (SUMPRODUCT with a project filter via an extra criteria array) and overdue count. One flat table, one dashboard, every project — no copy-paste between sheets.

Try It: Interactive Project Tracker

This is exactly what your Excel tracker behaves like. Use the − and + buttons to change each task’s completion in 10% steps. Watch the per-task progress bars fill, the RAG badges flip between Green, Amber, and Red based on progress and due date, and the overall effort-weighted completion at the top recalculate live — the same way SUMPRODUCT and conditional formatting work in the real sheet.

Advanced Project Tracker Tips

Model dependencies

Add a Depends On column holding the Task ID a task waits for. Then compute an earliest-start with XLOOKUP to pull the predecessor’s due date: =XLOOKUP([@[Depends On]],ProjectTasks[Task ID],ProjectTasks[Due Date])+1. Flag a scheduling conflict when a task’s start is earlier than its dependency allows.

Auto-sort by urgency

In Microsoft 365, build a live, self-sorting view with SORT and FILTER on a separate sheet: =SORT(FILTER(ProjectTasks[[Task]:[Days Left]],ProjectTasks[% Complete]<1),5,1) returns all unfinished tasks sorted by Days Left ascending — the most urgent work floats to the top automatically.

Highlight the whole overdue row

Select all task rows, add a formula rule =AND($H2<1,$F2<TODAY()) (H = % Complete, F = Due Date), and choose a subtle red fill. Now an overdue task lights up its entire row, not just one cell — impossible to miss in a long list.

Push it to a live dashboard

When the tracker outgrows a single editor — you need real-time collaboration, scheduled email updates, or a shareable link for clients — upload the same spreadsheet to DataHub Pro. It reads your existing columns and produces a live status dashboard with automatic refresh, so you keep planning in Excel and stop rebuilding charts by hand.

Common Errors & Fixes

Duration shows a date like “05/01/1900”

Excel inherited the date format from the cells you subtracted. Select the Duration column and set the number format to General or Number. The 5 means five days.

Days Left never changes

Calculation is probably set to Manual. Go to Formulas → Calculation Options → Automatic, or press F9 to force a recalculation. TODAY() only updates on recalculation or file open.

Data bars look wrong / one bar is always full

The scale is still automatic. Edit the rule and pin Minimum to 0 and Maximum to 1 (or 100). Without a fixed maximum, Excel scales to the biggest value in view, so comparisons across rows become meaningless.

COUNTIF returns zero for an obvious match

Almost always a stray space or inconsistent capitalisation in the Status column — the exact reason Step 2 uses Data Validation. Re-apply the dropdown and re-select the offending cells from the list.