How-To· 7 min read

How to Make a Task Tracker in Excel

A task tracker in Excel should tell you three things without you checking each row: what is overdue, what is in progress, and how far along the work is. Here is how to build one with seven columns and a handful of formulas.

Short on time? Our free Project Task Tracker has the status dropdown, days left and overdue count built in. Get the free task tracker template →

Step 1: The columns

In row 1: Task, Owner, Start, Due, Status, % complete, Days left. Format Start and Due as dates and % complete as a percentage.

Step 2: A status dropdown

Select the Status column, go to Data → Data Validation → List and enter Not started,In progress,Done. The counts in step 4 depend on the exact wording, so a dropdown is what keeps them right.

Step 3: Days left that never go stale

In G2:

=IF(E2="Done","—",D2-TODAY())

TODAY() updates every time the file opens, so the number is always current. A negative number means the task is late. Finished tasks show a dash instead of a meaningless count. Copy the formula down the column.

Step 4: A summary at the top or bottom

With tasks in rows 2 to 100:

  • Not started: =COUNTIF(E2:E100,"Not started")
  • In progress: =COUNTIF(E2:E100,"In progress")
  • Done: =COUNTIF(E2:E100,"Done")
  • Overdue: =COUNTIFS(D2:D100,"<"&TODAY(),E2:E100,"<>Done"). This counts tasks whose due date has passed and that are not marked Done.
  • Average completion: =AVERAGE(F2:F100)

Step 5: Colour that does the reading for you

  • Overdue rows in red: select A2:G100, then Home → Conditional Formatting → New Rule → Use a formula, and enter =AND($D2<TODAY(),$E2<>"Done"). Pick a light red fill.
  • Progress bars: select the % complete column, then Conditional Formatting → Data Bars.
  • Done in grey: another formula rule, =$E2="Done", with grey text.

Step 6: Keep it usable

  • Freeze the header row (View → Freeze Panes → Freeze Top Row).
  • Turn on filters (Ctrl+Shift+L) so each person can filter to their own tasks.
  • Insert new tasks inside the table rather than below it, so every range in the summary includes them.

When a task list is not enough

If tasks depend on each other or you need to see them across weeks, a timeline (Gantt) view is clearer. If you are tracking problems reported by users rather than planned work, an issue tracker with priority and severity fits better.

Frequently Asked Questions

How do I show days remaining in Excel?

Subtract today's date from the due date: =D2-TODAY(). Wrap it in IF to hide it for finished tasks: =IF(E2="Done","—",D2-TODAY()).

How do I count overdue tasks?

Use COUNTIFS with two conditions: due date before today and status not Done: =COUNTIFS(D2:D100,"<"&TODAY(),E2:E100,"<>Done").

Can several people use the same tracker?

Yes. Store it in OneDrive, SharePoint or Google Drive and edit it together. Filters let each person see only their own tasks.

Does it work in Google Sheets?

Yes. TODAY, COUNTIF, COUNTIFS, dropdowns and conditional formatting all work in Google Sheets.

Download the free task tracker

Days left, overdue count, status summary and average completion, already working. Free with an account.

Get the free task tracker template →