How to Remove Duplicates in Excel – 3 Methods Compared

Duplicate data can distort reports, inflate counts, and waste time. Excel offers several ways to identify and remove duplicate rows, from a one‑click button to advanced formulas and Power Query. The right method depends on the size of your dataset, the columns involved, and whether you need to preserve the original data. This guide covers the most reliable approaches across Excel 2016, 2019, and Microsoft 365.

Microsoft’s built‑in Remove Duplicates tool is the fastest option, deleting duplicate rows while keeping the first occurrence. For more control, formulas like COUNTIF and UNIQUE let you mark or extract unique values. Conditional formatting helps you inspect duplicates visually before taking action, and Power Query provides a repeatable, non‑destructive workflow for large or recurring tasks. Each method has specific strengths, and the following sections explain exactly how and when to use them.

Remove Duplicates Using the Built‑in Excel Tool

The Data tab’s Remove Duplicates button works on a selected range of cells. It deletes entire rows where the values in chosen columns match exactly. By default, Excel keeps the first occurrence and removes later duplicates.

Key insights

• The built‑in Remove Duplicates button is the fastest and easiest for most users, but it permanently deletes data – always work on a copy.
• Remove outlines or subtotals before using the tool; it does not work in PivotTable values areas.
• Formulas (e.g., COUNTIF + IF) give you more control, like marking duplicates without deleting them, or keeping only the first occurrence.
• Conditional formatting is ideal for a visual check before removal, especially when you need to review duplicates across multiple columns.
• For very large datasets or recurring cleanup tasks, Power Query offers a repeatable, non‑destructive way to remove duplicates.
• Always test on a copy of your data to avoid accidental data loss.

Quick‑reference fact table

Remove Duplicates Using Excel Formulas

Formulas give you flexibility when the built‑in tool is too rigid. They can mark duplicates, extract unique values, or help you keep the first occurrence without deleting data permanently.

How to remove duplicates in excel using formula

The COUNTIF function is available in all Excel versions. Insert a helper column with =COUNTIF(A$1:A1, A1) and drag down. Rows with a value greater than 1 are duplicates. Filter that column to see or delete them. For Excel 365 and 2021, the UNIQUE function is simpler: =UNIQUE(A1:C10) returns a dynamic array of unique rows based on all selected columns. According to a Microsoft Community discussion, UNIQUE is recommended for keeping the first email occurrence in a list.

How to delete duplicates in Excel but keep one

The built‑in Remove Duplicates tool always keeps the first occurrence. To explicitly keep one instance using a formula, add a helper column with =COUNTIF(A$1:A1, A1), filter for values equal to 1, and copy the visible rows to a new location. The UNIQUE function achieves the same result automatically in Excel 365. Both methods preserve the first appearance of each value.

Find and Highlight Duplicates Before Removal

Inspecting duplicates visually before deleting them reduces the risk of removing unique rows. Conditional formatting is the standard method, and it works across all Excel versions.

How to find duplicates in Excel

Select the range you want to check. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style (e.g., red fill) and click OK. Excel highlights duplicate values in the selected range. This method works on a single column or across multiple columns if you select an entire row range. If you need a formula to find duplicates, use =COUNTIF(range, cell)>1 in a helper column. TRUE marks duplicates.

How to highlight duplicates in Excel

After applying conditional formatting, you can filter by color to review only highlighted cells. To clear the rules later, go to Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet. Conditional formatting does not alter your data, making it a safe first step before removal. The Excel University tutorial recommends using this method for identification and then following up with the Remove Duplicates button for cleanup.

How to Remove Duplicates in Excel 2016 – Differences Across Versions

The core removal methods have remained consistent from Excel 2016 through Microsoft 365, but a few features have been added over time.

1. Excel 2016: The Remove Duplicates button and Conditional Formatting are present. Power Query is available via the Get & Transform group on the Data tab.
2. Excel 2019: Same layout as 2016. No major changes to duplicate removal functionality. Power Query is integrated into the Data tab.
3. Microsoft 365 (and Excel 2021): The dynamic array UNIQUE function is introduced. It spills unique values and updates automatically. Power Query receives enhanced features.
4. All versions: The step to access Remove Duplicates is identical: select data → Data tab → Remove Duplicates → choose columns → OK. Microsoft Support documents the same procedure for all versions.

What Is Certain and Uncertain About Removing Duplicates in Excel?

When Should You Use Each Method?

The choice depends on the size of your dataset and the complexity of your duplicate detection. For small datasets (under 1 000 rows) with simple duplicate detection on one or two columns, the built‑in Remove Duplicates button is fast and sufficient. For larger data with many columns, Power Query is safer because it does not alter the original data and can be refreshed. If you need custom logic – such as keeping the last occurrence or comparing only part of a cell – formulas are necessary. Conditional formatting is best as a preliminary step to confirm which rows are duplicates before taking destructive action.

Accidentally removing unique rows is a real risk. Always select the right columns in the Remove Duplicates dialog. Uncheck columns that contain data you want to preserve (e.g., price or date) to avoid deleting rows that are unique only in other fields. Microsoft Support advises removing outlines or subtotals before using the tool.

What Do Official Sources Say About Removing Duplicates?

“Use Remove Duplicates when you want to delete duplicate rows in your worksheet.”

Microsoft Support – Find and remove duplicates

“Power Query is better than the Data tab method when you don’t have a primary key, and it supports dynamic refreshes.”

YouTube tutorial (source: Remove Duplicates in Excel – Power Query)

“Conditional formatting is ideal for identification, then manually delete or follow with Remove Duplicates.”

Excel University – 3 Easy Ways to Remove Duplicates

What to Do After Removing Duplicates?

Once duplicates are gone, you may still need to clean further: trim spaces, standardise case, or merge duplicate records. For example, use VLOOKUP to combine duplicate entries with the same identifier, or use SUMIF to consolidate values. For a quick next step, see our guide on Remove Background From Image – Best Free Online Tools (a different type of cleanup) or How to Convert YouTube to MP3 Legally – Safe Methods for 2025 for other digital tasks.

Frequently Asked Questions