How to Remove Duplicates in Excel — 4 Methods for Every Situation

Method 1: Remove Duplicates Tool (Fastest — Permanent Deletion)

Excel's built-in Remove Duplicates feature permanently deletes duplicate rows with one click. Use this when you are confident the duplicates should be deleted and you do not need to review them first.

1. Click anywhere inside your data range (Excel will detect the full range automatically)
2. Go to the Data tab on the ribbon
3. Click Remove Duplicates
4. In the dialog, select which columns to check for duplicates. If all columns must match for a row to be considered a duplicate, select all. If only one column (like an ID) determines uniqueness, select just that column
5. Click OK

Excel shows a summary: how many duplicate rows were removed and how many unique rows remain.

Important: This is irreversible — the rows are deleted immediately. Always work on a copy of your data, or press Ctrl+Z immediately if the result is not what you expected.

Best for: Permanent deduplication, large datasets, when you are confident duplicates should be deleted.

Method 2: UNIQUE Formula (Microsoft 365 — Non-Destructive)

The UNIQUE function extracts a list of unique values into a new location without modifying your original data. This is the best approach when you need to keep the original list intact.

=UNIQUE(A2:A100)

This creates a dynamic array of unique values from column A. As your source data changes, the unique list updates automatically.

For unique rows based on multiple columns:

=UNIQUE(A2:C100)

For unique values sorted alphabetically, combine with SORT:

=SORT(UNIQUE(A2:A100))

Best for: Creating a unique list for dropdowns or reports, when the original data must be preserved, Microsoft 365 users.

Note: UNIQUE is not available in Excel 2019 or earlier.

Method 3: Conditional Formatting Highlight (Review Before Deleting)

Use this method when you want to see which rows are duplicates before deciding whether to delete them.

1. Select the column you want to check for duplicates (e.g., column A)
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
3. Choose a highlight colour (e.g., Light Red Fill) and click OK

All cells containing values that appear more than once are now highlighted. You can review them, then manually delete the rows you want to remove.

To highlight duplicate rows (not just individual cells), use a custom formula rule:

1. Select your entire data range
2. Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format"
3. Enter: =COUNTIF($A$2:$A2,A2)>1
4. Set a fill colour and click OK

This highlights the second and subsequent occurrences of each duplicate, leaving the first occurrence un-highlighted.

Best for: Auditing duplicates before deleting, understanding which values are duplicated.

Method 4: Power Query (Best for Large Datasets and Recurring Deduplication)

Power Query is the most powerful method for removing duplicates — it works on very large datasets, is repeatable, and does not modify your source data.

1. Select your data range
2. Go to Data → From Table/Range (creates an Excel Table if needed and opens Power Query Editor)
3. Select the column(s) that determine uniqueness
4. Right-click the column header → Remove Duplicates
5. Click Close & Load to output the deduplicated data to a new sheet

The Power Query steps are saved. When your source data is updated, click Data → Refresh All and the output table is automatically refreshed and deduplicated again.

Best for: Large datasets (100k+ rows), recurring data imports, when you need a repeatable deduplication process.

How to Find Duplicates Without Removing Them

To count how many times a value appears, use COUNTIF:

=COUNTIF(A:A, A2)

Any result greater than 1 means the value in A2 appears more than once. Add this as a helper column to see counts for all rows at once.

To check if a specific value is a duplicate:

=IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique")

Removing Duplicates Based on Multiple Columns

If a row is only a duplicate when multiple columns match (e.g., same First Name AND Last Name), use the Remove Duplicates tool and select both columns in the dialog. Or with COUNTIFS in a helper column:

=COUNTIFS(A:A, A2, B:B, B2)

Any row where this returns more than 1 is a duplicate based on both column A and column B combined.

Frequently Asked Questions

Which occurrence does Remove Duplicates keep — the first or the last?

Remove Duplicates always keeps the first occurrence and deletes all subsequent duplicates. If you need to keep the last occurrence instead, sort your data in reverse order before removing duplicates, then re-sort to the desired order.

Does Remove Duplicates consider capitalisation?

No — Remove Duplicates is not case-sensitive. "London" and "london" are treated as the same value. If you need case-sensitive deduplication, use a helper column with a formula that normalises case before removing duplicates.

I removed duplicates but my row count is still high. What happened?

This usually means your "duplicates" have subtle differences — extra spaces, different capitalisation, or number vs text type mismatches. Use TRIM and PROPER/UPPER/LOWER to normalise the data in a helper column first, then remove duplicates from that column.