Free Project Tracker Template for Excel (Download + Guide)

What should the project tracker template contain?

project tracker template for Excel is a key topic in modern project management and teamwork. Ten columns, four of them typed and six calculated. Each row is one task, and the table has a single header row with no merged cells, because merged cells break every formula that matters.

Two design choices carry most of the value. First, every calculated column sits to the right of its inputs, so the sheet reads left to right like a sentence. Second, there is no manual status dropdown at all. If a stakeholder wants to set status by hand, they are really asking to break the audit trail.

Skip the temptation to add cost, hours, and RAG in the same block. A tracker that tries to be a budget model stops being read by the people doing the work. Put budget on its own tab with four rows.

Which formulas make the tracker update itself?

Seven formulas do the whole job. Type them once, convert the range to a table with Ctrl+T, and rename the table to `Tasks` so the structured references below resolve.

1. Working-day duration

=NETWORKDAYS([@Start],[@Due])

This counts Monday to Friday between two dates, inclusive, and skips weekends. Add a holiday range as a third argument if your project crosses public holidays: `=NETWORKDAYS([@Start],[@Due],Holidays!$A$2:$A$12)`.

2. Status, derived from dates and progress

=IF([@[Percent done]]>=1,"Done",IF([@Due]<TODAY(),"Overdue",IF([@[Percent done]]>0,"In progress",IF([@Start]<=TODAY(),"Due now","Not started"))))

Read it as a decision chain: a task at 100% is done regardless of date, a task past its due date that is not done is overdue, a task with any progress is in progress, a task that should have started is due now, and everything else has not started. Four states are enough. Teams that add “blocked” as a status usually mean “we need a decision”, which belongs in Notes with a name attached.

3. Days late

=IF([@[Percent done]]>=1,0,MAX(0,TODAY()-[@Due]))

`MAX(0, …)` is what keeps the column clean: tasks that are early return zero instead of a negative number that ruins every average downstream.

4. At-risk flag

=IF(AND([@Status]<>"Done",[@Due]-TODAY()<=3),"At risk","")

Three days is a starting threshold, not a law. For tasks with external dependencies, use seven.

5. Duration-weighted progress

=SUMPRODUCT(Tasks[Duration],Tasks[Percent done])/SUM(Tasks[Duration])

This is the number to report to anyone outside the team. A simple average of percent-complete treats a two-day task and a twenty-day task as equally important, which is how projects report 90% complete for three weeks.

6. Overdue count and next milestone

=COUNTIF(Tasks[Status],"Overdue")
=MINIFS(Tasks[Due],Tasks[Status],"<>Done")

The second one answers the only question an owner asks in a status meeting: what is the next real date. `MINIFS` needs Excel 2019 or Microsoft 365.

7. Owner load

=SUMIF(Tasks[Owner],$O2,Tasks[Duration])

Put a list of names in column O and this returns the working days each person is carrying. Two people above 30 days of open work on a 20-day horizon is your capacity problem, visible before a deadline slips.

How do the formulas behave on a real example?

Numbers make the case better than descriptions, so here are three.

Worked example one: why the simple average lies. A five-task plan runs for 40 working days in total. Durations are 10, 2, 20, 5, and 3 days. Percent complete is 100%, 100%, 20%, 0%, and 0%.

Simple average: (100 + 100 + 20 + 0 + 0) ÷ 5 = 44%.

Weighted: (10×100 + 2×100 + 20×20 + 5×0 + 3×0) ÷ 40 = (1000 + 200 + 400) ÷ 40 = 1,600 ÷ 40 = 40%.

The difference is four points, which sounds trivial until you convert it into days: 4% of a 40-day plan is 1.6 days of work. The simple average is not just imprecise, it is systematically optimistic, because small completed tasks inflate it.

Worked example two: turning progress into a forecast. Same plan, now on day 20 of 40. Earned work is 40% of 40 days, which is 16 days. Schedule performance is 16 ÷ 20 = 0.80. At that rate the plan finishes in 40 ÷ 0.80 = 50 working days, ten days late.

That single division replaces an argument. Instead of “we are a bit behind”, the tracker says the plan lands on day 50. The team can then choose: recover ten days, cut scope, or move the date, and all three options have a number attached.

Add a health cell beside the weighted progress:

=IF(SPI<0.85,"Red",IF(SPI<0.95,"Amber","Green"))

At 0.80 the project is red in week three, not amber in week seven.

Worked example three: days late with a weekend in the middle. Task due Friday 13 March, today Tuesday 17 March, percent done 60%. `TODAY()-Due` returns 4 calendar days, but only two are working days. If you report calendar days late, a two-day slip looks like a four-day slip and the recovery conversation starts from the wrong number.

Fix it by using working days in the late column too:

=IF([@[Percent done]]>=1,0,MAX(0,NETWORKDAYS([@Due]+1,TODAY())-1))

The `+1` and `-1` account for inclusive counting. The result is 2 working days late, which is the figure the team can actually recover.

What conditional formatting and validation should you add?

Four rules and three validations, and then stop. Every extra rule makes the sheet slower and harder to explain.

Apply formatting to `$A$2:$M$400` so new rows inherit it.

Validations: Start and Due as dates with a helpful input message; Percent done as decimal between 0 and 1 with a whole-number rule; Owner as a list from a named range so names stay consistent. Without the last one, “Nadia”, “nadia”, and “N. Rahman” become three people in every `SUMIF`.

Then freeze panes below the header and lock the calculated columns so nobody overwrites a formula with text.

How do you set it up step by step?

