Remove Duplicate Bank Transactions in Excel Safely

TL;DR Don't use Excel's Remove Duplicates button on bank data straight away. Bank statements contain genuine repeats, like two identical coffees on the same day, and a blind removal deletes real money. Instead, find out where the duplicates came from (usually overlapping exports or the same statement added twice), flag candidates with a key built from date, amount and description plus the source file, review them, and only then remove. Afterwards, confirm each statement period still balances: opening balance plus activity equals closing balance. If it does, you removed duplicates and nothing else.

Duplicate transactions are one of the most common problems in bank data spreadsheets. They creep in when you combine a CSV download with a converted PDF statement, append the same month twice, merge files from two people, or export overlapping date ranges. The result is inflated spending, wrong category totals, and reconciliations that never balance.

The obvious fix, select everything and click Remove Duplicates, is also the most dangerous. This guide explains why, then shows a safe approach: understand the source, flag candidates, review, remove, and verify. Everything here works in Excel 365, with notes on older versions and Google Sheets.

Why "Remove Duplicates" is risky on bank data

Excel's Remove Duplicates feature deletes every row that matches an earlier row in the columns you select. For a mailing list, that's exactly right. For bank transactions, identical rows can be perfectly legitimate:

• Two lunches at the same café on the same day for the same price.
• Two transfers of the same amount to savings on the same date.
• Several identical subscription charges for different team members.
• Two parking payments, two bus fares, two identical refunds.

On most statements these lines look exactly the same: same date, same description, same amount. Remove Duplicates can't tell a real repeat from an accidental copy. Delete one and your spreadsheet no longer agrees with the bank.

The fix is to use information that distinguishes a real repeat from an accidental copy. That information usually isn't in the transaction itself. It's in where the row came from.

Step 1: find out where duplicates come from

Duplicates nearly always have a cause you can identify:

• Overlapping date ranges. You downloaded 1 March to 31 March, then 15 March to 15 April. Half of March is in there twice.
• Same statement added twice. The March PDF was converted and appended, then converted and appended again, perhaps under a different filename.
• Download plus statement. You combined a CSV download covering recent weeks with a converted statement covering the same period.
• Pending then posted. A download taken mid-month included pending transactions; a later download included the same items once posted, sometimes with slightly different descriptions or dates.
• Transfers between your own accounts. Not duplicates at all, but they look like it: a -500 on checking and a +500 on savings. They need flagging as transfers, not deleting.

Knowing the cause tells you how to fix it. Overlaps are best fixed by trimming the overlap; a statement added twice is best fixed by removing the whole second copy; pending-versus-posted needs case-by-case review.

Step 2: add a Source column if you don't have one

Every row should record where it came from: the statement period and account, or the download filename and date range. For example checking 2026-03 statement or checking download 2026-03-15 to 2026-04-15.

If you combine files with Power Query, the folder import adds the filename automatically. If you paste by hand, fill the Source column as you paste. This one column makes duplicate hunting far easier, because a genuine repeat comes from the same source, while an accidental copy comes from two different sources, or from the same source pasted twice.

Our guide to combining multiple bank statements in Excel shows how to set this up with Power Query.

Step 3: check statements balance on their own

Before hunting for duplicates, prove each statement is complete and contains no duplicates internally:

=ROUND(Opening + SUMIFS(Tx[Amount], Tx[Source], "checking 2026-03 statement") - Closing, 2)

A statement that balances has no extra rows within it, however many identical-looking lines it contains. Every one of those "duplicate" coffees is real. That's the key insight: the balance check is the referee. The free balance checker does it without formulas.

If a single statement doesn't balance by an amount equal to one of its transactions, that transaction was probably repeated during conversion or pasting. If you're converting PDFs, a converter with a built-in balance check catches this before you ever see the data. SheetStatement checks every statement before export. The free plan covers 3 pages a month; Starter ($9), Pro ($19) and Business ($39) plans cover larger volumes.

Step 4: build a match key

Create a key from the fields that identify a transaction:

=TEXT([@Date],"yyyy-mm-dd") & "|" & TEXT([@Amount],"0.00") & "|" & TRIM(UPPER([@Description]))

