How to find and remove duplicates in Excel

Answer up front

If you need to remove duplicates in Excel, choose the simplest safe tool for the job: use Excel's Remove Duplicates when you want a quick, column-based deletion; use Conditional Formatting or COUNTIFS when you want to mark duplicates without deleting; use Power Query when you need a repeatable, auditable transform across tables. Always work on a copy or backup so you can recover rows removed by mistake.

Which method fits your situation?

Different tasks require different approaches. The following brief comparison will help you pick a method quickly.

• Quick, one-off deletion: Remove Duplicates button in the Data tab.
• Don't delete — just highlight or flag: Conditional Formatting or COUNTIFS formulas so you can review first. See Highlight Duplicates with Conditional Formatting in Excel for more on highlighting options.
• Repeatable, transformable cleaning: Power Query provides a repeatable pipeline and works well for larger, linked datasets — see Use Power Query to Clean and Transform Data.
• Extract unique rows to another sheet without deleting: Advanced Filter.

Method 1 — Remove Duplicates (fast and destructive)

Use Remove Duplicates when you are certain which fields define a duplicate and you can lose the extra rows. This tool deletes rows permanently unless you undo or use a copy.

1. Select any cell inside the table or the whole range you want to de-duplicate.
2. Go to the Data tab and click Remove Duplicates.
3. In the dialog, check the columns that define duplication. If two rows match in all checked columns, Excel keeps the first and deletes the rest.
4. Click OK. Excel reports how many rows were removed and how many remain.

Notes: If your data contains hidden columns, include them in the selection if they matter. If you want to preserve the original, copy the sheet first.

Worked example

Suppose you have columns Name, Email, and Purchase. To remove rows where both Name and Email repeat:

1. Select the data range (including headers).
2. Remove Duplicates > check Name and Email > OK.
3. Excel keeps the first matching Name+Email and deletes later duplicates.

Method 2 — Mark duplicates without deleting (Conditional Formatting and COUNTIFS)

If you need to review before deleting or keep duplicates but flag them, use Conditional Formatting to color cells or a COUNTIFS column to tag rows.

Conditional Formatting (visual review)

1. Select the column or range you want to check.
2. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a format and click OK. Duplicates will be highlighted but not removed.

Conditional Formatting highlights exact matches only. To highlight duplicates across multiple columns, create a helper column that concatenates key fields and apply formatting to that helper column. More advanced highlight techniques are explained in Highlight Duplicates with Conditional Formatting in Excel.

COUNTIFS (explicit tags you can filter)

1. Add a helper column named DuplicateFlag.
2. Use a formula like =IF(COUNTIFS($A:$A,$A2,$B:$B,$B2)>1,"Duplicate","Unique").
3. Copy the formula down and filter or sort by the flag to inspect duplicates.

This approach lets you keep the data intact while making it easy to export only uniques or only duplicates after review.

Method 3 — Advanced Filter (extract uniques to another range)

Advanced Filter is useful when you want a separate list of unique records without altering the source data.

1. Select the range including headers.
2. Data > Advanced (in the Sort & Filter group).
3. Choose 'Copy to another location', set the Copy To box, and check 'Unique records only'.
4. Click OK — a new list appears in the specified location containing one instance of each unique record.

This is non-destructive and quick for one-off exports, but not ideal for repeatable cleaning across changing source data.

Method 4 — Power Query (best for repeatable or complex rules)

Power Query is suited to cleaning that you will repeat, or when you must apply deduplication after other transforms (trim whitespace, split columns, normalize case).

1. Load your table to Power Query: Data > From Table/Range.
2. In the Power Query Editor, select the columns that define a duplicate.
3. Home > Remove Rows > Remove Duplicates.
4. Close & Load to return the cleaned table to Excel. You can Refresh whenever the source updates.

Power Query stores the steps as a query, so the same rules apply consistently. For an introduction and examples of cleaning steps that commonly precede deduplication, see Use Power Query to Clean and Transform Data.

Checklist before you remove duplicates

• Work on a copy or duplicate the sheet so deleted rows are recoverable. See Best Practices for Backing Up and Versioning Excel Files for backup recommendations.
• Decide which columns define a duplicate (single column, combination, or entire row).
• Trim whitespace, normalize case, and remove stray characters if you want consistent matching.
• Use Conditional Formatting or COUNTIFS to preview what will be removed.
• Document the rule you applied (which columns, any filters) for future audits.

Common mistakes and how to avoid them

• Removing without a backup: Always copy the sheet or workbook first.
• Ignoring hidden or trimmed differences: Leading/trailing spaces and case differences can cause false uniques; normalize data first.
• Using the wrong columns: Removing duplicates based on too few columns can drop distinct rows; verify which fields constitute a duplicate.
• Assuming order is preserved: Remove Duplicates keeps the first occurrence it finds; sort the data first if you want a specific row retained.
• Not documenting the rule: If someone else must repeat the clean, record which columns and transforms were used.

Quick decision guide

1. Need to delete now and it's one-off? Use Remove Duplicates.
2. Need to inspect first or flag rows? Use Conditional Formatting or COUNTIFS.
3. Need a separate unique list without changing source? Use Advanced Filter.
4. Need repeatable, auditable cleaning across changes? Use Power Query.

Closing

Removing duplicates in Excel is straightforward when you match the tool to the task. If you are unsure, mark duplicates first with formatting or a flag column so you can review before you delete. For repeatable workflows and larger datasets, Power Query is usually the safest and most maintainable option. Before you make irreversible changes, save a copy or follow standard backup practices so you can restore accidentally removed data.