How do I… · Excel

How do I remove duplicates in Excel?

There are two different jobs here — deleting duplicate rows, and finding them without deleting anything. Do the second one first.

Steps

  1. To see them first: select the column, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Nothing is changed; you just find out how many there are.
  2. To delete them: select any cell in the data, then Data → Remove Duplicates.
  3. In the dialog, tick only the columns that define a duplicate. Ticking every column means "identical in every respect", which is usually not what you meant — two rows for the same customer with different dates are still one customer.
  4. Excel reports how many it removed and how many remain. That count is your check: if it removed far more than the highlight showed, you ticked too few columns.

Worth knowing

Remove Duplicates keeps the first occurrence and deletes the rest, and it cannot be undone once the file is saved and closed — work on a copy the first time. It also ignores case, so "ACME" and "Acme" are one. If you need to keep the rows and just mark them, use =COUNTIF($A$2:$A$999, A2) > 1 in a helper column instead.

Related

Stuck on the next one?

This page is written by the people who make Open Portal: press Ctrl+Shift+O, box any part of your screen, and ask about it. Excel included — it works in every app, because it reads pixels rather than plugging into anything.

Try it here, no install Download free

Faster than searching: box the part of your screen you cannot work out and ask about it directly — in Excel or anywhere else. Answers arrive where you drew the box.

Try it on a screenshot Download free