How to Remove Duplicates in Excel: 6 Methods That Work

By the XLsheetAI Team · Updated August 7, 2026 · 8 min read

The fastest way to remove duplicates in Excel

Click any single cell inside your data, open the Data tab, and choose Remove Duplicates in the Data Tools group. Tick the columns that must match for two rows to count as the same record, then click OK. Excel keeps the first occurrence, deletes every later copy, and reports how many rows went.

For a one-off cleanup that is the right choice. The rest of this guide covers what the button does not do: showing you the duplicates before they disappear, leaving the original untouched, and rerunning the same cleanup next month in one click.

Before you delete anything: a four-step safety check

Remove Duplicates is destructive, and Undo only helps until you save and close. Spend two minutes protecting yourself: duplicate the sheet, record the starting row count, look at the duplicates before removing them, and note what was cleaned and when.

• Work on a copy. Right-click the sheet tab, choose Move or Copy, tick Create a copy, and clean the duplicate. If it goes wrong, you still hold the original.
• Count the rows first. Put =COUNTA(A2:A100000) in a spare cell and note the number. Excel reports how many rows it deleted; the two figures should reconcile.
• Highlight, review, then delete. Colour the repeats and scroll through them. Two customers can share a surname; two invoices should not share a number.
• Keep an audit column. Add a column such as Cleaned 2026-08-07, or record the pre-clean count on a notes tab. Six months on, nobody remembers why the file has 4,812 rows instead of 5,300.

Method 1: The Remove Duplicates button

Remove Duplicates is Excel's one-shot deduplication tool. It scans the columns you select, keeps the topmost occurrence of each combination of values, and permanently deletes the rest. It works on ranges and Tables, needs no formulas, and finishes instantly on tens of thousands of rows.

1. Click a single cell inside the data. Do not select one column by itself.
2. Go to Data ▸ Data Tools ▸ Remove Duplicates.
3. Tick My data has headers if row 1 holds column names, or Excel treats the header as a record.
4. Click Unselect All, then tick only the columns that define a duplicate. All columns ticked means a row goes only if every value matches.
5. Click OK and read the confirmation, which states how many duplicate values were removed and how many unique values remain.

The trade-offs are real. Deletion is permanent once you save and close. Matching ignores case, so ACME Ltd and Acme Ltd collapse into one row without asking. Sheet order alone decides which copy survives, so sort by date first if the newest record should win. And if you select one column while related data sits beside it, ignoring Excel's warning about extending the selection misaligns your rows.

Method 2: Advanced Filter with "Unique records only"

Advanced Filter extracts a duplicate-free copy of your data to another location and leaves the source untouched. It is the safest built-in method, works back to Excel 2007, and takes about as many clicks as Remove Duplicates. The catch: it copies within one worksheet only.

1. Select the range, including the header row.
2. Go to Data ▸ Sort & Filter ▸ Advanced.
3. Choose Copy to another location.
4. Confirm the List range, leave Criteria range empty, then click Copy to and pick an empty cell on the same sheet, such as H1.
5. Tick Unique records only and click OK.

Excel writes the clean list at the cell you nominated, so you can compare row counts side by side and delete the source only once you trust the result. Two limits: the destination must be on the active worksheet, and the output is a static snapshot that ignores rows added later. Filter the list, in-place only hides duplicate rows, which is easy to mistake for a real cleanup.

Method 3: Highlight duplicates before you delete anything

Conditional formatting colours every repeated value without changing a single cell, which makes it the right first move whenever the data matters. You see which rows are at risk, spot near-misses caused by stray spaces, and decide case by case.

1. Select the column or range to inspect.
2. Go to Home ▸ Conditional Formatting ▸ Highlight Cells Rules ▸ Duplicate Values.
3. Leave the dropdown on Duplicate, pick a fill colour, click OK.

To shade whole rows rather than single cells, use a formula rule: select the data range, choose Conditional Formatting ▸ New Rule ▸ Use a formula, and enter =COUNTIF($B$2:$B$5000,$B2)>1, locking the column but not the row. Every row whose column B value repeats is shaded full width.

With the duplicates visible, click the filter arrow, choose Filter by Color to isolate them, and delete only the rows you want gone. That extra minute prevents the most expensive mistake in spreadsheet cleanup: deleting records that merely looked identical.

Method 4: A COUNTIF helper column

A helper column turns duplicates into numbers you can sort, filter, and count. COUNTIF reports how many times each value appears; a second variant numbers each occurrence, so you can keep the first and drop the rest. Nothing is deleted automatically, which makes it the most transparent method.

