Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

INVOICES AND E-INVOICING

Track unpaid invoices in Excel: know at any time who still owes you money

It is the 28th. In three days, payroll for 22 people goes out, and there is less in the account than you expected. Who still owes you money, and since when? To track unpaid invoices you have an Excel list your assistant keeps. Nobody else really understands it, and today your assistant is out of the office.

A man in glasses goes through a sheet on a clipboard at his desk next to a laptop, the way you check unpaid invoices before month end
Photo: Vitaly Gariev on Unsplash

A list that is wrong costs you twice. Reminders go out late, and the money stays out of your account for weeks longer. Or the worst happens: a good customer gets a late payment letter for an invoice they paid ten days ago, just because the payment reference said "Inv 412" instead of the full invoice number.

This guide shows how to track unpaid invoices in Excel with a list everyone on the team can read: the columns it needs, the formulas for due date, days overdue and aging bucket, and how to match payments from your bank statement before the first reminder goes out. With a worked example.

Quick answer: how to track unpaid invoices in Excel

An unpaid invoices list, in accounting terms an accounts receivable list, shows every sales invoice that has not been paid in full, with amount, due date and days overdue. Sorted into age brackets, it becomes an accounts receivable aging report, called an aged debtors report in the UK. The same structure works for your own unpaid supplier invoices. This guide covers the customer side. The list is only as good as its last match against the bank account.

  1. Set up invoices as a table

    One row per invoice with invoice number, customer, invoice date, payment terms and amount. Ctrl+T turns it into an Excel table that includes new rows in every formula.

  2. Calculate due date and days overdue

    The due date is the invoice date plus payment terms. Days overdue is the cut off date minus the due date, as long as something is still open.

  3. Match payments

    Export your bank transactions, pull the invoice number out of the payment reference and add up the payments per invoice.

  4. Clear the exceptions

    Check payments with no recognisable invoice, part payments and early payment discounts by hand. This is where the costly mistakes happen.

  5. Only then send reminders

    Read the aging bucket for each invoice, send payment reminders and note the date in the list.

Why your unpaid invoices list is so often wrong

The formulas are rarely the problem. Almost always the list is wrong because payments in the bank account do not end up against the right invoice. You can check these causes in your own list:

  • Payment reference without the full invoice number: customers write "Inv 412 October", the project name or nothing at all. The money is there, the list does not know it.
  • Part payments: a customer pays half now and the rest next month. If you only track paid yes or no, you cannot see the open balance.
  • Early payment discount: the customer deducts 2%, and €25 stays open in the list, only to show up as overdue months later.
  • Combined payments: one amount for three invoices. No single invoice matches the amount.
  • Credit notes and disputes: a credit note reduces what the customer owes but is not in the list.
  • TODAY instead of a cut off date: days overdue change every time the file opens. A month end report cannot be reproduced later.
  • One person, one system: the list works as long as the colleague who knows what each column means is in.

The columns an unpaid invoices tracker in Excel needs

Many lists have too few columns, and when it is time to chase, the one detail you need is missing. Others have 25 columns, and nobody except the author can find their way around. These ten are enough for most businesses. Use one word headings and the formulas stay short.

Columns of the table AR, one row per invoice
ColumnContentSource
InvoiceNoe.g. RE-2026-0412, uniquefrom the invoice
Customername, ideally with customer number as wellfrom the invoice
InvoiceDatedate of the invoicefrom the invoice
Termsdays, e.g. 14 or 30from invoice or contract
DueDateinvoice date plus termsformula
Amountgross invoice amountfrom the invoice
Paidsum of all matched paymentsformula from the bank statement
Openamount minus paidformula
Overduedays since due date, 0 if not dueformula
Bucketnot due, 1–30, 31–60, 61–90, over 90formula

Add a free Notes column for arrangements such as "instalments agreed" or "dispute open", and a column RemindedOn. Both stop anyone from chasing a customer you have just agreed something else with.

Due date, days overdue and aging bucket by formula

Put the cut off date in its own cell outside the table, for example L1. For day to day use, that cell holds =TODAY(). TODAY takes no arguments and shows the current date when the file opens. For the month end report, type a fixed date instead, such as 30/11/2026. Then the list shows the same figures next week too.

  • DueDate: =[@InvoiceDate]+[@Terms]
  • Open: =[@Amount]-[@Paid]
  • Overdue: =IF([@Open]<=0,0,MAX(0,$L$1-[@DueDate])). Paid invoices get 0, and so do invoices that are not yet due.
  • Bucket: =IF([@Open]<=0,"paid",IF([@Overdue]=0,"not due",IF([@Overdue]<=30,"1–30",IF([@Overdue]<=60,"31–60",IF([@Overdue]<=90,"61–90","over 90")))))

