Let's say I have a column of values (e.g., first names). There may be some duplicate data (some first names are very common).

How can I get the unique values?

In SQL I would do


How can I do it in a Google Spreadsheet?

1 Answer 1


In Google Spreadsheets, you can use the UNIQUE() formula to do that.




For the range as seen in the screenshot, there are 10 entries. The UNIQUE() formula accepts a range and filters out the duplicates and returns that range, see screenshot, leaving only 7 unique entries.


enter image description here


  • How does UNIQUE work exactly? In your example you specified the formula only in cell B1, yet values appear in B2, B3, B4, etc.. so clearly the UNIQUE function has some "magic" behaviour in that it alters cells around it even though they don't contain a value or formula.
    – Dai
    Commented Feb 7, 2017 at 2:48
  • @Dai In the past, the cells in which the formula had expanded to (like B2, B3 ... etc), contained formula's as well: CONTINUE(). With the new Sheets this is no longer the case. If it can't expand, then it will through an error: #REF!
    – Jacob Jan
    Commented Feb 7, 2017 at 17:17
  • 1
    This answer is a little misleading in that it shows an ideal case where all unique names appear before a duplicate is seen. Commented Sep 11, 2017 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.