Find duplicate transactions in Excel before they reach your books

A bank export or ledger download sometimes lists the same transaction twice. Nothing about the copy says "extra". This page explains how to decide what counts as a duplicate, how to flag the extra copies with COUNTIF and COUNTIFS, and the mistakes that let duplicates slip through, with three worked examples.

Why duplicate transactions are dangerous

Duplicates usually come from the import, not the business: a bank feed downloaded twice for overlapping dates, or the same week pasted in by two people. The transaction happened once but appears twice.

If the copy stays in the data, every total built on it is wrong:

• Expenses are double-counted. A courier payment entered twice makes shipping costs look higher than they were.
• Reconciliations stop working. To reconcile is to check that your records agree with the bank statement. An extra deposit makes your cash balance higher than the bank's, and you hunt for a difference that is really a copied row.
• Payments can go out twice. In an accounts payable list, a duplicated bill can be paid twice.

Removing duplicates is one of the clean-up tasks listed for the MO-220 exam [Official]. The exam's task instructions describe the purpose instead of naming the function [Official], so you need to recognize "find the transactions that were exported twice" as a counting job.

First decide what makes two rows the same

Before writing any formula, decide which columns define a duplicate. This is the key: the value, or combination of values, that should be unique for every real transaction.

A unique reference is the best key. Most bank exports give each transaction a reference number. If the same reference appears twice, that transaction was exported twice.

Without a reference, combine columns. If the export has no reference, a sensible key is date, description and amount together. Two rows that match on all three are very likely the same transaction.

Amount alone is not a key. Real transactions repeat amounts all the time: a monthly subscription, rent, two customers paying the same invoice value. These are near duplicates: rows that look alike but are separate, real transactions. Flagging them would remove genuine expenses and income from your records, which is as harmful as leaving a true duplicate in.

Count how many times each reference appears

The simplest test counts each row's key across the whole list. COUNTIF takes a range and a value, and returns how many cells in the range match that value. With references in D5:D20, the formula in E5 is:

=COUNTIF($D$5:$D$20,D5)

Fill it down to E20. The range is locked with dollar signs so every row searches the same full column. The second argument, D5, is relative, so each row counts its own reference. A result of 1 means the reference is unique. A result of 2 or more means the transaction appears more than once.

This count marks every copy, including the original. Deleting every row with a count of 2 would lose the real transaction too.

Keep the first copy with a growing range

To flag only the extra copies, count each reference from the top of the list down to the current row. The range starts at a fixed cell and ends on the current row:

=IF(COUNTIF($D$5:D5,D5)>1,"Duplicate","Keep")

Only the start, $D$5, is locked. When you fill the formula down, D5 at the end of the range becomes D6, D7, and so on, so the range grows one row at a time. The first time a reference appears, the count up to that row is 1, so the row is kept. The second time, the count is 2, so the row is flagged. Every original is kept and every extra copy is marked.

Match on several columns with COUNTIFS

When there is no reference, COUNTIFS does the same job across several columns. It takes pairs of range and value, and counts only the rows where every pair matches. With dates in column A, descriptions in B and amounts in C:

=IF(COUNTIFS($A$5:A5,A5,$B$5:B5,B5,$C$5:C5,C5)>1,"Duplicate","Keep")

Each range grows in the same way, and all three conditions must hold at once. A monthly charge with the same description and amount but a different date fails the date test, so it is kept.

Total the clean list with SUMIFS

Once every row is marked, SUMIFS adds only the rows you want. It takes the range to add, then pairs of condition range and condition:

=SUMIFS(C5:C20,E5:E20,"Keep")

To report how many copies you found, count the flags: =COUNTIF(E5:E20,"Duplicate").

Worked example 1: count each reference

Copperleaf Studio's September bank export has 16 rows with a bank reference in column D. Here are the rows that matter, with the count from =COUNTIF($D$5:$D$20,D5) filled down:

Three references appear twice, so three transactions were exported twice. The two customer deposits of 1,012.26 are different: they have different dates and different references. They are a near duplicate, and their count is correctly 1 each.

Worked example 2: flag the extra copies

Pine Street Dental's January export uses the growing-range formula =IF(COUNTIF($D$5:D5,D5)>1,"Duplicate","Keep"):

Row 6 is the first appearance of reference 101212, so the count up to row 6 is 1 and the row is kept. In row 7 the range $D$5:D7 holds that reference twice, so row 7 is flagged. The CloudDesk charge of 776.79 on 1/1/2026 and the one on 1/31/2026 have different references: that is a monthly subscription, not a duplicate. Only the copy in row 20 is flagged.

Worked example 3: no reference column

Juniper Landscaping's August export has only date, description and amount. The COUNTIFS formula marks a row only when all three match a row above it:

The two bank service charges match on description and amount but not on date, so both are kept. The 16 amounts add up to 42,317.49. The two flagged copies come to 1,444.02 + 4,584.45 = 6,028.47. So =SUMIFS(C5:C20,E5:E20,"Keep") in H4 returns 42,317.49 − 6,028.47 = 36,289.02, and =COUNTIF(E5:E20,"Duplicate") in H5 returns 2.

Remove Duplicates, UNIQUE, and why flagging comes first

Excel has two other tools for this job. The Remove Duplicates command on the Data tab deletes repeated rows in place, based on the columns you tick. The UNIQUE function returns a list with each distinct row once, in a new location. Both are useful for MO-220 practice.

For accounting data, flagging first is safer, for two reasons:

• You can review before you delete. Remove Duplicates acts immediately; tick only the amount column and it deletes every near duplicate too. A flag column lets you confirm each marked row really is a copy.
• You keep an audit trail. An audit trail is the record of how a number was produced. A flag column and a clean total show a reviewer exactly what you removed and why.

Once the flags are reviewed, you can filter on "Duplicate" and delete those rows, or simply build every report on the "Keep" rows.

Common mistakes

Leaving the count range unlocked

Writing =COUNTIF(D5:D20,D5) without dollar signs makes the range slide down as you fill. By row 6 it covers D6:D21 and no longer sees row 5. In the first example, row 6 would show 1 instead of 2, hiding the copy. Lock the range as $D$5:$D$20.

Counting amounts instead of references

Counting column C instead of column D finds repeated amounts, not repeated transactions. In the first example, the two customer deposits of 1,012.26 would both show 2, even though they are separate deposits on different dates.

Flagging the first copy too

Using the full range $D$5:$D$20 inside the IF marks both copies as Duplicate. In the second example, rows 6 and 7 would both be flagged, and removing them would delete the real City Power payment along with the copy. Use the growing range so the first appearance is always kept.

Not locking the start of the growing range

Writing D5:D5 instead of $D$5:D5 makes the start move with the end, so the range only ever covers the current row. The count is always 1 and nothing is flagged. Lock the start, leave the end free.

Matching on amount only

Without a reference, it is tempting to count matching amounts. In the third example, that flags the 8/16/2026 bank service charge as a duplicate of the 8/8/2026 one, removing a real charge from the books. Match on date, description and amount together.

Totals that still include the duplicates

Flagging the rows is only half the job. If the total is =SUM(C5:C20), it still returns 42,317.49 in the third example, duplicates included. Use SUMIFS with the "Keep" condition to get 36,289.02.