How to Remove Duplicates in Excel Without Losing Data

Removing duplicates sounds like a one-click job, and Excel does have a one-click button. The danger is that the button is fast, final and literal. It will remove a different person who shares a surname and city, and it will miss two records for the same person if one has a trailing space. This guide covers the safe procedure.

Three kinds of duplicate

Most "Excel can't find my duplicates" complaints are the second kind. Fix the formatting problems before you search (see how to clean messy Excel data).

Step 1: Decide the key

The key is the column, or combination of columns, that identifies one real-world thing. Choose it before you click anything:

• People: email address is usually the best key. A normalized phone number is next. Name alone is a poor key, because different people share names.
• Products: SKU or barcode.
• Orders and invoices: the order or invoice number.
• Transactions: date plus amount plus reference together.

Step 2: Normalize the key

Build a helper column that produces a comparable version of the key:

=LOWER(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))

For a combination key, join the cleaned parts with a separator that cannot appear in the data, so that "ab"+"c" and "a"+"bc" do not collide:

=LOWER(TRIM(B2))&"|"&LOWER(TRIM(C2))&"|"&TEXT(D2,"yyyy-mm-dd")

Step 3: Mark before you delete

Never start by deleting. Add two helper columns next to your data:

Count:  =COUNTIF($F$2:$F$5000,F2)
Repeat: =IF(COUNTIF($F$2:F2,F2)>1,"duplicate","first")

Here F holds the normalized key. Count shows how many times each key occurs; Repeat labels the first occurrence "first" and later ones "duplicate" (the range $F$2:F2 grows as it is filled down). Filter for Count greater than 1 and read the groups. This is where you spot that two "duplicates" are actually different people, or that the second row holds a newer address worth keeping.

You can also highlight duplicates visually: select the column, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. This is fine for a quick look but does not tell you which row to keep.

Step 4: Decide which record to keep

• Keep the newest. Sort by date descending (select the whole table first), then remove duplicates: Excel keeps the first occurrence it meets, which is the newest.
• Keep the most complete. Add a helper that counts filled fields, =COUNTA(A2:H2), sort by it descending, then remove.
• Merge, don't discard. If one row has the phone number and its twin has the address, copy the missing values across before deleting the twin.

Step 5: Remove

Method A: the Remove Duplicates button

1. Click inside your data. Save a copy of the file first.
2. Choose Data, Remove Duplicates (in the Data Tools group).
3. Tick My data has headers if it does, then tick only the columns that form the key.
4. Click OK. Excel reports how many duplicates were removed and how many unique values remain. Check those numbers against your Count column.

This button is not case-sensitive (Apple and apple count as the same) and it deletes in place, so it cannot be undone after you save and close.

Method B: UNIQUE (Microsoft 365), non-destructive

=UNIQUE(A2:H5000)

This spills a list of unique rows into a new area and leaves your source alone. Add a second argument to compare columns instead of rows, and a third (TRUE) to return only values that occur exactly once.

Method C: Power Query, repeatable

Load the table (Data, Get Data, From Table/Range), select the key columns, choose Remove Rows, Remove Duplicates, and load. Power Query's duplicate removal is case-sensitive, so lowercase the key first (Transform, Format, lowercase). Use it when the same clean-up has to be repeated on new data.

Near-duplicates: Jon vs John

Exact methods cannot see that "Jon Smith, 12 Oak Street" and "John Smith, 12 Oak St" are the same person. There are three practical approaches:

• Match on a stronger key. If both rows share the same normalized email or phone number, they are the same person whatever the name says. Use COUNTIF on that key alone.
• Sort and eyeball groups. Sort by surname and postal code; near-duplicates land next to each other. Fine for hundreds of rows, hopeless for hundreds of thousands.
• Clustering tools. OpenRefine's cluster and edit feature groups values that are probably the same using key-collision and nearest-neighbour methods and lets you merge them with a click. Power Query also offers fuzzy matching in merges.

All fuzzy methods produce candidates, not answers. "Jon" and "John" may be one person or two people in the same household. Treat a similarity score as a prompt to look, never as permission to delete.

Duplicates across two sheets or files

To see which rows of list A already exist in list B, add a helper column on A: =COUNTIF(ListB_key,F2)>0, or in Microsoft 365 =ISNUMBER(XMATCH(F2,ListB_key)). Rows returning TRUE already exist in B. This is the check to run before importing a new list into a CRM.

Pitfalls

• Hidden or filtered rows. Clear filters before removing so you can see what you are deleting.
• Displayed values. Microsoft documents that duplicates are judged by the value displayed in the cell, not the value stored: the same date formatted as 12/8/2017 and as Dec 8, 2017 counts as two different values. Format the key columns the same way first, or build a normalized text key. Formulas are compared by the value they display.
• Merged cells. They break the operation. Unmerge before you start.
• Different IDs for the same entity. Two customer IDs for one person means the problem is upstream. Flag it; do not just delete one.
• Trusting the count. If you expected about 200 duplicates and 4,000 rows vanish, stop and undo.