For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Build a simple invoice tracker in Excel

AR.E4 =IF(TODAY()>DueDate, TODAY()-DueDate, 0)
Build a simple invoice tracker in Excel — Excel example preview

Tracking open invoices and aging buckets (Current, 1–30, 31–60, 61–90, 90+ days past due) with automatic overdue flags.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Excel for Finance and Accounting Full Course Tutorial (3+ Hours)

Learn Skills Daily

Open on YouTube (opens in a new tab)
Learn Skills Daily

Loading preview…

video / Intermediate

How to automate Accounting Ledger, Trial Balance, Income Statement, Balance Sheet in Excel

EXCEL DOERS

Open on YouTube (opens in a new tab)
EXCEL DOERS

Loading preview…

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.

=PRACTICE

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

  1. Separate source transactions from calculations and summary outputs.
  2. Use consistent account, customer, or invoice IDs for matching.
  3. Add checks for missing dates, negative amounts, duplicates, and unmatched records.
  4. 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.