For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Create a simple project tracker in Excel

PRJ.G5 =AND(G$4>=$C5, G$4<=$D5)
Create a simple project tracker in Excel — Excel example preview

A conditional-formatting Gantt timeline in pure Excel cells that highlights active project weeks between Start Date and End Date.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Excel Project Tracker: Easily Manage & Track Projects Like a Pro!

Excel Simplified

Open on YouTube (opens in a new tab)
Excel Simplified

Loading preview…

video / Intermediate

The Excel Gantt Chart That Replaces Your Project Management Tool

MyOnlineTrainingHub

Open on YouTube (opens in a new tab)
MyOnlineTrainingHub

Loading preview…

Excel is not a project management system, and for a team of four it is usually better than one. The threshold is roughly this: if nobody is going to update a proper tool, a spreadsheet that gets updated is worth more than a Gantt chart that doesn’t.

The trick to one that stays current is to make updating it cheap — which mostly means calculating everything that can be calculated instead of asking people to type it.

One row per task

Format it as a Table (Ctrl+T, name it tblTasks) so formulas fill down as rows are added:

Column Notes
ID A stable number. Used for dependencies
Task What gets done. A verb and an object
Phase For grouping. Dropdown from Lists
Owner Exactly one name. Dropdown
Start A real date
Days Working days of effort
Due Calculated, not typed — see below
PercentDone 0, 25, 50, 75, 100. A dropdown, not free entry
Blocked Y/N
BlockedBy Why or who
Notes

Two columns deliberately absent: a typed Status, and a typed Days remaining. Both are derived below, and a derived field cannot go stale.

Calculate the due date so a slipped start moves everything that depends on it:

=WORKDAY([@Start],[@Days],Holidays)

WORKDAY skips weekends, and Holidays is a named range of public holidays on your Lists sheet. A plan built on calendar days will be wrong by about two days a week, every week, which compounds fast across a quarter.

Calculate status instead of asking for it

A typed status is wrong the moment somebody forgets to change it. Derive it:

=IFS([@PercentDone]=100,"Done",[@Blocked]="Y","Blocked",[@Due]<TODAY(),"Overdue",[@Start]<=TODAY(),"In progress",TRUE,"Not started")

The order matters — IFS returns on the first match, so Done must come before Overdue or completed tasks keep showing as late.

Add days remaining, in working days:

=IF([@PercentDone]=100,"",NETWORKDAYS(TODAY(),[@Due],Holidays)-1)

Now the only things a person types each week are PercentDone and Blocked. Everything else follows. That is the difference between a tracker people maintain and one that is three weeks stale by the time anyone looks.

Build the Gantt with conditional formatting

No add-in required. Put dates across the top of the sheet — one column per day for a short project, per week for a longer one — starting in, say, column N.

Set row 3 as the date header: N3 holds the project start date, O3 is =N3+7, and fill right.

Select the whole grid under those headers and add a conditional formatting rule with a formula:

=AND(N$3>=$E4,N$3<=$G4)

where $E4 is Start and $G4 is Due. Fill with your bar colour, no text.

The reference pattern is the whole trick: N$3 locks the row so every cell compares against its own column’s date, and $E4 locks the column so every cell compares against its own row’s dates. Getting one of those $ signs wrong is the usual reason a first attempt paints the wrong shape.

Three rules worth layering on top, in this order:

=AND(N$3>=$E4,N$3<=$G4,$J4="Y")

for blocked tasks, in amber — placed above the normal bar rule so it wins.

=AND(N$3>=$E4,N$3<=$G4,$H4=100)

for completed tasks, in grey, so the eye skips them.

=N$3=TODAY()

for a today line — a thin border or a tinted column. This one is worth more than it sounds: a Gantt without a today marker requires the reader to find today themselves, and they usually don’t.

Narrow the date columns to about 20 pixels and the whole thing reads as a proper bar chart.

Keep ownership singular

Every task gets one owner. Shared ownership sounds collaborative and reliably means nobody follows up — put collaborators in Notes.

Use a dropdown for the Owner column sourced from a Lists Table, not free text. J. Smith, Jane, and jane smith are three people to a COUNTIFS. The setup is in creating better dropdown lists.

The weekly view is the point

Nobody reads a 200-row task list. The thing that gets used is a short filtered view, and it is worth building it as a separate block at the top of the sheet.

Four counts:

=COUNTIFS(tblTasks[Status],"Overdue")
=COUNTIFS(tblTasks[Status],"Blocked")
=COUNTIFS(tblTasks[Due],">="&TODAY(),tblTasks[Due],"<="&TODAY()+7,tblTasks[Status],"<>Done")
=COUNTIFS(tblTasks[Status],"Done")/COUNTA(tblTasks[ID])

Overdue, blocked, due this week, and percentage complete. Those four numbers are the status report, and having them on the sheet means nobody has to write one.

Then a list — on Microsoft 365, as a formula that refreshes itself:

=SORT(FILTER(tblTasks[[Task]:[Owner]],(tblTasks[Status]="Overdue")+(tblTasks[Status]="Blocked")>0),2,1)

The + between the two conditions is an OR. On older versions, use an AutoFilter on the Status column instead.

Handle dependencies lightly

Full dependency chains are where Excel project trackers go to die — the formulas become recursive, and Excel does not do that well.

A version that works: a DependsOn column holding a task ID, and an earliest start derived from it:

=IFERROR(WORKDAY(XLOOKUP([@DependsOn],tblTasks[ID],tblTasks[Due]),1,Holidays),[@Start])

Then flag the conflicts rather than auto-shifting dates:

=IF([@Start]<[@EarliestStart],"Starts before predecessor ends","")

Flagging beats cascading. An automatic reschedule across a chain of tasks hides the decision; a flag puts it in front of the project manager, which is where it belongs.

Know when to stop

If you are building resource levelling, critical path, or multi-project portfolio views, you have outgrown this and the spreadsheet is now costing more than it saves.

What this does well is a single project with a few dozen tasks and a team that will actually open the file. The project tracker and project timeline Gantt templates both ship with this structure if you would rather not build it from scratch.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Create a project tracker that shows ownership, status, deadlines, and risk without becoming a second project plan. For this article, the goal is to practice: A task tracker that stays current — one row per task, calculated status instead of typed, a conditional-formatting Gantt that needs no add-in, and the weekly view that makes the file worth updating.

Practice workbook setup

List tasks with owners, start dates, due dates, status, priority, and notes.

Practice workflow

  1. Define the statuses before entering tasks.
  2. Add date checks for overdue, due soon, and completed work.
  3. Create filtered views for owner, status, and priority.
  4. Summarize open tasks and overdue work at the top of the sheet.

Quality checks

  • Every task has one owner and one next action.
  • Status labels are consistent and validated.
  • The summary matches the visible task table.

Common mistakes

  • Tracking too many low-value fields.
  • Using inconsistent status names such as Done, Complete, and Completed.
  • Leaving completed tasks mixed with current work without a filter.

Next actions

  • Add a weekly update view for the next project meeting.
  • Create a simple timeline or Gantt-style view only after the task table is clean.

Formula focus: test IF(), IFS(), TODAY(), COUNTIFS(), WORKDAY(), NETWORKDAYS(), AND() with normal rows, blank inputs, and at least one edge case.