=COUNTIF($B$2:$B$5000, B2)          → how many times this value appears
=COUNTIF($B$2:B2, B2)               → 1 for the first occurrence, 2 for the second, …
=IF(COUNTIF($B$2:B2,B2)>1,"Duplicate","Keep")

Note the expanding range in the second formula: the start is locked to $B$2 while the end grows as you fill down, so each row counts only the occurrences above it. Filter that column for values above 1 and delete the visible rows. For multi-column matching, join the fields with =B2&"|"&C2 and count the combined key.

The drawbacks: an expanding COUNTIF over hundreds of thousands of rows recalculates slowly, and the helper column has to be deleted afterwards. If COUNTIF is hazy, our Excel formulas cheat sheet covers the syntax in one page.

Method 5: UNIQUE() for a live, formula-driven list

UNIQUE returns a duplicate-free list that updates itself whenever the source changes. Type it once and the spilled output stays current. It requires Microsoft 365 or Excel 2021 and later, plus Excel for the web and Google Sheets, and returns a #NAME? error in Excel 2019 and 2016.

=UNIQUE(B2:B5000)                     unique values from one column
=UNIQUE(A2:C5000)                     unique whole rows across three columns
=UNIQUE(B2:B5000,,TRUE)               values that appear exactly once
=SORT(UNIQUE(FILTER(B2:B5000,B2:B5000<>"")))   clean, sorted, no blanks

The third argument is the one people miss: set to TRUE it returns only values with no repeat at all, a different question from "one of each".

Because UNIQUE spills, the cells below and right of it must be empty or you get a #SPILL! error. The output is a formula result, not data: you cannot type over individual cells, and deleting the source collapses the list. Copy and paste as values when you need something static. Dynamic arrays pair naturally with modern lookups, compared in XLOOKUP vs VLOOKUP.

Method 6: Power Query for cleanup you repeat

Power Query records your cleanup as a repeatable set of steps. Load the data once, remove duplicates in the query editor, and load the result to a new sheet. Next month, paste in fresh data and click Refresh: every step reruns in the same order.

1. Select your data and choose Data ▸ Get & Transform Data ▸ From Table/Range. Excel converts the range to a Table and opens the Power Query Editor.
2. Select the column or columns that define a duplicate, Ctrl-clicking for more than one.
3. Right-click a selected header and choose Remove Duplicates.
4. Add cleanup steps as needed: Transform ▸ Format ▸ Trim for stray spaces, Detect Data Type for text numbers.
5. Click Close & Load to write the result to a new worksheet.

One behaviour surprises people: Power Query's Remove Duplicates is case-sensitive, unlike the ribbon button, so ACME and Acme both survive. Lowercase the column first (Transform ▸ Format ▸ lowercase) to match the ribbon. Power Query is the heaviest option to learn and overkill for a one-off list, but for a monthly CRM export it pays for itself the second time you use it.

Not sure which method your data needs? Describe the problem in plain English — "flag rows where the email repeats but keep the newest" — and XLsheetAI writes the formula or the steps for you, ready to paste.

Removing duplicates in Google Sheets

Google Sheets offers both a menu command and a formula. Select your range, choose Data ▸ Data cleanup ▸ Remove duplicates, tick the columns that define a match, and click Remove duplicates. For a live list that never touches the source, put =UNIQUE(B2:B) in an empty cell instead.

The command reports how many duplicate rows were removed and how many remain, exactly like Excel. Tick Data has header row so column names are not read as a record. Matching ignores case here too, and deletion is permanent, though File ▸ Version history can walk it back.

The same menu holds a useful companion: Trim whitespace strips leading and trailing spaces from the selection in one step, catching the commonest reason duplicates slip through. UNIQUE has worked in Sheets for years, so there is no version question, and it accepts open-ended ranges such as B2:B.

Five reasons duplicates survive the cleanup

When Excel insists there are no duplicates and you can plainly see two identical rows, the values differ in a way the screen does not show. Invisible spaces, mixed data types, merged cells and mismatched column choices cover nearly every case, and each has a specific fix.

Bottom line

On a copied sheet, the Remove Duplicates button is hard to beat. When the data matters, highlight the duplicates first, or let Advanced Filter build a clean list while the original stays intact. Choose UNIQUE when the result should stay current, Power Query when the cleanup comes round monthly. Either way, count the rows before and after.

Cleaning a messy export right now? XLsheetAI writes the COUNTIF, TRIM or UNIQUE formula you need from a plain-English description, and explains any formula you paste in before it touches your data.