UNIQUE function vs Remove Duplicates: live formula or one-time cleanup?
Same goal, opposite philosophies: the Remove Duplicates menu command edits your data once and destructively; the UNIQUE function leaves the source untouched and produces a live deduplicated view that updates itself. Which one you want depends on whether the duplicates are noise to delete or structure to summarize.
The differences at a glance
| UNIQUE | |
|---|---|
| Type | Formula — spills a live result |
| Source data | Untouched; result updates when the source changes |
| Menu equivalent | Data → Remove Duplicates (Excel & Sheets built-in; LibreOffice needs Standard Filter with 'no duplications' — there's no one-click command) |
| Availability (executed) | Excel 365/2021, Google Sheets, LibreOffice 24.8+ (#NAME? in 24.2 — verified) |
| Extras | exactly_once mode returns values appearing only once — the menu command can't do that |
Which should you use?
- UNIQUE (formula) — Dashboards, dropdown sources, and any dedup that must stay current as data grows — plus counting distinct values (wrap in ROWS/COUNTA).
- Remove Duplicates (menu) — One-time cleanup of imported data where the duplicates are genuinely junk — and the only easy option in old Excel or LibreOffice 24.2 where UNIQUE doesn't exist (LibreOffice: Data → More Filters → Standard Filter, check 'No duplications').
Compatibility (from executed tests)
We executed UNIQUE across four LibreOffice releases: #NAME? in 24.2, working from 24.8 onward; it's in Excel 365/2021 and all current Google Sheets. The menu route exists everywhere in some form, so it remains the universal fallback.
Example formulas
| Live deduplicated list | =UNIQUE(A2:A100) |
| Distinct count | =ROWS(UNIQUE(A2:A100)) |
| Values that appear exactly once | =UNIQUE(A2:A100,FALSE,TRUE) |
Full per-version details on each function page: UNIQUE.