How to Remove Duplicates in Excel (5 Methods, 2026 Guide)

To remove duplicates in Excel, click any cell in your data, go to the Data tab, and click Remove Duplicates in the Data Tools group. In the pop-up, tick the columns Excel should compare, confirm My data has headers is set correctly, and click OK. Excel deletes the duplicate rows in place and tells you how many it removed.

That one button handles most cases. The parts worth knowing are the choices around it: deduping by a single column, keeping your original data intact, reviewing duplicates before you delete anything, and building a cleanup that refreshes automatically. This guide covers all five methods and the gotchas that make Excel "miss" duplicates.

Method 1: The Remove Duplicates button (fastest)

This is the built-in, permanent way to delete duplicate rows.

1. Click any cell inside your data table (or select the exact range you want to clean).
2. Go to the Data tab and click Remove Duplicates.
3. If your top row contains column names, tick My data has headers so Excel does not treat them as data.
4. Tick the columns Excel should use to decide what counts as a duplicate.
5. Click OK.

Excel keeps the first occurrence of each duplicate set and deletes the rest, then shows a message like "3 duplicate values found and removed; 47 unique values remain."

Important: this changes your sheet in place. If you might need the original, copy the sheet first or use Method 3 instead.

Method 2: Remove duplicates by specific columns

By default Excel compares every column, so a row is only a duplicate if the entire row matches. Often you want to dedupe on just one or two key columns.

• By one column (for example, remove repeated emails): open Remove Duplicates, click Unselect All, then tick only Email. Excel now treats two rows as duplicates whenever the email matches, even if the name or date differs, and keeps the first one.
• By multiple columns (for example, First name + Last name together): tick both columns. A row is a duplicate only when both values match another row, so two different people who share a first name are kept.

Choosing the right key columns is the whole game. Pick the column(s) that truly identify a unique record (an ID, an email, an invoice number) rather than every column.

Method 3: The UNIQUE function (keeps your original data)

If you want a clean list without touching the source, use the UNIQUE function (Excel 365 and Excel 2021).

1. Click an empty cell where the clean list should start.
2. Type =UNIQUE(A2:A100) and press Enter.
3. Excel spills the unique values into the cells below automatically.

Because it is a formula, it updates live: add a row to the source and the unique list refreshes on its own. Useful variations:

• Whole rows / multiple columns: =UNIQUE(A2:C100) returns unique combinations across those columns.
• Values that appear exactly once (true one-offs, dropping anything that was ever repeated): =UNIQUE(A2:A100,,TRUE).
• Sorted result: wrap it, =SORT(UNIQUE(A2:A100)).

On older versions without UNIQUE, use Method 5 (Advanced Filter) instead.

Method 4: Highlight duplicates before deleting (safest)

On important data, review first. Conditional Formatting colors duplicates without changing anything.

1. Select the range you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a fill color and click OK.

Every duplicate is now colored, so you can scan them, decide which to keep, and delete manually, or run Remove Duplicates once you are confident. To clear the coloring afterward, use Conditional Formatting > Clear Rules.

Method 5: Advanced Filter and Power Query

Two options for older Excel or for cleanups you repeat regularly.

Advanced Filter copies unique records to a new location (works in every Excel version):

1. Select your range, go to Data > Advanced (in the Sort & Filter group).
2. Choose Copy to another location, set Copy to to an empty cell, and tick Unique records only.
3. Click OK. The original stays put; a deduplicated copy appears where you pointed it.

Power Query is best when the same file gets new data every week and you want a repeatable cleanup:

1. Select your data and go to Data > From Table/Range.
2. In the Power Query editor, select the key column(s), right-click, and choose Remove Duplicates.
3. Click Close & Load. Next time the source updates, hit Refresh and the dedup runs again automatically.

Or remove them instantly, without Excel

If you do not have Excel open, or the file is a CSV, or you would rather not touch the original with a destructive button, you can do the whole thing in your browser with our free Excel Duplicate Finder and Remover. Drop in an .xlsx, .xls, or .csv file, tick the columns that define a duplicate, and it shows you the matches highlighted, then lets you download a cleaned file, a file of just the duplicates, or a highlighted copy to review in Excel.

It runs entirely on your device, so nothing is uploaded, which makes it safe for customer lists, mailing lists, and any file with personal data. It applies the same "keep the first of each group" rule as Excel and handles case-insensitive matching and single-column or multi-column keys, so the results match what the methods above would give you.

Why Excel sometimes "misses" duplicates

If Remove Duplicates leaves rows that clearly look identical, it is almost always one of these:

• Trailing or double spaces. "John Smith" and "John Smith " are different to Excel. Fix a column with =TRIM(A2), paste the result back as values, then dedupe.
• Numbers stored as text. 1001 as a number and 1001 as text do not match. Select the column and use Data > Text to Columns > Finish to normalize the type.
• Different formatting, same value. Dates or currency displayed the same can hold different underlying values. Match the data type, not just the look.
• Case is ignored, not honored. Remove Duplicates is not case sensitive, so "ABC" and "abc" are treated as one. If you need case-sensitive dedup, use a helper column with EXACT or a Power Query step.

After extraction: cleaning imported data

Duplicate rows show up most often in data you imported rather than typed, such as merged exports, re-run reports, or bank and invoice data pulled from PDFs. If you are pulling tables out of documents, a clean extraction cuts the cleanup work down front.

Our guide on how to convert a PDF to Excel walks through getting tables out with columns and number formats intact, and the convert bank statement to Excel guide covers multi-page statements where duplicate or repeated header rows are common. Dedupe the result with the steps above and you have a clean, analysis-ready sheet.

Quick reference

For a one-time cleanup, Data > Remove Duplicates is all you need. For anything you will repeat, or any data you cannot afford to overwrite, reach for UNIQUE or Power Query so your source stays safe and the dedup runs itself next time.