Find duplicate transactions in Excel

To find duplicate transactions in Excel, use COUNTIFS to flag repeated combinations of account, date, reference, signed amount and currency. Keep the original export and investigate every candidate against source records. A match is a review flag, never an instruction to delete or reverse a posting.

Start with the ledger rows and a clear key

Export the relevant ledger period with a stable row identifier, account, posting date, document reference, signed amount and currency. Include company or entity when the export spans more than one entity. Keep debit and credit signs consistent; an offsetting entry is different from a repeated charge.

Use a working copy and record the export's row count and signed total before adding formulas. For a general ledger, distinguish an individual posting line from its journal header: multiple lines with the same journal number can be legitimate. Add the source line identifier or another distinguishing field when the business process requires it.

This example is a US company's transaction extract. Account 1000 is cash; account 2000 is a separate account. References are already clean, and all amounts are USD. Put these fields in A:F, starting with headers in row 1.

Use true date values in C and numeric amounts in E. The worked examples on export rows give the wider principle: every summary must lead back to identifiable records.

Count the full combination

Add Matches in G1. In G2 enter this formula and fill down through G9:

=IF(OR(B2="",C2="",D2="",E2="",F2=""),"CHECK MISSING",
 COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,C2,$D$2:$D$9,D2,
          $E$2:$E$9,E2,$F$2:$F$9,F2))

Microsoft documents COUNTIFS as counting cells that meet multiple criteria. Here every selected field must match. Keep all criteria ranges the same size and extend them together when your export is longer.

Add Review in H1 and enter:

=IF(G2="CHECK MISSING","CHECK MISSING",IF(G2>1,"REVIEW",""))

Filter H for REVIEW. The result is:

There are four flagged rows in two matching groups. L003 has the same amount as L001 but a different date and reference. L007 has a different account. L006 has a different sign and reference. None matches the full combination.

COUNTIFS ignores letter case in text comparisons and interprets wildcard characters in criteria. If references contain literal asterisks, question marks or tildes, escape those characters before using them as criteria, or use an exact comparison method appropriate to your export.

Reconcile the flags before reviewing documents

Use these checks on the working copy:

=COUNTA(A2:A9)
=SUM(E2:E9)
=COUNTIF(H2:H9,"REVIEW")
=SUMIF(H2:H9,"REVIEW",E2:E9)
=SUMIF(H2:H9,"",E2:E9)

They return eight source rows, a signed total of -$2,100, four flagged rows, a flagged signed total of $5,100, and an unflagged signed total of -$7,200. The amounts reconcile: $5,100 + (-$7,200) = -$2,100. Nothing has been removed.

The $5,100 is the signed sum of all flagged rows, including both appearances. It is not a loss estimate. Do not call a net of receipts and payments an amount recoverable from duplicate transactions.

For each group, add reviewer, source record ID, finding and next action. Compare document images, payment records, posting timestamps and journal line identifiers. A repeated import, a legitimate split posting and a recurring transaction can require different conclusions. Keep the review evidence with the reconciliation.

Where it goes wrong

• Removing candidates immediately. Microsoft's RemoveDuplicates documentation describes removing duplicate values. That changes the working population; finish the investigation before any approved correction.
• Matching too few fields. Amount alone groups unrelated activity. Omitting entity or account can also combine valid postings from different books.
• Cleaning away meaning. Resolve accidental spaces consistently, but preserve the original reference. Removing punctuation or leading zeros can merge distinct documents.
• Ignoring timing and signs. Text dates, timestamps and rounded amounts can prevent expected matches. A debit and its credit are not the same posting.
• Treating missing fields as a match. Investigate incomplete records separately. The formula labels them CHECK MISSING instead of grouping empty references into apparent duplicates.

For another spreadsheet workflow, see finding duplicate transactions in Google Sheets. A different row-selection problem, finding dormant customers in Excel, also depends on choosing the correct record grain before counting.

Covirage is not affiliated with Microsoft.

Review your own transaction file in Covirage

Upload a ledger file or Google Sheet with the identifying fields and signed amounts; Covirage maps the columns, and its tools compute and check the figures. The external AI model explains the results with every figure traced to its rows, while your finance team investigates and approves any transaction correction. Create a free Covirage account and bring a sample of your own export; creating an account takes about a minute.

See Covirage for finance teams for the supporting workflow.