Accounts Receivable / Outstanding Tracker in Excel

Q: How do I calculate days overdue in Excel?

Use `=MAX(0, TODAY() − DueDate)`. `TODAY()` is the current date, so the number stays accurate each day, and `MAX(0, …)` keeps not-yet-due invoices at zero.

Q: Is the template free?

Yes — free to download, no sign-up. ---

⬇ Download the free receivables & payables tracker (Excel) — no sign-up

Last updated: 27 June 2026 · Includes the exact columns and formulas, plus a free template.

An accounts receivable tracker in Excel is a single sheet listing every unpaid sales invoice — customer, invoice number, invoice date, due date, amount, amount received and balance — with a formula that flags how overdue each one is so you chase the right customers first. Built with TODAY() and conditional formatting, it turns a pile of pending invoices into a clear, sorted daily call list.

Key takeaways

One row per invoice with seven core columns: customer, invoice no., invoice date, due date, amount, received, balance.
Balance = Amount − Received; Days overdue = TODAY() − Due date (only when positive).
Conditional formatting turns overdue rows red, so the worst accounts surface instantly.
Sort by days overdue (descending) to get today's collection priority list.
A summary block (SUMIF) shows total outstanding and total overdue at the top.
The same data feeds your ageing report and payment reminders.

Fact box. Outstanding receivables are amounts customers owe you for invoices already raised but not yet paid. Tracking each invoice's due date (not just its amount) is what lets you act before money becomes long-overdue.

What columns should an AR tracker have?

A clean receivables tracker needs these columns, left to right:

Column	Purpose	Example / formula
Customer	Party name	Sharma Traders
Invoice No.	Unique reference	INV-2026-0142
Invoice Date	When raised	02-06-2026
Credit Days	Agreed terms	30
Due Date	When payable	`=Invoice Date + Credit Days`
Amount	Invoice total	₹84,000
Received	Paid so far	₹40,000
Balance	Still owed	`=Amount − Received`
Days Overdue	Lateness	`=MAX(0, TODAY() − Due Date)`
Status	At a glance	`=IF(Balance=0,"Paid",IF(Days Overdue>0,"Overdue","Due"))`

Keep one row per invoice. If a customer makes a part-payment, update the Received column — the balance and status recalculate automatically.

How do I flag overdue invoices automatically?

Use the Days Overdue column with conditional formatting so problem accounts colour themselves:

Select the data rows.
Home → Conditional Formatting → New Rule → Use a formula.
Red fill: =$I2>30 (more than 30 days overdue — adjust the column letter to your Days Overdue column).
Amber fill: =AND($I2>0,$I2<=30).
Green/none for =$I2=0.

Now sort by Days Overdue, largest first, and the reddest rows — your oldest, riskiest balances — sit at the top.

Fact box. =MAX(0, TODAY() − Due Date) returns days overdue and shows 0 for invoices not yet due — so you never see misleading negative "overdue" numbers. TODAY() refreshes every time the file opens, keeping the tracker current with no manual edits.

How do I see total outstanding at a glance?

Add a small summary block above the table using SUMIF and COUNTIF:

Total outstanding: =SUM(Balance column)
Total overdue: =SUMIF(Days Overdue column, ">0", Balance column)
Number of overdue invoices: =COUNTIF(Days Overdue column, ">0")
Outstanding for one customer: =SUMIF(Customer column, "Sharma Traders", Balance column)

This gives you the two numbers that matter every morning: how much is owed in total, and how much of it is already late.

How to build the tracker (step by step)

Set up headers in row 1 using the columns above and freeze the row (View → Freeze Panes).
Enter the Due Date formula =InvoiceDate + CreditDays and copy down.
Add Balance =Amount − Received.
Add Days Overdue =MAX(0, TODAY() − DueDate).
Add Status with the nested IF above.
Apply conditional formatting for overdue rows.
Add the summary block with SUMIF/COUNTIF.
Format as a Table (Ctrl+T) so new rows inherit formulas automatically.

A free, ready-to-use receivables tracker is available to download, no sign-up — open it, replace the sample rows, and you're tracking.

How Ankeshan helps: Ankeshan builds this receivables view from your sales invoices automatically inside Excel — balances, overdue flags and customer totals update as you bill and collect, with no formulas to maintain. It's launching soon; join the waitlist.

Frequently asked questions

What is an accounts receivable tracker? It's a sheet listing every unpaid customer invoice with its due date and balance, plus a flag for how overdue it is — so you know exactly who to follow up with and how much they owe.

How do I calculate days overdue in Excel? Use =MAX(0, TODAY() − DueDate). TODAY() is the current date, so the number stays accurate each day, and MAX(0, …) keeps not-yet-due invoices at zero.

How do I handle part-payments? Keep a "Received" column and update it as money comes in. The Balance (Amount − Received) and Status recalculate, so a part-paid invoice shows its remaining balance.

Can one tracker handle ageing too? The same data drives ageing — you just bucket the balances by days overdue. See the AR/AP ageing report guide.

Is the template free? Yes — free to download, no sign-up.

Sources

Microsoft Excel function reference — TODAY, SUMIF, COUNTIF, IF: support.microsoft.com.
General accounting practice for receivables/debtors management.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.