Most duplicate invoices are not exact copies. The invoice number is the same, but typed differently.
Make a helper column that converts every invoice number to one standard form: upper case, no spaces, no dashes, slashes or full stops, and no leading zeros. Run duplicate tests on the helper column, not the original. INV-0012, inv 12 and 12 then all become the same key.
An exact-match test only flags invoice numbers that are identical character for character. In practice the same invoice gets entered as INV-0012 from a PDF, inv 12 from an email and 12 from a scanned copy. All three are one bill, and an exact test sees three different numbers. Cleaning the number first is the single biggest improvement you can make to a duplicate check.
With the original invoice number in column B, put this in a helper column:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2)," ",""),"-",""),"/",""),".",""))
TRIM removes spaces at the start and end, each SUBSTITUTE removes one character, and UPPER makes the result case-insensitive. Add another SUBSTITUTE for any other character your suppliers use, such as underscores or the # sign.
Some suppliers put letters in front of the number (INV, SI, TAX). If your team sometimes types the prefix and sometimes doesn't, remove the common ones as well:
=SUBSTITUTE(SUBSTITUTE(C2,"INV",""),"SI","")
Be careful: removing letters can make genuinely different invoices look the same. Only strip prefixes you have seen cause problems, and keep the supplier in every test so two suppliers' "1001" never match each other.
Leading zeros are the most common difference. In Excel 365 this formula removes them from a text value while keeping any letters later in the number:
=LET(t,D2,n,LEN(t),i,XMATCH(TRUE,MID(t,SEQUENCE(n),1)<>"0"),IF(ISNA(i),"0",MID(t,i,n)))
In older Excel, if the cleaned value is purely digits, =IFERROR(TEXT(VALUE(D2),"0"),D2) does the same job. Do not use VALUE on mixed letters and digits; it returns an error, which the IFERROR then leaves unchanged.
Combine supplier and cleaned number into one key:
=A2&"|"&E2
Then count how many times each key appears:
=COUNTIF($F$2:$F$5000,F2)
Any count above 1 is a possible duplicate. Sort by the key so the pairs sit next to each other for review.
Once the key works, run the wider set of tests in how to find duplicate invoices in Excel, which also compares amount and date.
The method is the same in the UK and US. UK suppliers often number VAT invoices with a prefix; US suppliers more often use plain numbers or purchase order references. Check what your own suppliers do before choosing which prefixes to strip.
Supplier statement to AP ledger. Finds invoices you haven't posted, credit notes still to come, price differences, duplicate postings and payments in transit, and builds the supplier reconciliation. Runs in your browser on your own computer: nothing is uploaded.
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.
Remove Duplicates deletes rows that are exact copies, so it misses the formatting differences that cause most duplicate payments, and it removes evidence you need to investigate.
No. This cleans the invoice number. Run a separate test on supplier, amount and date to catch duplicates where the number itself is different.
TRIM, SUBSTITUTE, UPPER, COUNTIF and VALUE work the same way. LET, XMATCH and SEQUENCE also exist in current Google Sheets, but check the result on a sample first.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.