How to Remove Duplicates in Excel

Delete duplicate rows in seconds with the built-in tool, dedupe based only on the columns you choose, highlight duplicates without deleting them, or pull a clean unique list with a formula.

By the Quiriz Team · Published July 24, 2026 · 5 min read

The quick answer: click any cell in your data, go to Data → Remove Duplicates, choose the columns that define a duplicate, and click OK. Below are the three situations that trip people up — deduping on specific columns, reviewing before deleting, and getting a unique list without touching the original.

1. The built-in Remove Duplicates tool

Click anywhere inside your table, then Data → Remove Duplicates. Excel shows a list of your columns with checkboxes. Leave them all ticked and it removes rows that are identical across every column. Click OK and it tells you how many duplicates it deleted and how many unique rows remain.

2. Remove duplicates based on specific columns

This is the part people miss. Say you want one row per customer email, even if the other columns differ. In the Remove Duplicates dialog, tick only the Email column and untick the rest. Now Excel calls two rows duplicates whenever the email matches, keeps the first, and drops the others. The ticked columns define what "duplicate" means.

3. Highlight duplicates without deleting

Want to review before you delete? Select the range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every duplicate gets coloured, nothing is removed, and you can decide row by row. Safer when the data matters.

Bonus: pull a clean unique list with a formula

On Excel 365 or 2021, =UNIQUE(A2:A100) spills a fresh list of distinct values wherever you put it, leaving your source data untouched. Combine it with SORT for a tidy list: =SORT(UNIQUE(A2:A100)).

The built-in tool matches values exactly. Near-duplicates — "Acme Inc" vs "Acme, Inc." vs "ACME inc" — slip right through, and that's usually where messy exports hurt. Fixing those means cleaning the text first, or handing the whole file to something that compares on meaning rather than exact characters.

🛠 Free tool: prefer not to click through menus? the free Duplicate Remover lets you paste your columns, pick which fields define a duplicate, and copy the clean result back — all in your browser, nothing uploaded.

Cleaning data so you can analyze it?

Quiriz reads messy Excel files as-is and answers your questions directly — ask "revenue by customer" without deduping by hand first. Free to start.

Try Quiriz free →

Frequently asked questions

How do I remove duplicates based on one column?

Open Data > Remove Duplicates, then tick only that one column and untick the rest. Excel treats two rows as duplicates whenever that single column matches, keeping the first occurrence and deleting the others.

How do I highlight duplicates without deleting them?

Select the range, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Duplicates get coloured so you can review them before deciding what to remove.

Is Remove Duplicates case-sensitive?

No. Excel's Remove Duplicates treats "Apple" and "apple" as the same value. For case-sensitive dedupe you need a helper formula with EXACT, or a tool that respects case.

How do I get a list of unique values without changing the original?

In Excel 365 or 2021, use =UNIQUE(range). It spills a fresh list of distinct values in a new location and leaves your source data untouched.