What is the fastest way to remove duplicates in Excel?

The fastest native method is Data > Remove Duplicates. Excel keeps the first occurrence in the selected range and deletes later matches based on the columns you choose.

Does Excel keep the first or last duplicate?

Excel keeps the first occurrence it sees. If you want to keep the newest or oldest row, sort the table first, then run Remove Duplicates.

How do I highlight duplicates before deleting them in Excel?

Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, or apply a COUNTIF rule such as =COUNTIF($A$2:$A$400,A2)>1.

Can Excel remove duplicates across multiple columns?

Yes. In the Remove Duplicates dialog, select the columns that define a duplicate row. Excel removes rows only when the selected columns match.

How do I find duplicates between two Excel sheets?

Create a helper key or use COUNTIF or XLOOKUP to flag matches from one sheet against the other, then review before deleting anything.

Can Power Query remove fuzzy duplicates in Excel?

Yes. Power Query can remove exact duplicate rows and can use fuzzy matching for near-matches in text fields, which is useful for messy names and categories.

Remove Duplicates Excel: Safe Ways to Clean Lists and Rows

Name: Deckary
Rating: 4.8 (50 reviews)
Author: Deckary

Remove duplicates Excel guide with safe steps for single columns, full rows, two-sheet checks, fuzzy matches, and AI-assisted cleanup in messy workbooks.

Bob · Former McKinsey and Deloitte consultant with 6 years of experienceMay 9, 20269 min read

Remove duplicates Excel is one of the fastest native cleanup tasks in the ribbon, and one of the easiest ways to delete the wrong rows if you rush it. Excel keeps the first occurrence it sees, removes later matches, and bases the decision on the columns you select.

For this guide, we reviewed the top 5 Google results for "remove duplicates excel" on May 9, 2026, checked 6 current Microsoft Support and Microsoft Learn pages on duplicate removal, conditional formatting, worksheet comparison, and Power Query, and mapped 14 duplicate-cleanup jobs across 5 methods: native Remove Duplicates, conditional formatting, COUNTIF, cross-sheet checks, and fuzzy matching in Power Query.

This guide covers the safe native workflow first, then multi-column rows, two-sheet checks, and the cases where Power Query Excel or a workbook tool such as Deckary is the better option.

If the job is...	Best method	Why
Remove exact duplicates from one table	`Data > Remove Duplicates`	Fastest native option
Review duplicates before deleting	Conditional Formatting or `COUNTIF`	Safer when the row choice matters
Keep the newest or oldest row	Sort first, then Remove Duplicates	Excel keeps the first visible match
Find matches across two sheets	Helper key plus `COUNTIF` or `XLOOKUP`	Better for reconciliation than blind deletion
Match near-duplicates like `Acme Ltd` and `Acme Limited`	Power Query fuzzy merge	Native Remove Duplicates only handles exact matches

Remove duplicates Excel decision guide infographic

What Remove Duplicates in Excel Actually Does#

Remove Duplicates in Excel deletes repeated rows from a selected range or table while keeping the first occurrence. Microsoft's Filter for unique values or remove duplicate values page says the change only affects the selected range or table, and that values outside the selected range do not move.

If you select...	Excel treats rows as duplicates when...	Best use
One ID column	The IDs match	Keep one row per customer, employee, SKU, or invoice
Several business-key columns	All selected key fields match	Remove repeated records where a row should be unique by key combination
Every column in the table	The whole row matches	Delete only exact full-row duplicates

Filtering unique values only hides repeats temporarily. Removing duplicates deletes them.

How to Use Remove Duplicates Excel on One List#

Microsoft's support page says Excel keeps the first occurrence it finds. If you want to keep the newest invoice, latest status, or highest-priority record, sort first and dedupe second.

Use this sequence:

Copy the table to a backup sheet if the cleanup matters.
Sort the data so the row you want to keep appears first in each duplicate group.
Click any cell inside the range or table.
Go to Data > Remove Duplicates.
Select the columns that define a duplicate.
Confirm the result message and spot-check a few rows.

Goal	Sort first?	Columns to select
Keep one row per customer ID	Optional	`Customer ID`
Keep the newest transaction per ID	Yes, newest date first	`Customer ID`
Keep only fully unique records	Usually no	All key columns or all columns

Most tutorials skip the sort step. That is where many bad deletions happen.

How to Preview Duplicates Before You Delete Them#

Microsoft's conditional formatting documentation shows two practical ways to preview duplicates: the built-in Duplicate Values rule and a formula rule with COUNTIF.

The quick native preview is:

Select the target column or range.
Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Pick the format and review the highlighted cells.

If you want more control, use a formula rule. Microsoft gives this example for duplicates in A2:A400:

=COUNTIF($A$2:$A$400,A2)>1

To preview duplicate composite keys, add a helper column like:

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

Then apply the same COUNTIF logic to the helper key column.

Preview method	Best for	Limitation
Duplicate Values rule	Fast visual review	Less flexible for complex logic
`COUNTIF` helper logic	Custom checks and exception rules	Takes one extra column or one custom rule

Continue reading: PowerPoint Master Slide · Best Fonts for PowerPoint · AI for Excel

Build and audit Excel workbooks with AI

