Duplicate payments are one of the most common and most recoverable leakages in accounts payable. They rarely come from one dramatic fraud. More often they come from a supplier sending the same invoice twice, a clerk capturing “INV-2231” one day and “INV2231” the next, or a vendor that exists twice in the master file. Whatever the cause, the money has left the organisation, and in many cases the auditor is the first person to notice.
This guide shows you how to detect duplicate payments in Excel using tests you can run today on any payment register, whether you audit a bank, a parastatal, a local authority or an NGO. We will work through a sample dataset, build the formulas step by step, and look at how to separate genuine duplicates from false positives.
What a duplicate payment test finds, and why it matters
A duplicate payment test looks for two or more payments that appear to settle the same obligation. The obvious version is an exact match: same supplier, same invoice number, same amount. The more useful versions look for near matches, because system controls usually block exact duplicates and the ones that slip through are slightly different.
Typical causes include:
- Invoice number formatting: hyphens, slashes, spaces, prefixes or leading zeros entered inconsistently (“0457” and “457”).
- Duplicate vendor records: the same supplier set up under two vendor IDs, so the system sees two different creditors.
- Re-submitted invoices: a supplier chases payment by sending a copy, and the copy is processed as a new invoice.
- Manual payments outside the system: an urgent payment is made by transfer, then the invoice is later paid again through the normal run.
- Multi-currency records: in Zimbabwe, an invoice may be captured once in USD and once in ZiG, which defeats tests that match on amount alone.
Beyond the money, a pattern of duplicates tells you something about the control environment: weak invoice validation, poor vendor master maintenance, or segregation of duties issues. Repeated duplicates involving the same supplier or the same capturer may also be a fraud indicator and deserve a closer look.
Preparing your payment data
Ask for a full payment listing for the period, exported from the accounting system rather than retyped. At minimum you need: payment ID, vendor ID, vendor name, invoice number, invoice date, payment date, amount and currency. Before testing, reconcile the record count and the total amount to the general ledger or bank statements, so you know the population is complete.
Then convert the range to an Excel Table (Ctrl + T) so formulas fill down automatically, and check that dates and amounts are stored as numbers, not text.
Worked example: a sample payment register
The table below is a simplified extract of ten payments. Several duplicates are hidden in it. Try spotting them by eye before reading on.
| Pay ID | Vendor ID | Vendor name | Invoice no. | Invoice date | Payment date | Amount | Currency |
|---|---|---|---|---|---|---|---|
| P1001 | V014 | Zimtrade Supplies | INV-2231 | 02/03/2026 | 10/03/2026 | 4,850.00 | USD |
| P1002 | V022 | Harare Office Solutions | 0457 | 04/03/2026 | 12/03/2026 | 1,200.00 | USD |
| P1003 | V014 | Zimtrade Supplies | INV2231 | 02/03/2026 | 24/03/2026 | 4,850.00 | USD |
| P1004 | V031 | Mutare Fuel Services | FS/889 | 05/03/2026 | 15/03/2026 | 32,400.00 | ZiG |
| P1005 | V031 | Mutare Fuel Services | FS/889 | 05/03/2026 | 15/03/2026 | 32,400.00 | ZiG |
| P1006 | V040 | Bulawayo Print Works | 7781 | 08/03/2026 | 18/03/2026 | 760.00 | USD |
| P1007 | V022 | Harare Office Solutions | 457 | 04/03/2026 | 29/03/2026 | 1,200.00 | USD |
| P1008 | V052 | Chitungwiza Water Works | CW-1120 | 10/03/2026 | 20/03/2026 | 2,310.00 | USD |
| P1009 | V058 | Chitungwiza Water Works | CW1120 | 10/03/2026 | 02/04/2026 | 2,310.00 | USD |
| P1010 | V040 | Bulawayo Print Works | 7790 | 20/03/2026 | 28/03/2026 | 760.00 | USD |
Assume the data sits in columns A to H, with headers in row 1 and data from row 2.
Step-by-step Excel tests
Test 1: Exact duplicates
In column I, label the header Exact_Dup and enter:
=COUNTIFS($B:$B,B2,$D:$D,D2,$G:$G,G2,$H:$H,H2)
Any result greater than 1 means the same vendor, invoice number, amount and currency appear more than once. In our sample, only P1004 and P1005 (Mutare Fuel Services) are flagged. That is the easy one.
Test 2: Normalised invoice numbers
Create a clean invoice key in column J that strips prefixes, punctuation, spaces and leading zeros. In Excel 365 or 2021:
=LET(k,UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(D2),”INV”,””),”-“,””),”/”,””),” “,””)),IFERROR(TEXT(VALUE(k),”0″),k))
If your version of Excel does not support LET, put the SUBSTITUTE part in one helper column and the IFERROR(TEXT(VALUE(…))) part in the next. Adjust the prefixes you remove to match what you see in the data.
Now in column K (Norm_Dup):
=COUNTIFS($B:$B,B2,$J:$J,J2,$G:$G,G2)
This catches P1001 and P1003 (INV-2231 versus INV2231) and P1002 and P1007 (0457 versus 457), which the exact test missed.
Test 3: Same invoice, different vendor ID
Drop the vendor ID from the match so that duplicate vendor records cannot hide a duplicate. In column L (CrossVendor_Dup):
=COUNTIFS($J:$J,J2,$G:$G,G2)
This flags P1008 and P1009: the same Chitungwiza Water Works invoice paid under vendor IDs V052 and V058. Finding this should also trigger a review of the vendor master file.
Test 4: Same vendor, same amount, close dates
Some duplicates carry a completely different invoice number. In column M (Amount_Window), look for the same vendor and amount paid within 30 days:
=COUNTIFS($B:$B,B2,$G:$G,G2,$F:$F,”>=”&F2-30,$F:$F,”<=”&F2+30)
This flags P1006 and P1010 at Bulawayo Print Works. These may be legitimate repeat orders, so this test produces leads to investigate, not conclusions.
Highlight and summarise the results
- Select columns I, K, L and M, then go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter 1 and choose a fill colour.
- Add a filter and show only rows where any test returns more than 1.
- Insert a PivotTable with Vendor name in Rows, Invoice key in Rows beneath it, and Sum of Amount and Count of Pay ID in Values. This gives you a clean schedule of potential duplicates and the value at risk per supplier.
Handling false positives and multi-currency records
Not every flag is an error. Before you raise a finding, check each exception against source documents:
- Instalments and retentions: one invoice may legitimately be paid in parts.
- Recurring charges: rent, subscriptions and service contracts often carry the same amount every month.
- Reversals: a payment may have been cancelled and reissued. Look for a matching negative entry or bank return.
- Currency: where payments are recorded in both USD and ZiG, run Test 3 on the invoice key alone, without the amount, and review any invoice paid in more than one currency.
For confirmed duplicates, record the amount, the supplier, the root cause and whether recovery is under way. If your team tracks findings and recommendations in Auditors Track GRC, you can attach the exception schedule as evidence and follow each recovery through to closure.
Common mistakes to avoid
- Testing only the exact match and concluding there are no duplicates.
- Skipping the completeness reconciliation, so the tested population is missing a month or a bank account.
- Treating every flag as a finding without checking for credit notes, reversals or instalments.
- Ignoring the root cause. A recovered payment fixes one loss; a fixed vendor master or invoice validation control prevents the next one.
Conclusion
Detecting duplicate payments in Excel needs nothing more than a clean extract, a normalised invoice key and a handful of COUNTIFS formulas. Run the four tests in order, from exact matches to near matches, investigate each exception against source documents, and report both the recoverable amount and the control weakness behind it. Make it a routine test on every accounts payable review and it will pay for itself many times over.
Excel works well for a few thousand payments. When your payment history runs into hundreds of thousands or millions of rows, formulas such as COUNTIFS slow down sharply and workbooks become hard to manage. ProDataTool is a desktop data analysis application built for auditors, risk analysts and accountants. It handles datasets from thousands to millions of rows, with project-based organisation, descriptive statistics, cross-tabulation, pivot analysis, summarisation and custom filtering, so you can run tests like these across full-year data in one place. Book a free demo of ProDataTool and see it on your own data.