How to Remove Duplicates in Excel

Duplicate rows are one of the most common data quality problems in Excel. Whether you're cleaning a CRM export, reconciling financial data, or deduplicating a mailing list — here are the three methods you'll actually use.

Method 1: The built-in Remove Duplicates tool

The fastest option when you just want the duplicates gone.

Step 1. Click anywhere inside your data.

Step 2. Go to Data → Remove Duplicates.

Step 3. Choose which columns to check for duplicates. If you check all columns, Excel only removes rows that are identical in every column. If you check just one column (e.g., Email), it removes any row where that column repeats — keeping the first occurrence.

Step 4. Click OK. Excel tells you how many duplicates were removed and how many unique rows remain.

Method 2: Flag duplicates with COUNTIF first

Use this when you want to review the duplicates before deciding which rows to keep.

Add a helper column with this formula (assuming your data is in column A):

The expanding range $A$2:$A2 is the key. It counts how many times the current value has appeared so far in the list. A result of 1 means it's the first occurrence; 2 or higher means it's a duplicate.

Filter the helper column to show values greater than 1. Review those rows. Then delete them, or sort them to the bottom.

Method 3: UNIQUE function (Microsoft 365)

If you're on Microsoft 365, use UNIQUE to output a deduplicated list without touching the original data:

This spills a clean list into adjacent cells. Add a second argument to control behavior:

• Second argument: FALSE = deduplicate by rows (default), TRUE = by columns
• Third argument: FALSE = remove duplicates (default), TRUE = return only values that appear exactly once

When duplicates are hiding

Duplicates don't always look like duplicates. Common culprits:

• Extra spaces — "Apple" and "Apple " look the same to you but are different to Excel. Fix with =TRIM(A2)
• Different case — "apple" vs "APPLE". Excel's Remove Duplicates tool is case-insensitive, but COUNTIF comparisons can be affected depending on how you use them
• Text vs. numbers — A cell with 42 (number) and 42 (text) are treated as different values

If you're getting unexpected results, add a TRIM + CLEAN formula column and deduplicate that instead of the raw data.

The Griddy way

Deduplication gets complicated fast once you're dealing with fuzzy matches ("J. Smith" vs "John Smith") or multi-column logic (same email but different names). Just describe the problem:

"Remove duplicate customers — same email address, keep the most recent entry by date"

Griddy handles the logic and cleans the data without you writing a single formula.