How to Reconcile Invoices and Payments in Excel

You sent 140 invoices last quarter. The bank shows a pile of payments landing in dribs and drabs, some with the invoice number in the reference, some without. Now you need the one answer that matters: which invoices haven't been paid? Scrolling two columns and matching invoice numbers by eye is how you miss the one client who quietly never paid. To reconcile invoices and payments in Excel, you want the mismatches to jump out on their own, not depend on your patience at 9pm.

This walks through a one-shot reconciliation the way a small business actually does it, using a free macro for the matching and Excel's built-in tools where they already fit.

TL;DR: reconcile invoices and payments in Excel

• Line up two columns. Invoice numbers you billed in one, invoice numbers that got paid in the other.
• Run the compare macro. It highlights every invoice number that has no match in the other column.
• Read the two exception types. A highlighted invoice means unpaid. A highlighted payment means an orphan.
• Chase the yellow. The highlighted cells are your whole to-do list, on both sides.

With the macro loaded this takes under a minute. By eye, on 140 rows, it's ten careful minutes and at least one number you'll misread.

What reconciling two lists actually means here

Reconciliation is a matching problem. You have a list of invoice numbers you issued and a list of invoice numbers that were paid, and you want to know where they disagree. It's the same shape whether you're matching invoices to payments, a stock count to your system, or a customer export to your mailing list.

The key idea: you're checking whether each value appears anywhere in the other list, not whether row 5 equals row 5. Payments arrive out of order, so a row-by-row comparison is useless. Presence matching is what you want, and it works no matter how the two lists are sorted.

Step 1: Line up the two lists

Get both sets of invoice numbers into two columns on one sheet. Paste the invoice numbers you billed into one column and the invoice numbers from your paid or bank list into the next. They don't need to be sorted or the same length.

If the two lists live in different places on the sheet, that's fine too. You can hold Ctrl and click to select two separate single columns. Either way, you're feeding the macro exactly two columns to compare.

Step 2: Highlight the unmatched invoices

Select your two columns and run Compare Two Columns. It paints every value that has no match in the other column light yellow, and it reports the counts both ways: how many billed invoices have no payment, and how many payments have no matching invoice.

The matching is forgiving in the right ways. It ignores case and trims stray spaces, so INV-014 matches inv-014 without complaint. New to running macros? The 30-second setup gets you going. When you're done, clear the colors with Home > Clear > Clear Formats.

Step 3: Read the two kinds of exception

The highlights mean two different things depending on which column they're in, and both matter.

• A highlighted invoice number (in your billed column) has no matching payment. That's an unpaid invoice. This is your accounts-receivable chase list.
• A highlighted payment (in your paid column) has no matching invoice. That's an orphan: a typo in the reference, a duplicate payment, a deposit for something else, or money you can't yet attribute.

So one pass gives you both the customers to chase and the payments to investigate. The rest of the rows, the unhighlighted ones, tied out cleanly and need no attention.

Step 4: Flag the gaps and duplicates

Two quick cleanups make the match more trustworthy. If some payment rows are missing an invoice reference entirely, select that column and run Highlight Blank Cells to see how many payments you can't match at all yet.

And if either list has the same invoice number twice, dedupe it first with Remove Duplicates by Multiple Columns. A duplicated invoice number won't break the match, but it inflates your counts and makes the totals confusing.

The formula alternative

If you'd rather have a live column than a highlight, the built-in COUNTIF function does the presence check. In a helper column next to your billed invoices: =IF(COUNTIF(Payments,A2)=0,"UNPAID","paid"), where Payments is your paid column. Fill it down and filter for UNPAID.

The formula wins when you want the status to update as new payments come in. The macro wins for a one-shot monthly check, because the yellow highlight is impossible to scroll past, and a helper column of "paid/UNPAID" tends to get sorted away and forgotten.

The hot take: the unmatched rows are the whole point

It's tempting to feel good about a reconciliation where 95% of the invoices tie out. That 95% is not the point. The reason you reconcile at all is the 5% that don't, the unpaid invoice and the orphan payment, and everything about your process should push those to the front.

That's the case for a visual diff over a formula column. A "paid/UNPAID" column is one sort away from being hidden at the bottom of the sheet. A block of yellow cells is still yellow whatever you sort by. Reconciliation is exception-hunting, so make the exceptions the loudest thing on the page.

Common pitfalls

• Invoice numbers stored as text in one list, numbers in another. 014 and 14 may not match if one is text. Standardize the format before comparing.
• Trusting the match on amounts. This compares invoice numbers for presence, not whether the amounts agree. A paid invoice with the wrong amount still shows as matched. Check amounts separately.
• Leaving duplicates in. A repeated invoice number inflates the unmatched counts. Dedupe each list first.
• Forgetting to clear the highlights. The colors are a normal fill. Clear them with Home > Clear > Clear Formats before you save the file as your record.

Frequently asked questions

How do I reconcile invoices and payments in Excel?

Put your billed invoice numbers in one column and your paid invoice numbers in the next, then run the Compare Two Columns macro. It highlights every invoice number missing from the other list, so highlighted billed invoices are unpaid and highlighted payments are orphans. It also reports both counts, giving you the full exception list in one pass.

How do I find which invoices haven't been paid?

Compare your billed-invoice column against your paid-invoice column. Any billed invoice number with no match in the paid list is unpaid. The macro highlights those cells yellow and counts them, so your accounts-receivable chase list is just the highlighted rows in the billed column. No formulas to write.

Can I compare two lists that are in a different order?

Yes. The comparison checks whether each value appears anywhere in the other column, not row by row, so the two lists can be in any order and any length. Payments almost never arrive in invoice order, which is exactly why a presence match beats lining the rows up side by side.

What if an invoice number has extra spaces or different capitalization?

The macro handles both. It trims leading and trailing spaces and ignores case, so INV-014, inv-014, and Inv-014 are treated as the same value. That covers most of the messiness that comes from copying references out of a bank statement or a payment app.

Do these macros work on Excel for Mac?

Yes. Every linked macro runs on Excel 2016 and later on both Windows and Mac. The comparison and dedupe macros avoid Windows-only features, so they behave the same on either platform.

What to do next

Line up the two columns, run the compare, and let the yellow tell you where to look. It turns a squinting match into a chase list you can act on the same morning.

To reconcile invoices and payments in Excel on your own books, browse the full set of Excel tools for small business owners, or start with Compare Two Columns on this quarter's invoices and payments.