Remove Duplicates and Keep One of Each

Excel & Google Sheets

How it works

UNIQUE looks through A2:A20 and returns each distinct value once, in the order it first appears. The results spill automatically into the cells below the formula, so you type it in one cell and get the whole deduplicated list. Your original data is untouched — the formula builds a live copy, which updates itself whenever the source changes. UNIQUE requires Excel 365 or Excel 2021; it works in all versions of Google Sheets. In older Excel, use Data > Remove Duplicates instead (copy your data first, since that tool deletes rows permanently).

UNIQUE

Returns each distinct value from the range exactly once.

A2:A20

The range to deduplicate. Use A2:B20 to dedupe whole rows across two columns.

When to use it

Use this to build a clean master list from messy data: distinct customer names from an order log, departments from an employee export, or products from a sales sheet — especially as the source list for a dropdown or a summary report.

Common mistakes

• ✕Getting #NAME? because your Excel version is too old.

  ✓UNIQUE only exists in Excel 365 and Excel 2021. In older Excel, select the data and use Data > Remove Duplicates — but copy the column first, because that tool deletes the duplicate rows permanently instead of building a separate list.

• ✕Getting #SPILL! because cells below the formula aren't empty.

  ✓UNIQUE needs empty cells below it to spill the results into. Clear anything in the way, or move the formula to an empty column.

• ✕Duplicates survive because of trailing spaces or inconsistent typing.

  ✓"Ana Torres" and "Ana Torres " count as different values, so both survive. Trim before deduplicating: =UNIQUE(TRIM(A2:A20)) in Excel 365, or =ARRAYFORMULA(UNIQUE(TRIM(A2:A20))) in Google Sheets.

• ✕Deleting the original column and breaking the formula.

  ✓UNIQUE is a live reference, not a copy. If you want to delete the source data, first copy the UNIQUE results and use Paste Special > Values to freeze them.