Use the Remove Duplicates button

Click any cell in your table, go to Data > Remove Duplicates, and a dialog lists every column. Leave all columns ticked to match whole rows, or untick some to dedupe on just a key column like email.

Flag duplicates first with COUNTIF

=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique")

This labels the second and later copies of a value as “Duplicate” so you can review them before deleting anything.

Get a unique list without deleting

=UNIQUE(A2:A100)

In Microsoft 365 the UNIQUE function returns the distinct values into a new range and leaves your original data untouched.

Common mistakes / tips

  • It edits in place. Remove Duplicates permanently deletes rows, so copy the sheet first.
  • Hidden columns still count. Only the columns you tick are compared, so check the right ones.
  • Trailing spaces fool it. “apple” and “apple ” look different; clean text with TRIM first.

Frequently asked questions

How do I remove duplicates based on one column only?

In the Remove Duplicates dialog, untick every column except the one you care about, such as email. Excel then treats two rows as duplicates whenever that single column matches.

Can I undo Remove Duplicates?

Yes, press Ctrl+Z immediately afterwards. Once you save and close, though, the deleted rows are gone, so keep a backup.

Deduplicating large datasets is faster in code. Move beyond spreadsheets with our free Pandas course.