Accounts Receivable Tracking Spreadsheet

Unpaid invoices sitting in your email aren’t accounts receivable — they’re hope. Actual AR management means knowing exactly who owes you what, how long they’ve owed it, and having a system that tells you when to follow up. A spreadsheet can do all of that.

Here’s how to build one that turns chasing money from a stressful chore into a predictable process.

The Core AR Tracker

Set up one row per invoice with these columns:

Column	What Goes Here
Invoice #	Your invoice number
Customer	Company or person name
Invoice Date	Date you sent it
Due Date	Payment terms applied
Amount	Full invoice amount
Amount Paid	Partial or full payment received
Balance Due	=Amount - Amount Paid
Days Outstanding	=TODAY() - Invoice Date
Status	Open / Partial / Paid / Write-off
Last Contact	Date of last follow-up
Notes	What was said, promises made

Filter this sheet by Status = “Open” and you have your working AR list.

Building the Aging Report

An aging report groups your AR by how long invoices have been outstanding. It’s the standard tool for understanding your receivables health at a glance. Add a summary section or a second tab with these buckets:

• Current (not yet due): =SUMIFS(BalanceDue, DaysOutstanding,"<0")
• 1-30 days past due: =SUMIFS(BalanceDue, DaysOutstanding,">0", DaysOutstanding,"<=30")
• 31-60 days past due: =SUMIFS(BalanceDue, DaysOutstanding,">30", DaysOutstanding,"<=60")
• 61-90 days past due: =SUMIFS(BalanceDue, DaysOutstanding,">60", DaysOutstanding,"<=90")
• 90+ days past due: =SUMIFS(BalanceDue, DaysOutstanding,">90")

Named ranges make the formulas cleaner. Select your Balance Due column, go to Data > Named Ranges, call it BalanceDue. Do the same for Days Outstanding.

Add a % of Total AR column next to each bucket: =BucketAmount / TotalAR. If more than 20% of your AR is 60+ days, you have a collection problem that needs attention now, not next month.

Automatic Follow-Up Triggers

Add a column called Follow-Up Due with this formula:

=IF(Status="Paid","", IF(DaysOutstanding<0, Due Date, IF(DaysOutstanding<=30, Invoice Date+7, IF(DaysOutstanding<=60, TODAY(), "URGENT"))))

What this does:

• For invoices not yet due: flags for follow-up 7 days before the due date (a friendly reminder)
• For 1-30 days past due: flags immediately
• For 31-60 days: flags as today (meaning follow up today)
• For 60+: shows URGENT

Apply conditional formatting: red fill for URGENT, yellow for any Follow-Up Due date that’s today or earlier. Filter by this column every Monday morning.

The Follow-Up Sequence

Most people give up on collections after one awkward email. A sequence takes the decision-making out of it:

Day 1 past due — Email: “Hi [Name], just checking in — invoice #[X] for $[amount] was due [date]. Happy to resend if it got lost. Let me know if you have any questions.”

Day 7 past due — Email + Phone: Reference the prior email. Ask specifically when you can expect payment. Get a date.

Day 21 past due — Firm email: “I need to collect on this invoice by [specific date]. If there’s an issue with the work, let’s talk. If payment is on its way, please confirm the amount and date.”

Day 45 past due — Final notice: State you’ll be turning the account over to collections or pursuing other remedies if not paid by a hard deadline.

Day 60+ — Decide: Collections agency (they take 25-50%), small claims court (good for amounts under $10k), or write it off.

Log every contact in your Notes column with the date.

What to Do With Genuinely Overdue Invoices

Before you write anything off or send it to collections, try two things:

Payment plan offer. A customer who can’t pay $3,000 today might pay $1,000 this month and $1,000 each of the next two months. Half the battle is just asking. Get it in writing.

Settlement offer. If an invoice is 90+ days old and you’re not confident you’ll collect, offer 75 cents on the dollar for immediate payment. A $3,000 invoice that becomes $2,250 in your account today beats $3,000 in aging receivables. Write off the difference as bad debt — it’s tax-deductible.

Preventing the Problem

Add a Credit Limit column for repeat customers. If someone owes you more than $2,000 and hasn’t paid, stop delivering work until they do. You’re not a bank.

Also track average days to pay per customer: =AVERAGEIF(Customer, "CustomerName", DaysOutstanding). Customers who consistently pay in 45 days get net-30 terms revised to payment-due-on-receipt or a deposit requirement.

Next Step

Export your current invoices from whatever system you use (QuickBooks, FreshBooks, a folder of PDFs) and build this spreadsheet today. Sort by Days Outstanding descending. Whatever is at the top of that list — that’s your first call this week. One follow-up call on a stale invoice often shakes loose money you’d mentally written off.