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.