Twenty-five minutes, once.

1. Open a blank workbook, type the thirteen headers in row 1, and press Ctrl+T to convert to a table. Rename it `Tasks` in Table Design.
2. Format E and F as dates, H as percentage, I and L as text, and set column widths so the sheet fits one screen.
3. Paste the formulas from the section above into G2, I2, J2, and L2, then confirm they filled down the whole table.
4. Create a `Lists` sheet with owners, workstreams, and priorities as named ranges, and point the validations at them.
5. Add the four conditional formatting rules and test each one by entering a past due date with 0% complete.
6. Build a small summary block above the table: weighted progress, overdue count, next milestone, and the health cell. Four rows, labels on the left.
7. Save the file as your master copy, read-only. Clone it per project, and never edit the master.

Step 7 is the one people skip. A tracker that doubles as the template gets a stray row within a fortnight, and every cloned project inherits it.

Where does Excel break for live project work?

Excel’s calculation is not the weak point. Coordination is.

Concurrent editing. Co-authoring works in Microsoft 365, but it degrades in specific ways: `TODAY()`-driven columns recalculate on open rather than live, offline edits merge as conflicts, and a table filtered by one person filters nothing for anyone else. Two people editing the same sheet for six weeks typically spend twenty minutes a week reconciling, which is two hours, or about $80 at a $40 rate. Not fatal, just friction that repeats.

Notifications. Excel cannot tell an owner that a task is due Thursday. At small scale, the weekly meeting covers it. At forty tasks across six people, something is always missed because a cell changed and nobody looked.

History. Version history exists, but “who moved this date and why” is a question the sheet cannot answer. Cell comments are not an audit trail.

Mobile. Reading a wide tracker on a phone means horizontal scrolling through thirteen columns. Field teams stop checking, and a tracker nobody checks is a report, not a control.

Permissions. Hiding a column is not security, and a shared workbook gives everyone the same view. If owner rates or client margins sit in the same file, they are visible to everyone with the link.

Excel, Google Sheets, or a project tool?

Each is right at a different stage. The honest comparison is about coordination, not features.

The pattern most teams follow: Excel for the business case and the budget, a board for the work. Keep the tracker for anything you report upward and archive, and move the daily task list into a tool that can notify, assign, and show a Gantt. A hybrid is not a failure of discipline; it is what the tools are each good at.

If you want the spreadsheet structure to survive the move, keep the same column names. Task, owner, start, due, percent, status. Every tool imports that shape cleanly, and the formulas become the tool’s job instead of yours.

How does Doitify handle the same tracker?

Doitify is our product, so this is disclosure. It contains the same model as the spreadsheet: tasks with owners, start and due dates, percent complete, dependencies, sub-tasks, and checklists, on top of Kanban, Gantt, calendar, and workload views.

The difference is that the calculated columns become native. Status, overdue count, weighted progress, and owner load are computed from the task data and surfaced in reports and dashboards, so the health cell is not something you rebuild per project. Import accepts the spreadsheet shape, and export returns it, which means the Excel version stays usable as a report.

That is worth paying for at one specific point: when the reconciliation and the notification gaps cost more than the subscription. Before that, the spreadsheet is genuinely fine.

` do not exist, so use `$G$2:$G$400` instead, and `MINIFS` requires a workaround or the newer Sheets function set. Conditional formatting rules transfer with the same formulas.|Why should status be a formula instead of a dropdown?::Because a dropdown depends on someone remembering to change it, and the person who forgets is usually the busiest one. A formula derives status from dates and progress, which are inputs people already update for their own reasons. The result is a status column that cannot be stale.|Can I use this tracker in LibreOffice or Excel 2016?::Yes with two edits. Replace `MINIFS` with `=SMALL(IF(…))` entered as an array, and replace structured references with absolute ranges. Everything else, including `NETWORKDAYS` and `SUMPRODUCT`, works unchanged.|How many tasks can a sheet handle before it slows down?::A few thousand rows is comfortable in modern Excel. The practical ceiling is lower than the technical one: beyond about 300 open rows, nobody scrolls, filters get complicated, and the sheet needs grouping by workstream or splitting per project.|How do I share the tracker without letting people break formulas?::Put the calculated columns on a protected sheet, unlock only the input cells, and share the file through OneDrive or SharePoint with edit rights for owners and view rights for stakeholders. Protection is a guardrail against accidents, not a security control.|Does the template handle dependencies?::Only visually, with a “Depends on” column holding task IDs. Real dependency logic, where moving one date shifts the rest, needs a Gantt tool or a formula chain that becomes unmaintainable past about ten links. At that point, move to a project tool.|What is a realistic progress figure to report upward?::Report duration-weighted progress and the forecast finish date together. Progress alone invites the question “how far behind is that”, and the forecast answers it before it is asked. If the two disagree with the team’s gut feeling, the durations are wrong, not the formula.”]

Conclusion

A project tracker template for Excel works when the sheet calculates instead of remembers. Four typed columns, seven formulas, four conditional formats, and a master copy that never gets edited.

The arithmetic is the part most trackers miss. Duration-weighted progress and a schedule forecast turn a status column into a decision tool, and they cost two cells. Build them this afternoon, run one real project through the sheet, and watch where the friction actually appears: if it is formulas, fix the formulas; if it is notifications and version conflicts, that is the signal to move the work into a tool and keep the spreadsheet for reporting.

If this post on project tracker template for Excel was helpful, you might also enjoy Event Planning Project Template: Sections, Example, and How to Use It and How to Reduce Project Status Meetings.