Formula guide · Excel and Google Sheets · Updated May 13, 2026
Remove Duplicates with a Formula
Remove duplicate rows or values dynamically with UNIQUE in Excel or Google Sheets.
Quick Answer
Use UNIQUE when you want a formula-based duplicate removal that updates as the source range changes.
Copyable Formula
=UNIQUE(A2:A100)Syntax
=UNIQUE(range)Excel and Google Sheets
Worked Example
| A | B |
|---|---|
| Raw list | Unique list |
| Acme | Acme |
| Blue Lab | Blue Lab |
| Acme |
Result: Returns one copy of each value from the source list.
Steps
- Select the source range.
- Use UNIQUE to return distinct values.
- Wrap with SORT if the output should be ordered.
- Clean spaces first when duplicates may not be truly identical.
Common Mistakes
- Expecting UNIQUE to change the original source data.
- Ignoring hidden spaces that make duplicates look unique.
- Placing the formula where there is not enough room to spill.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.