How to Track Overdue Invoices Automatically (Free Template)

This tracker turns a list of invoices into an answer to the only question that matters: who owes you money, and how late are they? Overdue rows turn red on their own, and the summary tells you how much is stuck in each age bracket.

⬇ Download the Invoice Tracker (.xlsx)
Free, no email required. Works in Excel 2016 and later. Prefer Google Sheets? Open a copy in your own Drive.

The rest of this guide explains how it decides what counts as overdue, so you can change the rules to match your own payment terms.

The colours are a formula, not a highlighter — an invoice turns red the morning it goes late.

What it does

Sheet	What it's for
Invoices	One row per invoice. You fill in seven columns; Status and Days overdue calculate themselves.
Summary	What's open, what's overdue, how late it is, and which client is sitting on the most of your money.
Clients	Your client list. Feeds the drop-down and the per-client totals.

The formula that decides everything

Column H asks two questions in order — has it been paid, and is it past the due date:

=IF($A2="","",
   IF($F2="Yes","Paid",
      IF(TODAY()>$D2,"Overdue","Open")))

Read from the outside in:

• No invoice number in the row? Show nothing. This keeps 200 blank rows from all reporting "Overdue"
• Marked as paid? Say Paid, and stop — a paid invoice is never overdue, however late it was
• Otherwise, compare today's date to the due date

Order matters. Nested IFs stop at the first true answer, so the paid check has to come before the date check. Reverse them and every late-but-settled invoice would show as overdue forever. If nesting is unfamiliar, the IF formula guide covers it properly.

Why TODAY() is doing the real work

=IF($H2="Overdue",TODAY()-$D2,"")

TODAY() re-reads the system date every time the file opens or recalculates. Nothing needs updating by hand — an invoice that's fine on Thursday turns red by itself on Friday morning.

Subtracting one date from another gives a plain number of days, because Excel stores dates as numbers. If your result shows as a date rather than a number, the cell inherited date formatting — set it to General.

⚠️ TODAY() is volatile: it recalculates constantly, and in a workbook with tens of thousands of rows that costs speed. At this size it's irrelevant.

Making overdue rows turn red

The colour isn't typed in. It's a conditional formatting rule reading the Status column:

1. Select the whole data range, A2 to I201
2. Home → Conditional Formatting → New Rule → Use a formula
3. Enter: =$H2="Overdue"
4. Set a light red fill
5. Repeat with =$H2="Paid" and a light green fill

The dollar sign in $H2 is what makes the whole row colour rather than one cell. Locking the column but not the row means every cell in row 2 looks at H2, and row 3 looks at H3.

Write =H2="Overdue" without the dollar sign and each column checks a different column's status — the result is a scattering of coloured cells that looks like a bug, because it is one.

The aging summary

The "How late" block sorts unpaid invoices into brackets. Each figure is a SUMIFS with two conditions applied to the same column:

=SUMIFS(Invoices!$E$2:$E$201,
        Invoices!$H$2:$H$201, "Overdue",
        Invoices!$I$2:$I$201, ">=31",
        Invoices!$I$2:$I$201, "<=60")

Naming the Days overdue column twice — once for each end of the bracket — is how you express "between 31 and 60" in a function that has no BETWEEN. The same technique handles any range of numbers or dates, and it's covered in more depth in the SUMIFS guide.

Why aging matters more than a single overdue total: 30,000 spread across invoices two weeks late is a cash-flow timing issue. The same 30,000 sitting past ninety days is money you may not collect. One number hides that difference; four brackets show it.

Changing the payment terms

The template assumes you type the due date yourself. To calculate it from the issue date instead, put this in the Due date column:

=IF($C2="","",$C2+30)

Change 30 to your terms. For end-of-month terms:

=IF($C2="","",EOMONTH($C2,1))

⚠️ If different clients have different terms, add a Terms column to the Clients sheet and look it up rather than hard-coding a number — otherwise you'll be editing formulas every time a contract changes.

Building it from scratch

1. Clients sheet — names in column A.
2. Invoices sheet — Invoice no., Client, Issue date, Due date, Amount, Paid, Date paid, Status, Days overdue.
3. Data validation on Client (list from the Clients sheet) and on Paid (type Yes,No directly into the source box).
4. The nested IF in Status, the subtraction in Days overdue, filled down.
5. Two conditional formatting rules on the full range.
6. Summary sheet — SUMIF by status, SUMIFS for the aging brackets, SUMIFS by client.

Half an hour, and you'll be able to bend it to how your business actually invoices.

Does this work in Google Sheets?

Yes. IF, TODAY, SUMIFS, COUNTIFS, EOMONTH and formula-based conditional formatting all behave the same way. The menu path differs slightly: Format → Conditional formatting → Custom formula is.

When it misbehaves

Everything says Overdue, including blank rows

The IF($A2="","",...) guard is missing. Without it, an empty due date reads as zero, which is before today, so every blank row reports as late.

Dates that aren't dates

Dates pasted from accounting software often arrive as text. TODAY()>$D2 then compares a number to text and gets a wrong answer without erroring. Select the column and use Data → Text to Columns → Finish.

Days overdue shows a date

The cell picked up date formatting from its neighbours. Set it to General.

The summary doesn't match the rows

Usually a status typed by hand somewhere, overwriting the formula. Check for a Status cell that isn't bold — the calculated ones all are.

Common questions

Can I track partial payments?

Not as it stands. Add an "Amount received" column and change Status to compare that against the invoice amount. The template deliberately stays binary because most small operations bill that way.

Can it email reminders?

Not from a spreadsheet alone — that needs a script or an accounting package. What the tracker gives you is the list to work from, sorted by how late each one is.

Should I use this instead of accounting software?

If you issue a handful of invoices a month, this is faster and free. Past that, or once you need tax reporting, use proper software. This is a tracker, not a ledger.

Will the colours survive in Google Sheets?

Yes. Formula-based conditional formatting rules convert on upload.

The short version

• Status is a nested IF: paid first, then the date comparison.
• TODAY() is what makes the sheet re-read itself with no maintenance.
• Conditional formatting needs $H2 — column locked, row free — to colour whole rows.
• Aging brackets are SUMIFS naming the same column twice, once per end of the range.
• Guard every calculated column with IF($A2="","",...) or blank rows will lie to you.

Next steps: the status column is built on nested IF logic, and the aging brackets on SUMIFS with two conditions on one column. If you're tracking money going out as well as coming in, the expense tracker uses the same summary pattern.