How to Remove Duplicates in Excel (Without Deleting Data You Need)

How-to · Updated July 2026 · ~11 min read

You paste an export into Excel, spot the same invoice number twice, and reach for Data > Remove Duplicates. Twenty minutes later a total doesn't tie and you can't tell which rows Excel took away. The button does exactly what Microsoft documents — the problem is that it deletes whole rows permanently, it decides what "identical" means differently from how you would, and it leaves no record of what it removed.

Below is the safe order: find the duplicates, decide what counts as one, then remove them with a method that matches how much you can afford to be wrong.

Start here: find before you delete

Finding and deleting are different jobs. Microsoft puts it plainly: "When you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values."

Almost every duplicate problem in finance is an investigation first, so work in this order: highlight or count the duplicates in place; confirm the flagged rows are repeats and not two legitimate transactions that look alike; clean the data so the comparison is fair; and only then remove — or better, output a deduplicated copy and keep the original.

Method 1: Highlight duplicates with Conditional Formatting

The fastest visual check, and it changes no data. Select the column or range, then: on the Home tab, in the Style group, select the small arrow for Conditional Formatting, and then select Highlight Cells Rules, and select Duplicate Values. Pick a fill colour and click OK.

For the unique values instead, go Home > Conditional Formatting > Manage Rules > New Rule, pick the rule type Format only unique or duplicate values, and choose unique in the Format all list. One documented limitation: "You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values." Inside a PivotTable, work on the source data.

Method 2: Count duplicates with COUNTIF and COUNTIFS

Highlighting shows that there's a duplicate. A helper column tells you how many times a value appears.

=COUNTIF($A$2:$A$1000,A2)>1

TRUE for every row whose column A value appears more than once; drop the >1 to see the count itself. Syntax is COUNTIF(range, criteria), and note that "criteria aren't case sensitive."

Real duplicates in accounting data are rarely one column. An invoice is a duplicate if the supplier, the invoice number and the amount all repeat — that's COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…), up to 127 pairs, where "each additional range must have the same number of rows and columns as the criteria_range1 argument":

=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2)>1

A criteria reference pointing at an empty cell is treated as 0, so keep the ranges tight or blank rows will flag each other. Filter that column to TRUE and you have a reviewable list of every duplicate group.

Method 3: Data > Remove Duplicates — and exactly what it does

The built-in button, and the right tool when the data is disposable.

1. Select the range or click inside your table.
2. On the Data tab, select Remove Duplicates (in the Data Tools group).
3. Under Columns, use Select All or Unselect All, then tick only the columns Excel should compare. (Tick the dialog's My data has headers box too, so your header row isn't treated as data.)
4. Click OK. Excel reports how many duplicate values were removed and how many unique values remain — don't quote that number in a workpaper, because the counts "might include empty cells, spaces, etc."
5. If it's wrong, Ctrl+Z undoes it.

Four things to internalise before you press it:

It deletes entire rows, not just the compared cells. Microsoft is explicit: "Data will be removed from all columns, even if you don't select all the columns at this step." So the column choice matters: dedupe on invoice number alone and a corrected cost centre on the second row goes with it, while deduping on every column keeps both rows and you carry on double-counting.

It keeps the first occurrence. "When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted." Sort order decides which row survives — sort newest-first to keep the latest version.

It's permanent, and Microsoft says back up first: "Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another sheet or workbook before removing duplicate values."

It won't run on outlined or subtotalled data. "You cannot remove duplicate values from outline data that is outlined or that has subtotals." Clear subtotals and grouping first.

How Excel decides two rows are identical

This explains most "Remove Duplicates missed some" complaints: "A duplicate value is one in which all values in at least one row are identical to all of the values in another row. A comparison of duplicate values depends on what appears in the cell—not the underlying value stored in the cell."

The comparison is on the displayed value, with documented consequences:

Formatting inconsistency therefore hides duplicates, while two unrelated formulas showing 1,250.00 get collapsed into one. Standardise formats first.

Case is a related trap. Excel's text comparison ignores upper and lower case — for text filtering Microsoft states that Excel "doesn't distinguish between uppercase and lowercase characters" — so ACME Ltd and acme ltd behave as the same value. If case matters, flag it with EXACT(), which "is case-sensitive but ignores formatting differences":

=SUMPRODUCT(--EXACT($A$2:$A$1000,A2))>1

That construction is mine rather than a documented Microsoft pattern, but it reliably counts case-exact matches across the range. Keep the range tight, because blanks match blanks and will flag.

Clean the data before you compare it

