How to Build a Customer Payment Follow-Up Sheet in Excel

This article first appeared on Invoala.

A customer payment follow-up sheet in Excel is one row per invoice, with columns for the customer, invoice number, invoice date, due date, amount, amount paid, days overdue, and the date of your last reminder. The follow-up work itself comes from three formulas: one that calculates days overdue, one that buckets each invoice into a status (paid, due, overdue), and one that tells you when the next nudge is owed. Everything else is housekeeping.

Below is a setup you can build in about fifteen minutes, plus the parts most templates get wrong.

The columns that actually matter

Skip the decorative bits and start with this. Put it in row 1 as headers, freeze it, and add a filter.

• Customer — consistent naming. "Acme Ltd" and "Acme Limited" should never both exist, or filtering breaks.
• Invoice no. — your reference; keep it as text so leading zeros survive.
• Invoice date and Due date — two separate columns. Don't store payment terms ("Net 30") as text only; you want a real date to calculate against.
• Amount — the total the customer owes, including any tax you charged.
• Paid — the amount actually received so far. This lets you handle part-payments, which are the single most common reason a tracker goes wrong.
• Outstanding — =Amount − Paid. A formula, never typed by hand.
• Status — paid, part-paid, due, overdue. Formula-driven.
• Days overdue — formula-driven, zero or blank when not overdue.
• Last contact and Next follow-up — the two dates that make the sheet a follow-up tool rather than a list.
• Notes — short factual lines: "promised Friday", "disputed £90 late fee", "AP contact is Priya". Keep it boring and useful.

One optional column worth adding: Aging bucket. More on that below.

Photo by Marissa Grootes on Unsplash

The formulas, with the exact syntax

Assume headers are in row 1 and your first data row is row 2. Invoice date in C, due date in D, amount in E, paid in F, last contact in J, next follow-up in K.

Outstanding balance:

=IF(A2="","",E2-F2)

Days overdue (returns 0 if not yet due, and ignores blanks):

=IF(D2="","",MAX(0,TODAY()-D2))

Status:

=IF(A2="","",IF(F2>=E2,"Paid",IF(TODAY()>D2,"Overdue",IF(TODAY()=D2,"Due today","Due"))))

That gives you "Paid" only when the received amount covers the total, which keeps part-payments visible instead of silently marking them done.

Next follow-up. The simplest useful rule: nudge three business days after the due date, then weekly. A cell-per-row version:

=IF(OR(B2="",F2>=E2),"",IF(J2="",D2+3,MAX(D2+3,J2+7)))

That reads as: if nothing has been sent yet, follow up three days after the due date; otherwise, seven days after the last contact — never earlier than three days past due. Adjust the numbers to whatever rhythm you actually keep.

Aging bucket, if you want to see how much of your money is old:

=IF(E2=F2,"Paid",IF(TODAY()<=D2,"Not due",IF(TODAY()-D2<=30,"1–30 days",IF(TODAY()-D2<=60,"31–60 days","60+ days"))))

Then a pivot table on the Aging bucket column, summed on Outstanding, gives you the number that matters: how much cash is stuck in the oldest bucket.

Format dates as dates (Excel will otherwise store them as text when you paste from PDFs and emails), and use number format with two decimals for money. Set the whole Status and Days overdue columns to conditional formatting — red for Overdue, amber for "Due today" — so the sheet tells you what to do before you read it.

Example: tracking three invoices in one week

Suppose it's 14 March and you've issued three invoices:

• INV-1042, Acme Ltd: $2,400 invoiced, 30-day terms from 1 February, so due 3 March. Received nothing. TODAY() minus 3 March gives 11 days overdue, Outstanding $2,400, Status "Overdue", bucket "1–30 days". Last contact was blank, so the Next follow-up formula returns 6 March — already past, meaning act today.
• INV-1043, Bridgewater Studio: $850, due 20 March. Days overdue = 0, Status "Due", Next follow-up 23 March. Nothing to do this week beyond noting it.
• INV-1044, Calder Design: $1,600, due 10 March, with $600 received. Outstanding is $1,000 and the status stays "Overdue" rather than "Paid" — exactly the case a naive tracker hides. Note that the customer paid a portion; your reminder should reference the balance, not the full invoice.

Total outstanding $4,400, of which $3,400 is overdue. That's your working list for the week: one email to Acme, one short note to Calder confirming the $1,000 balance and asking when it lands, and nothing to Bridgewater.

Notice what the sheet did not do: it didn't tell you what to say. A follow-up sheet produces a queue; the message is still yours to write. That's also why the Notes column earns its place — the next time you open the sheet you should be able to tell in three seconds who you're chasing and what they last promised.

Two caveats. TODAY() is volatile, so the sheet re-evaluates on open; if you need a frozen snapshot for a report, copy the values into a separate tab. And if your team works across countries, agree on one date format and one currency before anyone types a number.

Photo by Dimitri Karastelev on Unsplash

Where spreadsheets stop working

A hand-maintained sheet has a failure mode: it only helps if you remember to open it. The usual breakpoints are more than roughly twenty open invoices, multiple people touching the same file, or invoice data that has to be re-typed from whatever tool you issued it from.

If you already produce invoices somewhere, the duplication is the real cost. You can generate the PDF with the free Invoala invoice generator — fill a form, download an A4-accurate PDF, no sign-up and no watermark — and then the question becomes where the follow-up happens.

Before you automate anything, it's worth keeping the sheet honest for a month. If you find yourself filtering by Status and Days overdue every Monday, that's a signal to move the tracking into invoice payment tracking that already knows which invoices are paid, due or overdue, rather than rebuilding it by hand each week.

How Invoala helps with the follow-up loop

The sheet works; it just needs feeding. Here's where Invoala removes steps:

1. Issue the invoice. Open the invoice generator, fill in the customer details, line items, tax and payment terms, and download the PDF. Because the invoice is A4-accurate, the layout doesn't shift when the customer prints it — which matters when someone forwards it to their accounts payable team.
2. Keep the due date accurate. The due date you set on the invoice is the anchor for everything downstream; a wrong date here produces a wrong Days overdue column everywhere.
3. Let reminders run. Instead of re-typing Last contact and Next follow-up every week, automated payment reminders handle the follow-ups for unpaid invoices, so the schedule lives with the invoice rather than in a file you have to remember to open.
4. Watch the status. Tracking paid, due and overdue is the same three-bucket logic as the Status formula above, minus the maintenance.
5. Fall back to the sheet when you need one. If a client or a bookkeeper wants a single view, export the list and keep using your pivot table. Nothing about using a generator stops you from keeping a spreadsheet for your own sanity.

Pricing is free forever with an optional upgrade, if you want to check what's included before relying on it.

A short reminder script that matches the sheet

When the sheet says "act today", send something short and specific rather than a generic chase. Something like:

That references the invoice number, the amount and the exact due date — all three of which came straight out of the tracker. It's also the reason a clean invoice PDF matters: the customer can find the reference without hunting.

Two practical notes on tone, which depends on the relationship and the country: many businesses send a gentle note a few days after the due date and a firmer one after two or three weeks, but collection practices, late-payment interest and any statutory demand process vary by jurisdiction. If an invoice is seriously in arrears, check the official guidance where you and your customer are based, or take advice, rather than relying on a spreadsheet formula to decide your next move.

The sheet is your memory. The formulas are your clock. Keep both simple and you'll chase the right invoices on the right day, which is most of the job.

---

Originally published at How to Build a Customer Payment Follow-Up Sheet in Excel. More guides at Invoala.