TRIM and UPPER remove differences in spacing and capitalization, which vary between downloads and statements. Rounding through TEXT(..., "0.00") avoids invisible decimal differences.

Then count how many times each key appears overall, and how many distinct sources it appears in:

=COUNTIF([Key], [@Key])

=COUNTIFS([Key], [@Key], [Source], [@Source])

Now interpret:

This is far safer than Remove Duplicates, because it uses the Source column to separate real repeats from overlaps.

Step 5: handle overlapping sources properly

When two sources overlap, the cleanest fix is usually to decide which one is authoritative for each date range, then drop the other source's rows for that range. For example:

• Statements are authoritative for every closed month.
• Downloads only fill in the current, unfinished month.

In practice: filter the download rows to dates after the last statement's closing date, and delete the rest. That removes the overlap in one move, without row-by-row decisions.

=AND(LEFT([@Source],8)="download", [@Date]<=LastStatementEnd)

Rows where this returns TRUE are download rows already covered by a statement. Filter and delete them.

Step 6: deal with near-duplicates

Downloads and statements don't always describe the same transaction identically. A download might say AMZN Mktp US*2K4 while the statement says AMAZON MARKETPLACE. Pending transactions might carry the authorization date, while the posted version carries the posting date a day or two later. Exact keys miss these.

Find near-duplicates on amount within a date window, from different sources:

=COUNTIFS([Amount], [@Amount], [Date], ">="&[@Date]-3, [Date], "<="&[@Date]+3, [Source], "<>"&[@Source])

A non-zero result means a transaction with the same amount appears within three days in another source. Review these by eye. Most will be the same transaction described differently. If you've already applied Step 5's rule (statements win for closed months), most near-duplicates disappear with it.

Step 7: review, then remove

Never delete straight from the formula results. Instead:

1. Add a Decision column with values like Keep, Remove and Transfer.
2. Filter to candidates (total count above 1, or near-duplicate flags) and fill in the decision.
3. Copy the workbook or keep the original data on another sheet before deleting anything.
4. Filter to Remove and delete those rows.

If you prefer to keep everything, filter Remove out of your reports instead of deleting. A pivot table with a filter on Decision does this cleanly.

Step 8: verify

After removing, check again:

• Each statement still balances. If removing rows broke a statement's balance, you removed real transactions. Undo and review.
• Each month's total matches the statements. Compare the sum of activity per month with the statement's summary totals.
• No overlap remains. For each account, the dates covered by each source shouldn't overlap.

If everything balances, you removed duplicates and nothing else.

Using UNIQUE and Remove Duplicates when it is safe

There are cases where blind de-duplication is fine:

• Lists of payees or descriptions, for building a category rules table. =UNIQUE(Tx[Description]) gives you a clean list to categorize, and our guide to Excel formulas to categorize transactions shows what to do with it.
• A transaction ID column. If your data includes a unique transaction ID from the bank or a platform, as many downloads and payment platform reports do, then rows with the same ID really are duplicates. Remove Duplicates on the ID column alone is safe.
• Combined data where you include the Source and a line number in the comparison, so real repeats are protected.

Without one of these, the key-and-source method above is the safe route.

Preventing duplicates in the first place

Cleaning up is fine, but a workbook that never collects duplicates is better. A few habits do most of the work.

Keep an import log. A small sheet with one row per file you've added: account, source type, first date, last date, number of rows, and the date you added it. Before adding a new file, check the log for overlapping dates. A formula can flag it:

=COUNTIFS(Log[Account], [@Account], Log[First], "<="&[@Last], Log[Last], ">="&[@First]) > 1

TRUE means this file's dates overlap another file for the same account.

Use statements for closed months. Let statements own every month that's finished, and only use downloads for the current month. When the statement for that month arrives, delete the download rows and replace them with the statement. One authoritative source per period leaves nothing to overlap.

Name files consistently. checking-2026-03.pdf is hard to add twice by accident. Statement (3).pdf and Statement (3) copy.pdf are easy to.

Load from a folder with Power Query. If every statement lives in one folder and Power Query loads the folder, adding a file twice means having two copies in the folder, which is easy to spot. The query also adds the filename as a Source column automatically. Our Power Query bank statement guide covers the setup.

