How to Track Unpaid Invoices in Excel

Money owed to you has a way of going quiet. A bill you sent sits in a client's pile; a bill you owe sits in yours. If you track unpaid invoices in Excel — one live list, kept true — nothing owed or owing can go quiet for long. This guide builds that list.

This list is for the bills you must pay, and it works the same for the bills you send and now await pay on — flip the names and the columns hold. Most small firms need the pay side most: a true list of what is owed, to whom, and by when is the core of cash sense.

The list earns its keep the day it stops a late fee, a double pay, or a cash crunch you saw too late. And it feeds the next step: a list that knows what is late is a list that tells you whom to nudge, and when. That chase craft is its own guide — how to follow up on a late payment — this one builds the list the chase runs on.

One sheet, eight columns, one weekly rite. That is the whole system. Here is how each part works.

The columns that matter

A tracker fails in one of two ways: too few columns, so it can not answer; or too many, so no one keeps it up. Eight is the count that holds for most firms. Each column earns its place by a question it answers at a glance.

Two of those columns do the heavy work. The due date is the one most lists lack — they track what landed, not when it falls due — and it is the one that turns a list into a plan. Days late, built from the due date by a plain formula, is the alarm bell: a list sorted by days late is a work queue, worst first.

Key the list by the bill number. One row per bill, named by firm and number. That key is what makes the twin check a sort — and it is why the number field gets its own care, in our guide on why invoice numbers matter. A tracker keyed by number can not hold the same bill twice with no one the wiser.

Build it from the bills, not from memory

A tracker is true if it is built from the bills themselves. Each bill that lands gets a row the week it lands, keyed from the page: firm, number, dates, total. A row typed from a memory or a bank line will drift from the bill's facts, and the drift shows up at the worst time — the pay run.

The fast path is to convert the bill and lift the row from the result: the converter gives you firm, number, dates, and sums in a row shape, and you paste or type the row into the tracker once, true. For a month's pile at once, the batch path builds the same rows in bulk — sort the pile first, then run it, per the craft in the batch guide.

Set the due date at the same moment, from the terms. If the bill prints a due date, use it. If it prints terms — net 30 — count from the bill date and write the date, not the terms. A tracker full of “net 30” is a tracker that makes you do math at each glance. Dates you can sort; terms you can not.

Mark part-pays in the row, not in a new row. A bill half paid is one bill with a status and a note: “half paid May 20, rest due June 2.” Split it into two rows and the twin check breaks, the count of bills breaks, and the firm's run of numbers grows a hole that is not real.

The aging view: what is late, at a glance

Once a week, sort the open rows by days late. The list now reads as a queue: the most late at the top, the due-this-week block in the midst, the fresh bills at the base. Bookkeepers call this an aging view, and it is the one view of your debts that tells the truth in ten seconds.

Color helps, if you keep it plain: no color for not-yet-due, amber for due this week, red for late. Three colors, no more. A sheet lit up like a fair teaches the eye to skip the lights. A sheet with three states makes the red rows shout, as they should.

Sum each bucket. The sums are your cash truth for the month: what must go out this week, what is stuck, what is at risk of a fee. A owner who reads those three sums each week does not get caught by a crunch that was on the sheet, in red, for a month.

The weekly rite that keeps it true

A tracker is a promise to keep it true, and the promise is kept weekly. Pick a slot — the same slot as your pay run is best — and run four steps: add new bills, mark the paid ones paid with their pay dates, read the aging view, and act on the red rows. Fifteen minutes for most firms.

Mark paid from the bank, not from your plan. A bill you meant to pay and a bill you paid are two states, and the tracker must hold the true one. When the pay run is done, match each pay to its bank line, then flip the rows. A row flipped on a hope will flip back as a late fee.

Keep paid rows in the sheet, marked paid, do not delete them. The paid rows are your history: they prove what was paid, when, and they are the list the twin check runs on. A tracker that holds just the open bills can not tell you if the bill in hand was paid last month. One sheet, open and paid, is the whole tale.

Feed the sheet from the month's batch when the pile is big. Our guide on how to convert a month of invoices at once ends with rows checked by sample — those rows are the tracker's new lines, keyed and true, with no re-typing in between.

When a bill is stuck: holds and part-pays

Not all open bills are the same kind of open. A bill under query — wrong sum, goods not in — is not late in the same way as a bill you just have not paid. Give it a status of its own: on hold, with the note saying why and since when. A hold with a note is a task. A late bill with no note is a risk.

Set a rule for holds: a hold older than two weeks gets a call, and the note grows a line with what the firm said. Holds rot in the dark. The aging view should sort holds by the date the hold began, not the due date — a hold's clock runs from the query, and a firm that has not fixed a bill in a month needs a new kind of talk.

Part-pays get the same light. Note what was paid, when, and what is left, with the date the rest falls due. A firm that took half now and half in 30 days has given you terms — write them in the row, so the rest does not drift into the late bucket by a clerical slip and earn a fee it should not.

And when a bill is paid off at last after a hold or a part-pay, let the note keep the tale in one line. Those lines, read at year end, are a map of which firms bill clean and which bill rough — a map worth real money at the next price talk.

What the list gives back

A true tracker pays rent in four ways. It stops late fees, since no due date can sneak past a sorted list. It stops twin pays, since each bill has one row by number. It smooths cash, since next week's outgo is a sum you can read today. And it ends the “did we pay that?” talk, which in most small firms eats more time than the pays do.

It also gives you a stance with vendors. A firm that can say “your bill 1042, due June 2, is in this week's run” is a firm vendors trust and serve first. Vague firms get vague service. A true list is a credit rating you build in the open, one kept date at a time.

If your bill count grows past what a sheet holds with grace — rows in the high hundreds, many hands in the file at once — that is the sign to move the list into your bookkeeping tool and keep the sheet as the check. The columns and the rite move with you. The tool changes; the craft does not.

Your next step

Start the sheet today with the bills now open on your desk. Eight columns, one row per bill, due dates set from the terms. Sort by days late and read your queue — the first read is the one that finds the bill you forgot.

Then set the weekly slot next to the pay run, and guard it. A tracker kept true for a month becomes the desk's source of truth; a tracker left for a month becomes one more stale file. If the sums you owe strain the month, the plans on the pricing page cover the page counts for teams that run bills in bulk — the rows still land in the same sheet.

What you owe, to whom, by when — in one list that is never more than a week stale. That is the whole build. It fits on one screen, and it pays for its keep the first time it turns a red row into a kept date.

Sources and further reading

• Manage your business — The US Small Business Administration on managing finances: accounts payable and cash are two of the core jobs a bookkeeper — or a good sheet — must stay on top of.
• What kind of records should I keep — The IRS on gross receipts and purchases: the bills and invoices behind your sums are the supporting documents your records stand on.

← All guides