How to remove duplicates in Excel (5 ways, including near-duplicates)

Before you delete anything

Removing duplicates is destructive, so spend thirty seconds on three decisions first.

1. Work on a copy. Right-click the sheet tab, choose Move or Copy, tick Create a copy. If something goes wrong, the original is still there.
2. Decide what makes a row a duplicate. Two rows with the same customer name aren’t necessarily duplicates — the same customer can order twice. Usually you want to match on an identifier (order number, invoice number, email address) or a combination such as date + amount + reference.
3. Decide which copy to keep. Every method below keeps the first row it meets. If you want the newest record, sort newest-first before removing.

Method 1: the Remove Duplicates button

The fastest option when you’re sure which columns identify a record.

1. Click any cell in your data.
2. Go to Data → Data Tools → Remove Duplicates.
3. Tick My data has headers if row 1 holds column names.
4. Untick every column, then tick only the columns that define a duplicate — for example Order ID. Leave them all ticked to remove only rows that are identical in every column.
5. Click OK. Excel tells you how many duplicates it removed and how many unique values remain.

Method 2: highlight duplicates first

If you want to see duplicates before deciding what to do with them, colour them.

1. Select the column to check, for example the Order ID column.
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Pick a format and click OK.

Every value that appears more than once turns red — including the first occurrence, which you probably want to keep. Conditional formatting works on single values, so to highlight rows that are duplicates across several columns, use the helper column in the next method.

Method 3: flag duplicates with COUNTIFS

A helper column gives you full control: you can filter, review and delete exactly the rows you choose. In a new column next to your data, enter this in row 2 and fill it down:

The range $A$2:$A2 grows as you fill down, so the formula counts how many times the current Order ID has appeared so far. The first occurrence returns FALSE and every repeat returns TRUE.

To define duplicates by several columns, add a pair of arguments per column:

Then turn on a filter (Data → Filter), show only the TRUE rows, check them, select the visible rows and delete them.

COUNTIFS has two quirks to know about: it isn’t case-sensitive, and it treats * and ? in your data as wildcards, so a value like A* can match more rows than you expect.

Method 4: list unique rows with UNIQUE

In Excel 365 and Excel 2021, the UNIQUE function returns a de-duplicated copy of your data without touching the original. Type it in an empty cell:

The result spills into the cells below and to the right, and it updates when the source data changes. Two useful variations:

• =UNIQUE(C2:C500) lists each customer once.
• =UNIQUE(C2:C500, , TRUE) lists only the customers that appear exactly once.

To keep the result as fixed values, copy it and use Paste Special → Values.

Method 5: Power Query for repeat jobs

If you clean the same export every week or month, Power Query records the steps so you can rerun them with one click.

1. Click inside your data and choose Data → From Table/Range.
2. In the Power Query editor, select the columns that identify a duplicate (Ctrl-click to select several).
3. Choose Home → Remove Rows → Remove Duplicates.
4. Click Close & Load. Next month, paste in the new data and choose Data → Refresh All.

Unlike the Remove Duplicates button, Power Query’s comparison is case-sensitive, so ACME and acme stay as two rows unless you add a step that converts the column to lower or upper case first (Transform → Format → lowercase).

Catching near-duplicates

The hardest duplicates are the ones that aren’t exact: Acme Ltd, ACME LTD and Acme Ltd are the same company to you but three different values to Excel. Normalise them in a helper column, then de-duplicate on that column instead.

This replaces non-breaking spaces (common in data copied from websites) with normal spaces, removes extra spaces with TRIM and converts everything to lower case. For phone numbers, strip the formatting so 082 123 4567 matches 0821234567:

For company names, also strip suffixes such as Ltd, Inc and (Pty) with more SUBSTITUTE calls — or let a tool do it. MindySheets’ duplicate finder has a Smart match mode that ignores capitals, spacing, punctuation, common company suffixes and phone formatting, and shows you every group before anything is removed.

Which method should you use?

In Google Sheets

Google Sheets has a built-in tool too: select the range and choose Data → Data cleanup → Remove duplicates, then pick the columns to compare. Data → Data cleanup → Trim whitespace fixes the spacing problems first. The UNIQUE function works the same way as in Excel.

Frequently asked questions