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 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.
- 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.
- 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.
- Match payments
Export your bank transactions, pull the invoice number out of the payment reference and add up the payments per invoice.
- Clear the exceptions
Check payments with no recognisable invoice, part payments and early payment discounts by hand. This is where the costly mistakes happen.
- 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.
| Column | Content | Source |
|---|---|---|
| InvoiceNo | e.g. RE-2026-0412, unique | from the invoice |
| Customer | name, ideally with customer number as well | from the invoice |
| InvoiceDate | date of the invoice | from the invoice |
| Terms | days, e.g. 14 or 30 | from invoice or contract |
| DueDate | invoice date plus terms | formula |
| Amount | gross invoice amount | from the invoice |
| Paid | sum of all matched payments | formula from the bank statement |
| Open | amount minus paid | formula |
| Overdue | days since due date, 0 if not due | formula |
| Bucket | not due, 1–30, 31–60, 61–90, over 90 | formula |
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.
| Status in the tool | Meaning | What you do |
|---|---|---|
| Unchanged | invoice and payment with the same amount | mark as paid |
| Changed | payment found, amount differs | check for part payment or discount |
| Missing | invoice without payment | candidate for a payment reminder |
| New | payment for a number that is not in the list | check for a double payment or old invoice |
| Unclear | number empty or repeated | match the payment by hand |

Example: €7,605 overdue on paper, €2,780 in reality
| Invoice | Customer | Due date | Amount | Paid | Overdue | Finding |
|---|---|---|---|---|---|---|
| RE-2026-0412 | Hotel Lindner | 16/10/2026 | 4,800.00 | 0.00 | 45 days | paid on 24 Nov, invoice number in reference incomplete |
| RE-2026-0431 | Bäckerei Hofer | 29/10/2026 | 1,250.00 | 1,225.00 | 32 days | 2% discount valid, write off 25.00 |
| RE-2026-0447 | Steuerkanzlei Berger | 11/11/2026 | 3,600.00 | 1,800.00 | 19 days | part payment, 1,800.00 open |
| RE-2026-0458 | Autohaus Wimmer | 19/11/2026 | 2,150.00 | 2,150.00 | 0 days | paid |
| RE-2026-0466 | Hotel Lindner | 26/11/2026 | 980.00 | 0.00 | 4 days | open, payment reminder |
| RE-2026-0479 | Physiopraxis Sommer | 04/12/2026 | 2,400.00 | 0.00 | 0 days | not 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:
- Match transactions up to yesterday
Match new payments and clear the checklist of unclear payments.
- Read the notes
Instalments, a dispute or a credit note agreed? Do not chase these invoices automatically.
- 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.
- Late payment letter
If the reminder gets no response, send a formal letter with a new deadline. Enter the date in RemindedOn.
- 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.
