How to Find and Remove Duplicates in Excel

Duplicates are the quietest data error. Nothing looks wrong — every row is a real-looking record — but every count is too high and every total is inflated, usually in one date range where an export ran twice. Before trusting any number from a sheet, check for duplicates. Here are five ways, from fastest-to-see to most-controllable, and a decision guide for what to do once you've found them.

First: what counts as a duplicate?

This matters more than the technique. Two rows can be:

• Exact duplicates — every cell identical. Almost always an export or paste mistake. Safe to remove.
• Key duplicates — same order ID, customer ID, or email, but other cells differ. One might be an update of the other, or two real events sharing an ID. Not safe to remove blindly.
• Fuzzy duplicates — "Acme Corp" and "ACME Corp." or "acme corp ". Real duplicates to a human, different strings to Excel.

Decide which kind you're hunting before you start. The techniques below are grouped by that.

Method 1: Conditional formatting (see them instantly)

Select the column that identifies a record — Order ID, Email — then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every value that appears more than once turns red.

Fast, visual, and non-destructive. Its limits: it works on one column at a time, it treats "10" (text) and 10 (number) as different, and it doesn't tell you how many times something repeats. Use it to answer "do I have a duplicate problem at all?"

Method 2: COUNTIF (count them)

Add a helper column next to your key:

=COUNTIF(A:A, A2)

Every row now shows how many times its ID appears. Filter for values greater than 1 and you're looking at the duplicates and only the duplicates, with their full rows.

Two refinements:

• First occurrence vs. repeats. =COUNTIF($A$2:A2, A2) counts only rows above and including the current one, so the first occurrence shows 1 and repeats show 2, 3… Filter for >1 to see exactly the rows you'd delete.
• Duplicates on two columns (same customer and same date): =COUNTIFS(A:A, A2, E:E, E2).

This is the method to use when you need to decide about duplicates rather than just delete them.

Method 3: Remove Duplicates (delete them)

Select the data → Data → Remove Duplicates. A dialog lists every column with a checkbox.

• All columns checked = remove exact duplicates only. Safe.
• Only Order ID checked = keep the first row for each ID, delete the rest. Excel keeps whichever came first in sheet order and doesn't ask — so sort first (e.g. newest updated date at the top) if you care which one survives.

It reports how many rows were removed. Work on a copy: this is permanent once you save.

Method 4: UNIQUE (list them without deleting)

Excel 365 and 2021:

=UNIQUE(A2:A1000)

spills the distinct IDs into a column. =UNIQUE(A2:A1000, , TRUE) returns only the IDs that appear exactly once — the complement is your duplicate set. Combine with COUNTA to get the distinct count: =COUNTA(UNIQUE(A2:A1000)) versus =COUNTA(A2:A1000); the difference is the number of surplus rows.

Method 5: Pivot table (count by key)

Insert a pivot with the key field in both Rows and Values (as Count). Sort the count descending; anything above 1 is a duplicate, and you see the counts at a glance. Good for large sheets where a helper column would be slow, and for reporting "we found 214 duplicated order IDs" to someone else. See the pivot table tutorial for the mechanics.

Fuzzy duplicates

None of the above catch "Acme Corp" vs "ACME Corp.". Normalize first, in a helper column:

=LOWER(TRIM(SUBSTITUTE(B2, ".", "")))

then run any method on the helper column. For genuinely messy names (typos, "Inc" vs "Incorporated"), Excel's Fuzzy Lookup add-in or Power Query's fuzzy matching are the tools; a formula won't get there.

Deciding what to keep

The one rule: never remove key duplicates without sorting first so the survivor is a deliberate choice.

Ask instead of hunting

If the sheet is an export you're about to analyze, the duplicate check is the first question to ask. Upload it to ChatExcel and ask "are there duplicate order IDs, and how many rows do they add?" — the assistant counts them and shows the affected rows. Follow with "total revenue excluding duplicate order IDs" and you get the corrected number without editing the file. When you need the fix in the workbook itself, "how do I remove these in Excel?" returns the Remove Duplicates steps for your columns.