Build a simple invoice tracker in Excel

Tracking open invoices and aging buckets (Current, 1–30, 31–60, 61–90, 90+ days past due) with automatic overdue flags.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

Excel for Finance and Accounting Full Course Tutorial (3+ Hours)
Learn Skills Daily
Open on YouTube (opens in a new tab)
Excel for Finance and Accounting Beginner Tutorial
Learn Skills Daily
Open on YouTube (opens in a new tab)
How to automate Accounting Ledger, Trial Balance, Income Statement, Balance Sheet in Excel
EXCEL DOERS
Open on YouTube (opens in a new tab)An invoice tracker has one job: tell you who owes money, when it was due, and
what to chase this week. Most of them fail at that not because the formulas are
wrong but because of a structural decision made in the first five minutes — a
single Amount paid column, which cannot represent an invoice paid in two
instalments.
Two tables, not one
tblInvoices — one row per invoice, issued once, never edited after that
except for status:
| Column | Notes |
|---|---|
| InvoiceNo | The unique key. Text format, to protect leading zeros |
| Customer | Dropdown from a Lists sheet, never typed |
| InvoiceDate | A real date |
| TermsDays | 14, 30, 60 — per customer, not assumed |
| DueDate | =[@InvoiceDate]+[@TermsDays] |
| Amount | Net of tax, or gross — decide once and label it |
| Currency | Even if you only use one today |
| Status | Open, Disputed, Written off |
| Notes | Why a disputed invoice is disputed |
tblPayments — one row per payment received:
| Column | Notes |
|---|---|
| PaymentDate | When the money landed, not when it was promised |
| InvoiceNo | Which invoice it settles |
| AmountPaid | Positive |
| Method | Bank transfer, card, cheque |
| Reference | The bank reference, for matching |
Format both as Excel Tables (Ctrl+T). The two-table split is what makes part
payments, overpayments, and credit notes representable at all — and it means you
never edit an invoice row to record a payment, so the invoice history stays
intact. The general case is in
Excel Table vs normal range.
The formulas that do the work
Paid to date, on the invoice row, summing across the payments table:
=SUMIFS(tblPayments[AmountPaid],tblPayments[InvoiceNo],[@InvoiceNo])
Balance:
=[@Amount]-[@PaidToDate]
Days overdue — zero rather than a negative number for invoices not yet due, so you can sort the column meaningfully:
=IF([@Balance]<=0,0,MAX(0,TODAY()-[@DueDate]))
That first condition matters: a paid invoice should not keep accruing days overdue just because it was settled late.
Ageing bucket, which is what turns the list into a report:
=IFS([@Balance]<=0,"Paid",[@DaysOverdue]=0,"Current",[@DaysOverdue]<=30,"1-30",[@DaysOverdue]<=60,"31-60",[@DaysOverdue]<=90,"61-90",TRUE,"90+")
On Excel 2016 or earlier, nest IF instead — same logic, more brackets.
Chase status, so the action is a column rather than a judgement call:
=IFS([@Balance]<=0,"",[@Status]="Disputed","Resolve dispute",[@DaysOverdue]>60,"Escalate",[@DaysOverdue]>14,"Second reminder",[@DaysOverdue]>0,"First reminder",TRUE,"")
Build the ageing summary from SUMIFS
Customers down the side, buckets across the top:
=SUMIFS(tblInvoices[Balance],tblInvoices[Customer],$A5,tblInvoices[AgeBucket],B$4)
Total row at the bottom, and a control total beside it:
=SUMIFS(tblInvoices[Balance],tblInvoices[Balance],">0")
Those two must agree. When they don’t, a customer or a bucket value exists in the data but not on the summary — usually a customer typed by hand instead of picked from the dropdown. That is the single most common failure in a receivables report, and it is exactly why the Customer column should be a validated list. See creating better dropdown lists.
Keep status values short and closed
Good status values are few and unambiguous: Open, Disputed, Written off.
Notice that Paid and Part paid are not in that list — those are calculated
from the balance, not typed. Anything derivable should be derived, because a
typed status drifts out of step with the numbers the moment somebody forgets to
update it.
Free-text status is the fastest way to make a tracker unsummarisable. Paid,
paid, PAID, and Paid - thanks! are four categories to a pivot table.
The weekly view
Filter to Balance > 0, sort by DaysOverdue descending, and read the top of
the list. That one view is usually enough to run a collections conversation.
Two numbers worth keeping visible on the sheet:
=SUMIFS(tblInvoices[Balance],tblInvoices[Balance],">0",tblInvoices[DaysOverdue],">30")
=COUNTIFS(tblInvoices[Balance],">0",tblInvoices[DaysOverdue],">30")
Value and count of debt over 30 days. The value tells you the exposure; the count tells you how many conversations it represents. They move independently and both matter.
Drive conditional formatting from the AgeBucket column rather than writing the
date thresholds a second time into the formatting rules — one rule, one place to
change it, which is the argument in
conditional formatting for KPI flags.
Don’t let TODAY() surprise you
TODAY() is volatile and recalculates whenever the workbook does. That is
correct for a live tracker and wrong for a saved report: reopen last month’s file
and every ageing figure silently re-ages.
If you need a point-in-time snapshot, put the reporting date in one named cell
(AsAtDate) and reference that instead of TODAY() everywhere. Type over it to
freeze the report. The tracker stays live while the archived copies stay true to
when they were run.
What belongs here and what doesn’t
This is a tracker, not a ledger. It will not do double entry, it will not handle partial credit notes against multiple invoices elegantly, and it should not be the system of record once you are issuing more than a few hundred invoices a month.
What it does well is sit alongside an accounting system and answer the chasing
question faster than the accounting system will. When the bank statement arrives,
match payments against it using the cleanup process in
cleaning a bank statement export —
the Reference column on tblPayments exists for exactly that join.
The invoice template and CRM contact tracker in the template library both use this two-table structure if you would rather start from a working file.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Build an accounting-friendly worksheet that reconciles, ages, or summarizes transactions clearly. For this article, the goal is to practice: An invoice tracker that answers who owes you money, when it was due, and what to chase — with the two-table structure that handles part payments and an ageing summary built from SUMIFS.
Practice workbook setup
Use sample invoices, payments, dates, customers, and amounts with a few missing or overdue records.
Practice workflow
- Separate source transactions from calculations and summary outputs.
- Use consistent account, customer, or invoice IDs for matching.
- Add checks for missing dates, negative amounts, duplicates, and unmatched records.
- Create a summary that ties back to the transaction detail.
Quality checks
- Totals reconcile between detail and summary.
- Date logic handles blanks and future dates correctly.
- Manual adjustments are labeled and separated from formulas.
Common mistakes
- Hard-coding a total that should come from transaction detail.
- Mixing text and numeric invoice IDs.
- Ignoring unapplied payments or duplicate invoice numbers.
Next actions
- Add an audit column that explains exceptions.
- Turn the layout into a reusable monthly template.
Formula focus: test SUMIFS(), TODAY(), MAX(), IF(), IFS(), COUNTIFS() with normal rows, blank inputs, and at least one edge case.






