Excel's built-in duplicate highlighting only finds identical text. Real duplicates are rarely identical.
Build a normalised invoice reference by stripping spaces, dashes, slashes and leading zeros and forcing upper case. Then use COUNTIFS to flag repeats by supplier and reference, by reference and amount across all suppliers, and by supplier and amount within a date window. Exact-match checks miss most real duplicates, because they differ only by formatting.
Export your invoice register with one row per invoice and put it in columns like this, starting in row 2:
Include at least the last 12 months, not just the current payment run. Many duplicates are re-keyed weeks after the original.
In F2, strip the characters people type differently and force one case:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(C2)," ",""),"-",""),"/",""),".",""))In G2, drop leading zeros from references that are only digits, so 000451 and 451 match:
=IF(ISNUMBER(--F2),TEXT(--F2,"0"),F2)References that contain letters, such as INV0451, are left as they are. Fill both formulas down. If your references are longer than 15 digits, skip G and use F, because Excel can't hold more than 15 significant digits as a number.
Adjust the ranges to your last row. Each test returns TRUE when a row has a possible duplicate.
H: same supplier, same normalised reference. The most common duplicate.
=COUNTIFS($A$2:$A$5000,A2,$G$2:$G$5000,G2)>1I: same reference and amount, any supplier. Catches the same invoice posted to two supplier records.
=COUNTIFS($G$2:$G$5000,G2,$E$2:$E$5000,E2)>1J: same supplier and amount within 30 days. Catches re-keyed invoices under a new reference.
=COUNTIFS($A$2:$A$5000,A2,$E$2:$E$5000,E2,$D$2:$D$5000,">="&D2-30,$D$2:$D$5000,"<="&D2+30)>1K: same supplier, amount within 1%, dates within 7 days. Catches the copy keyed with or without tax, or after a small credit.
=SUMPRODUCT(($A$2:$A$5000=A2)*(ABS($E$2:$E$5000-E2)<=ABS(E2)*0.01)*(ABS($D$2:$D$5000-D2)<=7))>1Filter for any TRUE in columns H to K and sort by supplier and amount, so pairs sit next to each other. Treat H and I as high priority: they are almost always genuine duplicates. Treat J and K as review items: some will be legitimate repeat charges, such as monthly rent.
For every confirmed duplicate that is still unpaid, hold it. For any already paid, follow the duplicate payment audit steps to recover it.
For why these four patterns get past manual review, see how duplicate invoice payments get past review.
The formulas are the same everywhere. Test on the gross amount if some invoices are keyed with VAT or sales tax and others without, or run test K, which tolerates small differences.
Paste your invoice list and see likely duplicates in minutes. Free Excel file: enter your work email to download it.
The Accounts Payable Control System runs four duplicate-invoice tests, supplier master fraud checks and a three-way match on every invoice, with a payment run release gate that blocks release while exceptions are open. Excel, no macros, one-time price.
It looks for identical values in one column. INV-0451 and INV0451 are different text, so they aren't highlighted, even though they're the same invoice.
COUNTIFS handles tens of thousands of rows comfortably. The SUMPRODUCT test is slower; on very large registers, run it on a filtered subset or by supplier.
Before. Stopping a duplicate costs nothing. Recovering one takes time and supplier goodwill.
Last updated 18 October 2026. General guidance for finance teams; check it against your own policies, systems and local rules.