500 free credits, no card. Try Smalt AI →

Operations

How do I find duplicate invoices in a list of payments in Excel

Add a COUNTIFS helper column on invoice number and amount, filter for results above 1, then review each flagged pair before you delete anything.

Smalt AI team ·

Add a helper column that counts how many rows share the same invoice number and amount, using a formula like =COUNTIFS(A:A,A2,B:B,B2). Filter that column for anything above 1, and you have every candidate duplicate. Review the flagged rows by hand before you delete or reverse anything.

Start with highlighting, not deleting

Microsoft’s guidance on finding and removing duplicates is to review first and delete second. For a quick look, select the invoice number column, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every repeated value turns a colour you choose.

That view has a limit for payments. The same invoice number can legitimately appear twice, for example when one invoice is paid in two instalments. A repeated number on its own is a lead, not proof.

Flag rows that match on more than one column

A real duplicate payment usually matches on several fields at once. Suppose your list has the invoice number in column A, and the amount in column B. In C2, enter:

=COUNTIFS(A:A,A2,B:B,B2)

Fill it down. Any row that returns 2 or more shares its invoice number and amount with another row. This is an application of the syntax described in Microsoft’s COUNTIFS documentation: each range and criterion form a pair, the ranges must be the same size, and a row is counted only when every pair matches. It is a formula to try on your own data, not a recipe Microsoft publishes for invoices.

To see only the suspects, turn on a filter (Data > Filter) and show values greater than 1 in column C. Sort by invoice number so each pair sits together. Add the payment date to the criteria if you want to separate a true double payment from an instalment.

Catch near-duplicates with a cleaned key

Exact matching misses “INV-001” against “INV001”, or a number with a trailing space. A common workaround is a second helper column that standardises the text before you count:

=UPPER(TRIM(SUBSTITUTE(A2,"-","")))

Point your COUNTIFS at this cleaned column instead of the raw one. Treat this as general practice rather than an Excel feature: you decide which characters to strip, and over-cleaning can merge invoices that really are different.

Use Remove Duplicates only on a copy

Data > Remove Duplicates is permanent. Microsoft’s page on filtering for or removing duplicate values says to back up first and to remove any outlines or subtotals beforehand. It also warns that if you leave a column such as amount or date out of the selection, rows that differ only in that column are deleted.

If you want a non-destructive version, use Data > Advanced and tick “Unique records only”. That filter can copy the unique rows to another location and leaves your original list intact.

For payment checking, flagging usually beats deleting. The payment list is a record of money that left the account. You are looking for rows to investigate, and removing them from the list does nothing about the payment itself.

Doing it with an AI coworker

If the invoices are not already in a list, you can upload them to Smalt as a .zip and ask your AI coworker for one workbook. For a reconciliation, ask it to reconcile last month’s invoices against your PO sheet: you get one workbook of matches, with each exception linked to the invoice it came from.

You still decide what counts as a duplicate, and a person should read each flagged pair before anyone acts on it.

What to check before you rely on it

Excel judges duplicates on what a cell displays, not what it stores. Microsoft’s page on duplicate values notes that “12/8/2017” and “Dec 8, 2017” count as different, and so do the same number shown in two formats. Normalise dates and amounts before you compare. The same page says reported counts can include blank cells or cells containing only spaces.

Check each flagged pair against the source documents. Two rows with the same invoice number and amount might be a corrected resubmission, a credit note and its original, or a recurring charge. Only the invoice, the bank record or the supplier can settle that.

Also check what the formula cannot see. COUNTIFS only finds rows that match the way you told it to. A duplicate that was re-keyed with a different invoice number, or the same bill paid against two different suppliers’ records, will pass straight through. If that is a real risk for you, add a second check on amount plus supplier plus date.

A city auditor’s continuous monitoring review of duplicate payments (City of Richmond, Virginia, May 2026) shows why. Its tests looked for invoices with different numbers but identical amounts, dates and vendor names, and it found 50 duplicate payments in the selected FY2024 and FY2025 transactions it analysed. The report notes that more duplicates may exist that its criteria did not catch, and the same caution applies to your formula.

For a sense of scale, a CFO.com summary of APQC benchmarking reported that top performers see about 0.8% of annual disbursements as duplicate or erroneous, against about 2% for bottom performers. Treat that as indicative only. It dates from March 2020, it combines duplicate and erroneous payments into one figure, and the methodology is not described, so it is not a target for your own list.

Smalt is an AI coworker that takes jobs like this end to end and hands back the finished file.