Check the balance after every addition. If you add one statement and the control sheet shows it balances, and the opening balance matches the previous month's closing balance, you know nothing doubled up.

Duplicates inside Power Query

If you combine files in Power Query, you can handle the overlap rule there instead of in the sheet. Add a step that keeps download rows only when their date is after the last statement's end date, and the overlap never reaches the worksheet. Power Query's own Remove Duplicates step has the same risk as Excel's: use it only on a true transaction ID column, or on a combination that includes the source and a line number.

Transfers are not duplicates

Money moved between your own accounts appears on both statements, once negative and once positive. In a combined workbook, these look like pairs, and they inflate both income and spending if left in totals. But they're not duplicates. Deleting one side would break that account's balance.

Flag them instead: give both sides a Transfer category, and exclude it from income and spending reports. A helper formula finds likely pairs:

=COUNTIFS([Amount], -[@Amount], [Date], ">="&[@Date]-2, [Date], "<="&[@Date]+2, [Account], "<>"&[@Account])

Duplicates in accounting software

If duplicates have already reached your accounting software, the cleanup happens there, but the causes are the same: an import overlapping a bank feed, or a file imported twice. Our guide to fixing duplicate transactions in QuickBooks after an import covers that case, and our guide to Xero bank statement import errors covers Xero. Prevention is easier than cleanup: import one statement period at a time, and only for dates the bank feed doesn't already cover.

A worked example

A freelancer builds a yearly spending workbook from twelve converted statements plus a CSV download covering the first three weeks of the following January, so she can see recent spending too. Halfway through the year, she also accidentally appended June twice.

1. Source column. Every row has a source: one per statement, plus the download.
2. Balance checks. All twelve statements balance individually, but the June total in the combined table is exactly double. The June statement's source appears twice in the import log. She deletes the second block of June rows in one move.
3. Overlap rule. The download covers 20 December to 21 January. The December statement already covers 20–31 December. She deletes download rows dated on or before 31 December.
4. Keys. The remaining table has 17 keys with a total count of 2, all with a same-source count of 2. These are real repeats: two coffees, two train tickets, two transfers to savings. The statements balance, so she keeps them all.
5. Transfers. Transfers to savings get the Transfer category so they don't count as spending.
6. Verify. Every statement balances, monthly totals match the statement summaries, and her annual spending figure drops by the size of the duplicate June, which is exactly what she expected.

FAQ

Is it safe to use Excel's Remove Duplicates on bank transactions?

Usually not on its own. Real transactions can be identical, such as two purchases of the same amount at the same shop on the same day. Use a source column and a balance check to tell real repeats from accidental copies.

How do I find duplicate transactions?

Build a key from date, amount and description, count how often each key appears, and compare that with how often it appears in the same source file. Repeats across different sources are likely overlaps.

How do I know I didn't delete a real transaction?

Check that every statement period still balances: opening balance plus all activity equals closing balance. If it does, nothing real was removed.

What causes duplicates in the first place?

Overlapping downloads, the same statement added twice, combining a download with a statement for the same dates, and pending transactions that later posted with a different description or date.

Are transfers between my accounts duplicates?

No. They appear on both accounts and should both stay. Flag them as transfers so they don't count as income or spending.

Can I use UNIQUE instead?

UNIQUE is great for building lists of payees or categories. On whole transaction rows, it has the same problem as Remove Duplicates: it can't tell a real repeat from a copy.

What about pending transactions in a download?

Pending items can reappear later as posted transactions with a different date or description. Either exclude pending items from downloads, or replace the download with the statement once the month closes.

Can duplicates be caused by the PDF conversion itself?

Occasionally a line at a page break can be read twice. That's why each converted statement should balance on its own before you combine it with anything else. A balanced statement has no extra lines.

Does this work in Google Sheets?

Yes. COUNTIF, COUNTIFS, TEXT, TRIM, UPPER and UNIQUE all exist in Google Sheets with the same behavior.

Should I fix duplicates in Excel or in my accounting software?

Fix them where they are. If duplicates are only in your workbook, clean the workbook. If they've been imported into accounting software, they need removing there too, and the import process needs changing so it doesn't happen again.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro $19 and Business $39.