How to Remove Duplicates in Excel Without Losing Data

Excel's Remove Duplicates tool is under Data → Remove Duplicates. It works, and it deletes rows permanently with no undo once the file is saved. That's the part nobody mentions.

Before you use it, there's one question worth answering: do you actually want the duplicates gone, or do you just want to see them? Those are different jobs, and reaching for the delete button first is how people lose data they needed.

This guide covers four methods, from the safest to the fastest, and the five traps that make Excel delete the wrong rows.

Decide first: find, or remove?

What you want	Method	Destructive?
See which rows repeat	Conditional formatting or COUNTIF	No
A clean copy, original kept	UNIQUE, or Advanced Filter	No
Delete duplicates in place	Remove Duplicates	Yes
A repeatable cleanup	Power Query	No

If you're at all unsure, work non-destructively. A second copy of the data costs nothing; a deleted row you needed costs an afternoon.

Method 1: See the duplicates without touching them

Select your data range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Repeated values turn red immediately.

This checks one column at a time. To find rows that repeat across several columns, build a key first:

=A2&"|"&B2&"|"&C2

The pipe character keeps values apart, so "AB" + "C" doesn't collide with "A" + "BC". Then count how often each key appears:

=COUNTIF($D$2:$D$1000, D2)

Anything above 1 is a duplicate. Filter that column to show only those rows and you can see exactly what Excel would have deleted — before it deletes anything.

COUNTIF shows you exactly what Remove Duplicates would delete — before it deletes it.

Method 2: Remove Duplicates, done properly

Once you know what's there:

1. Copy the sheet first. Right-click the tab → Move or Copy → tick "Create a copy". Ten seconds, and it's the only real undo you have.
2. Click any cell inside your data.
3. Data → Remove Duplicates.
4. In the dialog, tick only the columns that define a duplicate.
5. Click OK. Excel reports how many rows it removed.

The column selection is the whole decision

This is where most damage happens. Excel compares only the columns you tick.

Tick Email only, and two rows with the same email are duplicates — even if one has a phone number and the other doesn't. Excel keeps the first and deletes the second, phone number included.

Tick every column, and only completely identical rows are removed. That's the conservative choice and usually the right one.

Excel always keeps the first occurrence and deletes the rest. It does not check which row is more complete or more recent. If the newer record sits lower in the sheet, sort by date descending before you run the tool.

Method 3: UNIQUE — a clean list without deleting anything

On Microsoft 365 or Excel 2021:

=UNIQUE(A2:C100)

The de-duplicated list spills into empty cells beside your data. The original is untouched, and the result updates itself when the source changes.

To list only values that appear exactly once — dropping every repeated value rather than collapsing it:

=UNIQUE(A2:A100, FALSE, TRUE)

⚠️ UNIQUE needs empty space to spill into. If anything sits in the way you get #SPILL! — clear the cells below and to the right.

Method 4: Advanced Filter — works in every Excel version

If UNIQUE isn't available:

1. Data → Advanced (in the Sort & Filter group)
2. Choose Copy to another location
3. List range: your data, including headers
4. Copy to: an empty cell where the clean list should start
5. Tick Unique records only → OK

You get a de-duplicated copy and keep the original. It doesn't refresh like UNIQUE does, but it runs anywhere — including Excel 2010.

Method 5: Power Query, for cleanups you repeat

If the same messy export lands on your desk every month, do it once in Power Query and refresh thereafter:

1. Select the data → Data → From Table/Range
2. Select the columns that define a duplicate
3. Right-click → Remove Duplicates
4. Close & Load to a new sheet

Next month, paste the new export over the source and hit Refresh. The cleanup replays itself.

Five traps

1. Trailing spaces

"PRD-102" and "PRD-102 " are different values to Excel, so neither Remove Duplicates nor COUNTIF sees them as a pair. Clean the column first with =TRIM(A2), paste back as values, then de-duplicate. This is the same invisible problem that breaks exact-match VLOOKUP.

2. Case is ignored

Excel treats "SMITH", "Smith" and "smith" as the same value. If case matters, add a helper column with =EXACT(A2,B2) logic or compare with a case-sensitive key before removing anything.

3. Numbers stored as text

The text "1001" and the number 1001 are not duplicates as far as Excel is concerned. Look for the small green triangle in the cell corner, then use Data → Text to Columns → Finish to convert the whole column.

4. Hidden and filtered rows still get deleted

Remove Duplicates ignores your filter. It processes the entire range, including rows you can't see. Clear all filters before running it.

5. "Expand the selection"

If you select one column and run the tool, Excel asks whether to expand to the surrounding data. Always expand. Continuing with the current selection deletes cells from that one column and leaves the rest of each row in place, which silently shifts your data out of alignment.

Does this work in Google Sheets?

Yes, with different names. Data → Data cleanup → Remove duplicates is the equivalent tool, and the UNIQUE function works the same way. Sheets also has Data cleanup → Trim whitespace, which handles trap 1 in one click.

Common questions

Can I undo Remove Duplicates?

Ctrl+Z works while the file is still open. Once you save and close, the rows are gone. Copy the sheet first.

Which row does Excel keep?

Always the first occurrence, top to bottom. Sort the data the way you want before running the tool — that's how you control which version survives.

How do I count duplicates before deleting?

Add =COUNTIF($A$2:$A$1000,A2) in a helper column and filter for values above 1.

Why did it say "0 duplicate values found" when I can see duplicates?

Almost always trailing spaces, or one value stored as text and the other as a number. Fix the formatting first, then run it again.

The short version

• Copy the sheet before deleting anything.
• Look at the duplicates before removing them — COUNTIF takes thirty seconds.
• The columns you tick define what counts as a duplicate. Tick too few and you lose good data.
• Excel keeps the first occurrence, so sort before you run it.
• Clean trailing spaces first, or the tool finds nothing.
• Prefer UNIQUE or Advanced Filter — neither destroys the original.

Once your list is clean, the natural next step is pulling matching values out of it — which is what a lookup formula is for.