Describe the model, cleanup, audit, or output sheet you need. Deckary's AI Excel agent works through the task inside your workbook.

Try AI Excel Agent See How It Works

How to Remove Duplicate Rows Across Multiple Columns#

Suppose two rows share the same Customer ID but have different Region values. If you dedupe on Customer ID alone, one region row will be deleted. If the real business key is Customer ID + Region, you need to select both columns.

Use this rule:

Business question	Select these columns
"Should there be only one row per employee?"	`Employee ID`
"Should there be only one row per employee per month?"	`Employee ID`, `Month`
"Should this whole record appear only once?"	All relevant columns

Power users often add a helper key before deleting anything:

=A2&"|"&TEXT(B2,"yyyy-mm-dd")&"|"&C2

That makes it easier to review exact row groups before you remove them.

How to Find Duplicates Between Two Excel Sheets#

The fastest exact-match approach is a helper formula on Sheet1 that checks whether the value exists on Sheet2:

=COUNTIF(Sheet2!$A:$A,A2)>0

If the result is TRUE, the value in A2 exists on Sheet2. If the duplicate definition spans several fields, create the same helper key on both sheets, then compare the keys instead of the raw columns.

Cross-sheet task	Recommended method	Why
Check whether one ID exists on another tab	`COUNTIF`	Fast and readable
Pull the matched value back for review	`XLOOKUP`	Good when you want the exact matched field beside the source row
Compare two tabs visually	Side-by-side view	Useful for manual QA on smaller lists
Reconcile multi-column rows	Helper key on both sheets	Cleaner than several nested formulas

Microsoft's Compare two or more worksheets at the same time page also shows how to open worksheets side by side and turn on synchronous scrolling for quick manual review.

When Remove Duplicates Excel Is Not Enough#

For repeatable imports, Power Query is better than rerunning cleanup steps by hand. Microsoft's Keep or remove duplicate rows (Power Query) page says duplicate removal is based on the columns you select in the query, just like the worksheet tool, but the steps can be rerun whenever new files arrive.

For fuzzy text cleanup, Microsoft's Fuzzy merge documentation says Power Query uses the Jaccard similarity algorithm and sets the default similarity threshold to 0.80. Microsoft's How fuzzy matching works in Power Query page says a threshold of 1.00 behaves like an exact match, while lower thresholds allow increasingly loose text matches.

Situation	Worksheet Remove Duplicates	Power Query
Exact duplicate customer IDs	Good	Good
Recurring monthly cleanup	Manual repetition	Better because the steps can be refreshed
Near-matches like `Acme Ltd` and `Acme Limited`	No	Better with fuzzy matching
Duplicate rows across several imported files	Clumsy	Better with query-based cleanup

Use fuzzy matching on vendor names, free-text categories, or messy survey values. Do not use it on invoice numbers, account IDs, or anything else that should match exactly.

When AI Helps With Duplicate Cleanup in Excel#

Microsoft and LinkedIn's 2024 Work Trend Index says 75% of knowledge workers use AI at work. That explains why many teams now ask AI for cleanup help. The limit is reliability. Justin Payan and coauthors' 2023 paper InstructExcel: A Benchmark for Natural Language Instruction in Excel introduced a benchmark with over 10,000 samples, 170-plus Excel operations, and 2,000 public spreadsheets, and found spreadsheet instruction following is still hard for strong models. Raymond Panko's 2008 paper Spreadsheet Errors: What We Know. What We Think We Can Do says research had already shown spreadsheet errors to be common and non-trivial. Microsoft and LinkedIn's 2024 Work Trend Index says 75% of knowledge workers use AI at work. The limit is reliability. Justin Payan and coauthors' 2023 paper InstructExcel: A Benchmark for Natural Language Instruction in Excel introduced a benchmark with over 10,000 samples, 170-plus Excel operations, and 2,000 public spreadsheets, and found spreadsheet instruction following is still hard for strong models. Raymond Panko's 2008 paper Spreadsheet Errors: What We Know. What We Think We Can Do says spreadsheet errors are common and non-trivial.

If the task is...	Best move
Remove exact duplicates from one clean list	Use native Excel
Build a repeatable cleanup flow for monthly files	Use Power Query
Audit duplicates across tabs, keep the newest rows, and generate an exceptions tab	Use a workbook agent and verify the output

This is where tools such as Deckary fit naturally. Instead of giving you one formula and stopping, the tool can inspect the workbook, add helper columns, write a reconciliation tab, and leave a reviewable output before you approve permanent changes. For adjacent workflows, see AI for Excel, ChatGPT for Excel, and Excel AI Agent.

Common Mistakes#

Mistake	Better move
Dedupe before sorting	Sort first based on the row you want to preserve
Select too few columns	Define the business key before deleting
Trust AI output without checking	Spot-check the kept rows and exceptions

Summary#

Native Remove Duplicates is the fastest option for exact duplicates in one table.
Excel keeps the first occurrence, so sort first if row priority matters.
Use conditional formatting or COUNTIF when you need to preview duplicates before deleting.
Use helper keys for multi-column and cross-sheet duplicate checks.
Use Power Query or an AI workbook agent when the cleanup is repeatable, fuzzy, or workbook-wide.