If DueDate shows a number like 46341 instead of a date, set it to Date under Home > Number Format. The Overdue column, on the other hand, must be formatted as Number or General, or Excel shows a date there too.

Totals per aging bucket with SUMIFS

A small summary next to the table shows how much money sits in each bucket. SUMIFS takes the range to add first, then the criteria range and the criterion: =SUMIFS(AR[Open],AR[Bucket],"31–60"). The same formula with "1–30", "61–90" and "over 90" gives you your aging report. A second criterion such as AR[Customer] shows the same for a single customer. To make the values stand out, select the Overdue column and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, for example with 30.

Match payments from your bank statement before you chase

This is where the list becomes right or wrong. Export your transactions from online banking, usually as CSV, and set them up as a second table named Bank, with the columns Date, Payer, Amount and Reference. If the file arrives with broken special characters or everything in one column, the free Fix a CSV file tool helps.

If your invoice numbers follow a fixed pattern like RE-2026-0412, twelve characters, a helper column InvoiceNo in the Bank table pulls the number out of the text: =IFERROR(MID([@Reference],SEARCH("RE-",[@Reference]),12),""). SEARCH finds where "RE-" starts, MID cuts out twelve characters from there. If the number is missing, SEARCH returns an error, and IFERROR turns it into an empty cell. Those empty rows are your checklist.

In the AR table, the Paid column then adds up all payments for each invoice: =SUMIFS(Bank[Amount],Bank[InvoiceNo],[@InvoiceNo]). Two part payments are added automatically, and the balance appears in the Open column.

The match is faster with our free Compare Excel files tool. Load the unpaid invoices list as the previous file and the bank list with the helper column as the new file. The tool matches rows by a column with the same heading in both files, here InvoiceNo, and compares every other column with the same heading. If the amount is called Amount in both files and formatted the same way, you see at a glance what matches. Your files stay in your browser.

How to read the result of the match
Status in the toolMeaningWhat you do
Unchangedinvoice and payment with the same amountmark as paid
Changedpayment found, amount differscheck for part payment or discount
Missinginvoice without paymentcandidate for a payment reminder
Newpayment for a number that is not in the listcheck for a double payment or old invoice
Unclearnumber empty or repeatedmatch the payment by hand
A person spreads printed invoices across a table and checks them with a pen, like ticking off open items
Photo: Dimitri Karastelev on Unsplash

Example: €7,605 overdue on paper, €2,780 in reality

Unpaid invoices list in the example with the findings, cut off date 30/11/2026
InvoiceCustomerDue dateAmountPaidOverdueFinding
RE-2026-0412Hotel Lindner16/10/20264,800.000.0045 dayspaid on 24 Nov, invoice number in reference incomplete
RE-2026-0431Bäckerei Hofer29/10/20261,250.001,225.0032 days2% discount valid, write off 25.00
RE-2026-0447Steuerkanzlei Berger11/11/20263,600.001,800.0019 dayspart payment, 1,800.00 open
RE-2026-0458Autohaus Wimmer19/11/20262,150.002,150.000 dayspaid
RE-2026-0466Hotel Lindner26/11/2026980.000.004 daysopen, payment reminder
RE-2026-0479Physiopraxis Sommer04/12/20262,400.000.000 daysnot yet due

After clearing, €2,780 is really overdue, not €7,605: the rest of the part payment and one invoice that has been due for four days. Without the match, Hotel Lindner would have received a late payment letter for €4,800 it had already paid. And Gasthaus Brandner's €640 would have stayed in the account unnoticed, although it has to be refunded or set against a new invoice.

Match first, then chase: payment reminder and late payment letter

A fixed routine protects you from chasing too late or chasing the wrong customer. This order works well, once a week or before every reminder run:

  1. Match transactions up to yesterday

    Match new payments and clear the checklist of unclear payments.

  2. Read the notes

    Instalments, a dispute or a credit note agreed? Do not chase these invoices automatically.

  3. Friendly payment reminder

    For the 1–30 bucket: a short email with invoice number, amount and the invoice attached. Many payments have simply been overlooked.

  4. Late payment letter

    If the reminder gets no response, send a formal letter with a new deadline. Enter the date in RemindedOn.

  5. Next step

    What follows the letter, a collection agency, court proceedings or a conversation, you decide case by case, for larger amounts with your legal adviser.

