How to find duplicate values in a column
✓ Verified in LibreOffice 25.8.7.3Flag every value in a list that appears more than once.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","Unique") | Put it next to the first value and fill down. |
| Google Sheets | =IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","Unique") | Identical. |
| LibreOffice Calc | =IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","Unique") | Identical. |
How it works
COUNTIF counts how many times the current row's value appears in the whole list. A count above 1 means the value is duplicated somewhere, so the IF labels it "Duplicate". The $ signs lock the list range so the formula fills down correctly. In the sample list, "apple" appears twice, so its row is flagged.
Verified, not just documented
We ran =IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","Unique") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned Duplicate — exactly the expected result. Every formula here is confirmed by actually executing it.