How to remove duplicates in Excel

Excel can delete duplicate rows in two clicks, or keep your original list intact and show a clean copy.

Skip the steps and do it right here: Table cleanerOpen tool

Delete duplicate rows

  1. Click a cell inside your data (or select the range).
  2. Open the Data tab and click Remove Duplicates.
  3. Tick the columns that must match for a row to count as a duplicate.
  4. Click OK. Excel tells you how many duplicates were removed.

Keep the original (Microsoft 365)

The UNIQUE function returns a de-duplicated list without touching your data:

=UNIQUE(A2:A100)

Find duplicates before deleting

  1. Select the column.
  2. Choose Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
  3. Review the highlighted cells.

In Google Sheets

Select the range and choose Data, Data cleanup, Remove duplicates.

Warning: “Remove Duplicates” deletes rows immediately. Work on a copy of the sheet, or press Ctrl + Z straight away if the result is wrong.

Questions

Why are “identical” cells not treated as duplicates?

Trailing spaces and different capitalisation can make cells differ. Clean them with =TRIM(A2) first.

Does it keep the first or last entry?

Excel keeps the first occurrence and removes the later ones.