How to Remove Duplicates in Excel (4 Ways)

Duplicate rows quietly ruin spreadsheets: mailing lists that send twice, stock counts that double up, sales totals that include the same order two times. Excel gives you four ways to deal with duplicates, and the right one depends on whether you want to delete them permanently, just see them, or keep your original data untouched. All four are covered here, with the traps that catch people out.

Before you start: work on a copy

One of the methods below (Remove Duplicates) deletes rows permanently, with no undo once you save and close. Take thirty seconds first: right-click the sheet tab, choose Move or Copy → Create a copy, and work on the copy — or save a backup version of the file. Every duplicate disaster story starts with skipping this step.

Way 1: The Remove Duplicates tool (fastest, permanent)

Click any cell inside your data, then go to Data → Remove Duplicates. A dialog lists your columns with checkboxes. Tick the columns Excel should compare: a row counts as a duplicate only if ALL the ticked columns match. If your data has a header row, make sure “My data has headers” is ticked so the header is not treated as data. Click OK and Excel deletes the duplicate rows and tells you how many were removed and how many unique values remain.

The column choice is the whole skill here. Comparing only the Email column deletes every repeat email even if the names differ; comparing Name + Email + Date keeps rows that share an email but are genuinely different entries.

Way 2: The UNIQUE function (nothing gets deleted)

If you use Microsoft 365 or Excel 2021 or later, the UNIQUE function produces a clean list without touching the original:

=UNIQUE(A2:A100) — returns each value from the range once, spilling down as far as needed.

=UNIQUE(A2:C100) — returns unique ROWS across three columns: a row is kept unless all three values repeat an earlier row.

Because the result is a live formula, it updates when the source data changes — the right choice for dashboards and lists that keep growing.

Way 3: Highlight duplicates first with Conditional Formatting

Sometimes you should look before you delete. Select the range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every repeated value is shaded instantly. This is the safest way to inspect what Excel considers a duplicate — you will often spot that two “duplicates” are actually different records with the same name. Remove the highlighting later from the same menu (Clear Rules).

Way 4: Advanced Filter (for older Excel versions)

On versions without UNIQUE, Data → Advanced still works: choose “Copy to another location”, set the List range to your data, pick a destination cell, and tick Unique records only. Excel writes out the deduplicated list, leaving the original in place.

Three traps that make duplicates “invisible”

• Trailing spaces. “Ali ” and “Ali” look identical but are different values to Excel, so both survive deduplication. Clean lists first (the TRIM function removes extra spaces).
• Numbers stored as text. An ID entered as text will not match the same ID stored as a number. Consistent formatting matters before you dedupe.
• Case. Excel’s duplicate tools generally treat “Apple” and “apple” as the same value. If case matters in your data, check the results carefully after deduplicating.

Doing the same in Google Sheets

Sheets has the tool built in: select your data, then Data → Data cleanup → Remove duplicates, and choose the columns to compare. The UNIQUE function also works identically to Excel’s. Both are covered in context in our Google Sheets Formulas: A Beginner’s List.

Which way should you choose?

• One-off cleanup of a finished list: Remove Duplicates tool — on a copy.
• A list that keeps changing: UNIQUE function.
• You are not sure the duplicates are really duplicates: Conditional Formatting first, decide second.
• Old Excel version: Advanced Filter.

Quick recap

Back up first. Remove Duplicates (Data tab) deletes repeats based on the columns you tick; UNIQUE builds a live clean list without deleting anything; Conditional Formatting shows duplicates for review; Advanced Filter covers older versions. And if duplicates survive a cleanup, suspect trailing spaces or text-versus-number mismatches before anything else.