How to get unique values (remove duplicates) with a formula
✓ Verified in LibreOffice 25.8.7.3Return the distinct values from a column with a formula, dropping duplicates — without the Data > Remove duplicates menu.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =UNIQUE(A2:A9) | Excel 365+. Result spills down automatically. |
| Google Sheets | =UNIQUE(A2:A9) | Native in Google Sheets; spills. |
| LibreOffice Calc | =UNIQUE(A2:A9) | LibreOffice 25.8 (UNIQUE arrived in 24.8; earlier versions return #NAME?). |
How it works
UNIQUE scans the range top-to-bottom and keeps the first occurrence of each value, so duplicates are dropped while original order is preserved. It is a dynamic-array function: type it in one cell and the results spill into the cells below.
Verified, not just documented
We ran =UNIQUE(A2:A6) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned apple, banana, cherry — exactly the expected result. Every formula here is confirmed by actually executing it.