What you are entitled to when another business pays late is set by law. In Germany, a customer who is not a consumer is in default at the latest 30 days after the due date and receipt of the invoice, even without a reminder (section 286(3) of the German Civil Code, BGB). You can then claim default interest of nine percentage points above the base rate and a flat fee of €40 (section 288(2) and (5) BGB). In Austria, section 456 of the Commercial Code (UGB) sets interest on late payment of money claims at 9.2 percentage points above the base rate where the debtor is responsible for the delay, and under section 458 UGB you can claim a flat €40 for collection costs. Whether you actually charge this to a good customer is a business decision. This section is not legal advice.

Common mistakes that cost money or a good customer

  • Chasing without matching: the most common cause of embarrassing letters. Always match bank transactions up to the previous day first.
  • Only ticking paid: a tick cannot show a part payment. Track amounts, and Excel works out the balance.
  • Not checking discounts: not every deduction is valid. Check the period and the percentage before you write off the rest.
  • Leaving unmatched payments: an unclear payment is either a paid invoice you are about to chase, or money that is not yours.
  • The file travels by email: three versions of the list, and nobody knows which is current. Keep the file in one place everyone can reach.
  • Overwriting formulas: typing an amount into the Paid column by hand replaces the formula. Protect formula columns or shade them grey.

Limits and effort: what the manual work costs

The list in this guide takes an hour to set up. The time goes into keeping it up: exporting transactions, matching payments, clearing exceptions, writing reminders. A conservative assumption is 6 minutes per sales invoice. At 60 invoices a month, that is 6 hours a month or 72 hours a year. At an internal cost of €38 per hour, wages plus employer costs, keeping the list costs around €2,736 a year. That does not include the money that arrives later because reminders sit undone.

Run your own numbers in the office work cost calculator. Excel reaches its limits with combined payments for several invoices, customers who never quote a number, and several bank accounts. Then the formula turns back into manual work, and it stays with the one person who knows how it works.

When an office helper takes over the matching

For an occasional match, the free Compare Excel files tool is enough. It picks a unique column itself, and you can change it. It ignores spaces at the edges by default, and upper and lower case if you choose. Numbers that appear more than once in a file, such as two part payments for the same invoice, are never matched silently but marked as unclear. It supports .xlsx and .csv up to 5 MB and 20,000 rows per file. You download the result as CSV.

If the match runs every week, an office helper, a small program we build into your existing routine, can fetch the bank transactions, match payments by invoice number, amount and customer, and flag part payments and discounts. The result is an up to date unpaid invoices list with suggested payment reminders. You check and approve, and nothing is sent without you. Your accounting software and your online banking stay as they are. More on the Excel automation page.

If you spend an hour ticking off bank statements before every reminder run, find out how much of it can go. In the free workflow check, tell us how many invoices you send a month, which bank the transactions come from and who keeps the list today. You get a personal, free assessment within 1–2 working days. Small projects are delivered at a fixed price from €1,990 excl. VAT, 3–7 days after the use case is agreed.

Sources and further reading

FAQ

Quick answers.

What should an unpaid invoices list include?

At least invoice number, customer, invoice date, due date, amount, amount already paid and the open balance. Days overdue, an aging bucket and a notes column for arrangements help as well. With these columns you see at a glance who owes how much and since when.

How do I calculate days overdue in Excel?

Subtract the due date from the cut off date, for example with =MAX(0,TODAY()-E2) when the due date is in E2. MAX makes sure invoices that are not yet due show 0 instead of a negative number. Format the result cell as Number, or Excel shows a date.

What is an accounts receivable aging report?

It is a list of unpaid customer invoices sorted by how long they have been overdue, usually in brackets such as 1–30, 31–60, 61–90 and over 90 days. In the UK it is often called an aged debtors report. In Excel you build it with a days overdue column and SUMIFS per bracket.

How often should I match the list against the bank account?

At least before every payment reminder and every late payment letter, or you will chase customers who have already paid. When cash is tight, a weekly match is worth it. The shorter the gap, the fewer unclear payments pile up.

Is there a free Excel template to track unpaid invoices?

The table with the ten columns from this guide is your template. You set it up in a few minutes and copy in the formulas for due date, days overdue and aging bucket. If your accounting software already produces a receivables list, export it and match it against the bank statement instead of keeping a second list by hand.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.