Skip to content
Klargrid

Remove duplicates in Excel

Excel has a command for it, and it does exactly what you ask: no more. Duplicates written slightly differently slip through, and what was removed is recorded nowhere.

In Excel, step by step

  1. Spot them first

    Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values: you see what would go.

  2. Choose the columns

    Data, Remove Duplicates, then tick the columns that make a duplicate (the customer code alone, or name and date together).

  3. Confirm

    Excel keeps the first occurrence of each value and says how many rows it removed.

Pitfalls

  • "Smith" and "Smith " with a trailing space are not duplicates for Excel: clean first (TRIM).
  • The command always keeps the first row: to keep the most recent, sort from newest to oldest first.
  • Removed rows are kept nowhere: make a copy first.
  • "Smith Ltd" and "SMITH LTD." stay two customers: they are near-duplicates.

Faster, on the whole file

Remove duplicates shows the rows that will go before removing them, optionally ignores case, spaces and accents, keeps the first or last occurrence, and puts the removed rows in a separate sheet. For different spellings of the same company: Near-duplicates.

Frequently asked questions

How do I count duplicates without removing them?
=COUNTIF($A$2:$A$500,A2)>1 copied down returns TRUE on every row whose value appears more than once.