Receivables · Guide
How to generate customer statements from Excel
A statement is not an invoice. It's a summary of everything on an account — invoices, credit notes, payments and the balance — over a period. Excel can produce them, but the last 20% is where the time goes.
You need four things per row before you start: a customer reference, a document number, a document date, and an amount. Due date and document type make life easier. If your export has a payment row as a negative amount, keep it that way rather than netting it off elsewhere.
1. Start from one clean ledger
Export every transaction for the period, not just open items. Statements are usually read as a history, and customers will query a payment they can't see. Filter out anything dated after the statement date before you go further.
2. Build a running balance
Sort by customer, then by date. In a new column, add this — with G$2 as an absolute anchor to the first row:
=SUMIFS($E$2:E2, $A$2:A2, A2)That gives a per-customer running balance without breaking when the customer changes. A common shortcut is a plain cumulative sum, which silently carries the previous customer's balance into the next one.
3. Calculate days overdue, then bucket it
Age from the due date, never the invoice date. Put the statement date in its own cell so every formula reads from one place:
Days overdue =MAX(0, $B$1 - C2)
Bucket =IFS(E2=0, "Current",
E2<=30, "1-30",
E2<=60, "31-60",
E2<=90, "61-90",
TRUE, "90+")The MAX(0, …) matters: without it, invoices that aren't due yet land in a negative bucket and disappear from the report.
4. One statement per customer
This is the part that costs the afternoon. Three options, roughly in order of how much they'll annoy you later:
- PivotTable + filter. Fast for a handful of customers. Slow and error-prone at forty, because you're copying sheets by hand.
- Mail merge into Word. Set up once with a filtered query per customer, then merge to a single PDF. Powerful, but the merge rules break whenever the ledger shape changes.
- A macro that loops customers and writes a sheet per account. Works well, until the person who wrote it leaves.
If you do one thing: put the statement date in a fixed cell instead of using TODAY(). Otherwise the statement a customer received last week no longer matches the one you send this week, and reconciliation conversations get much harder.
5. Tie the total back before you send
Sum the closing balance of every statement and compare it to the AR control account as at the statement date. They should agree to the penny. If they don't, the cause is almost always one of: payments dated after the cut-off, credit notes sitting in a different period, or unapplied cash still on the account.
6. Send them without sending them one at a time
Mail merge straight to PDF, then attach via whatever bulk mail tool your firm already uses. Keep the statement date in the email subject line — it saves a remarkable number of "which statement is this?" replies.
When it stops being worth it
If you have more than about twenty accounts with activity, the manual build takes a full afternoon every month and the reconciliation step is the first thing to get skipped when you're busy. That's the point at which the work is mechanical enough to hand off.