An Excel tracker that updates itself

The job today

Your team keeps a task list in Excel. Before each meeting, someone scrolls to find what is overdue, colors a few cells by hand, and copies some numbers into an email. Then a new row gets added under the list, and the formulas and colors don't reach it. Two people keep two copies, and the copies drift apart. The list is only as current as the last person who fixed it.

What changes when it's automated

The list becomes a table that grows as rows are added, and the totals, flags and lists follow it. Overdue flags compare each due date with today's date, so they change on their own.

Dropdown filters let anyone pick an owner or a status, and the whole page follows. There are no macros, so there is no macro warning to click through.

The steps, in plain words

1. Name the columns you track: task, owner, status, due date.

2. Decide what “overdue” and “due soon” mean for your work.

3. The table and the dashboard get built on made-up rows first.

4. You paste in your real rows, and the dashboard fills itself in.

What you can try on your own first

Click anywhere in your list and press Ctrl+T. That turns it into a table, and a table grows by itself when you type below it.

For overdue flags, select your list, beginning at row 2, then add a conditional formatting rule that uses a formula, such as =AND($D2<TODAY(),$E2<>"Done"). Here column D holds the due date and column E holds the status. A Status column with a dropdown (Data, Data Validation, List) keeps entries clean, and COUNTIFS can count the tasks in each status.

Before you ask anyone for help, write down in one sentence what “overdue” means for your work. A task that is blocked on a supplier may not count as late, and that rule decides how the flags should work.

When it's worth getting help

Doing it yourself works well for one simple list. Help makes sense when you want totals, flags, filters and “this week” lists on one page, when the list has outgrown one person's attention, or when you'd rather not debug formulas. Know the limits first. One Excel table and one dashboard per job; no macros. We build and test in Windows Excel and haven't tested on a Mac. If you use a Mac, tell us before the quote.

Where to go next

We explain what we build and how we quote on the Excel dashboards and trackers page. You can also see the Excel work tracker demo, built on made-up tasks, or read the common questions.