Two things routinely make genuine duplicates look unique in ERP exports. Trailing and non-printing characters — "ACME Ltd " is not "ACME Ltd" — so compare on =TRIM(CLEAN(A2)) instead (my recommendation; Microsoft doesn't document how Remove Duplicates handles whitespace). And numbers stored as text: 4,500.00 as text never matches 4500 as a number, so convert with =IFERROR(VALUE(A2),A2) or work through cleaning messy data in Excel.

Better still, build one "match key" that normalises everything at once and dedupe on that:

=UPPER(TRIM(CLEAN(A2)))&"|"&TEXT(B2,"yyyy-mm-dd")&"|"&TEXT(C2,"0.00")

Supplier, date and amount in one string; duplicates on that key are real duplicates.

Method 4: UNIQUE() — a live list that never touches the source

On a recent Excel this is the cleanest answer: a deduplicated output, source untouched. Syntax is =UNIQUE(array,[by_col],[exactly_once]) — array is the range to return unique rows or columns from; by_col TRUE compares columns and returns unique columns, FALSE or omitted compares rows; exactly_once TRUE returns only rows or columns occurring exactly once, FALSE or omitted returns all distinct ones.

So =UNIQUE(A2:A20) is a distinct list of one column; =SORT(UNIQUE(A2:A20)) is distinct and alphabetised, a nesting that appears in Microsoft's own examples; =UNIQUE(A2:C20) compares whole rows; and =UNIQUE(A2:A20,FALSE,TRUE) returns only values appearing exactly once, so everything missing from that list is a duplicate.

UNIQUE is in Microsoft 365, Excel 2021, Excel 2021 for Mac, Excel 2024, Excel 2024 for Mac, Microsoft365.com, and Excel for iPad, iPhone and Android. It is not in Excel 2019 or 2016, so if a colleague on a perpetual 2019 licence has to open the file, use another method.

Method 5: Advanced Filter — copy the unique rows out

Works in any Excel version and needs no formulas. Click inside your data, then Select Data > Advanced (in the Sort & Filter group), choose Copy to another location, confirm the List range, give a destination in the Copy to box, tick Unique records only and click OK. You get a deduplicated copy beside untouched source data; Filter the list, in-place hides the duplicates instead.

Method 6: Power Query — repeatable and refreshable

If this is a monthly job, do it once in Power Query and refresh thereafter. The dedupe becomes a recorded step, not something someone has to remember to do correctly.

1. Select Data > Get & Transform Data > From Table/Range.
2. Select the columns to compare, then: Home tab > Reduce rows group > Remove rows > Remove duplicates.
3. Return the result with Home > Close & Load, which lands it on a new worksheet.

To audit rather than clean, invert it: Home > Reduce rows > Keep rows > Keep duplicates leaves only the offending rows.

Two official warnings to plan around:

• "Power Query is case-sensitive. When working with duplicate values, Power Query considers the case of the text, which might lead to undesired results. As a workaround, you can apply an uppercase or lowercase transform before removing duplicates."
• "Power Query doesn't guarantee that it keeps the first instance in a set of duplicates when it removes duplicates."

The second matters more than it looks: Excel's worksheet Remove Duplicates does keep the first occurrence, Power Query makes no such promise. Where the surviving row matters financially, group by your key and take the max date or amount. New to the editor? Start with Power Query for beginners.

Which method should you use?

The accounting problem: duplicate invoices and payments

Duplicate rows in a spreadsheet are annoying; duplicate transactions in a ledger are money. The same invoice arrives twice — once by email, once on a statement upload — and gets paid twice, and because the references differ (INV-4471 vs 4471 vs INV4471) exact matching never finds it. A payment recorded twice also stops a reconciliation balancing: one bank line, two book lines, and the gap is exactly the duplicate.

So stop looking for identical rows. Look for the same supplier and amount within a date window. With supplier in column A, date in C and amount in D:

1. Sort by Supplier, then Amount, then Date.
2. Add a normalised supplier key in column E: =UPPER(TRIM(A2)).
3. Flag same-supplier, same-amount pairs: =COUNTIFS($E$2:$E$5000,E2,$D$2:$D$5000,D2)>1.
4. From row 3 of a second helper column, copied down, get the gap between consecutive rows: =IF(AND(E3=E2,D3=D2),C3-C2,"").
5. Review anything under, say, a 30-day gap; long regular gaps are usually recurring payments.

Watch the reverse trap: a payment and its reversal, or a credit note, can look like a duplicate — check the sign and document type first. Where references differ by formatting rather than substance, you need fuzzy matching.

If a duplicate is what's breaking your month-end, our free bank reconciliation template is a starting point: a Reconciliation sheet that works the bank side against the book side and prints "RECONCILED" or "OUT BY" the difference, plus Bank statement and Ledger sheets with Date / Description / Amount / Matched? for 200 rows, where Matched? uses COUNTIFS against the other sheet's Amount column. Its limitation is printed on the sheet: amount-only matching can't handle one deposit covering several invoices, or descriptions that differ. Fuller walkthrough: reconciling a bank statement in Excel.

Where an AI add-in fits

Everything above is free and built into Excel, and for most duplicate problems it's all you need. Formulas stop scaling only on huge ledgers where the duplicates aren't exact.

HISAB 360 is a paid Excel add-in built for that case. Its AI bank reconciliation matches statement lines to ledger rows using amount plus date tolerance, many-to-one deposits and fuzzy descriptions, flagging duplicates in the same pass. You can also describe the dedupe in plain English in its chat panel and it writes and applies the Power Query step or formula to your live workbook — a recorded query step being auditable in a way a manual Remove Duplicates click never is. It reads and writes QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage.

Honest limits: Windows desktop Excel only — not Mac, not Excel on the web — and paid after a 15-day free trial (every feature plus 50 HISAB AI credits, no credit card, once per machine). Connecting an accounting system means authorising it yourself via OAuth, and any write back to a ledger should be reviewed before you approve it. See features or pricing.

Whatever you use, the habit is the same: standardise formats, build a match key, count the duplicates, review the flagged groups, then remove — into a copy if the data matters.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.