Customer pick list
=SORT(UNIQUE(Orders!B2:B)) builds a sorted distinct buyer list for a dropdown range.
What is UNIQUE in Google Sheets?
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
When to use it
Use UNIQUE to build category lists, find distinct customers, or feed validation dropdowns from live transaction data.
When to skip it
Skip UNIQUE when you need grouped sums per category. Use QUERY GROUP BY or pivot tables. UNIQUE only lists distinct values.
How it works
-
1
Reference the column that may contain repeats, such as Customers!C2:C.
-
2
Write =UNIQUE(C2:C) and let results spill down a new column.
-
3
Pass TRUE as the second argument when comparing entire rows in a table.
-
4
Add by_col when deduping across columns instead of down rows.
-
5
Pair with SORT for alphabetized pick lists on Settings tabs.
-
6
Filter blanks with FILTER(UNIQUE(...), LEN(...)) if empty cells appear in output.
Examples in Google Sheets
Tag discovery
=UNIQUE(FLATTEN(SPLIT(A2:A, ","))) splits comma tags then dedupes for a master tag column.
Count occurrences
=UNIQUE(A2:A, TRUE, TRUE) with counts helps audit how many times each SKU appears.
Build this without starting from a blank cell
Use a Better Sheets tool for UNIQUE, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Including header row in the range, which treats the word Customer as a value.
- Expecting UNIQUE to ignore trailing spaces; TRIM first if imports are messy.
- Spilling UNIQUE over important formulas in cells below.
- Using UNIQUE on whole rows when only one column should dedupe.
- Forgetting UNIQUE refreshes when source adds new values, changing dropdown length.
Frequently asked questions
- UNIQUE vs Remove duplicates?
- UNIQUE is live in a formula. Remove duplicates is a one-time menu action on static data.
- Does UNIQUE preserve order?
- First-seen order by default. Wrap with SORT for alphabetical lists.
- UNIQUE with blanks?
- Blanks may appear once. FILTER them out with LEN or ISBLANK tests.
- UNIQUE on multiple columns?
- Dedupe full rows when the second argument is TRUE.
- UNIQUE for validation?
- Point data validation at the UNIQUE spill range on a Settings tab.
- Why fewer values than expected?
- Hidden duplicates from spaces or case may exist. TRIM and LOWER cleanup helps.
- UNIQUE and QUERY?
- QUERY SELECT DISTINCT is similar. UNIQUE is simpler for one column lists.
Related Tutorials
Watch how UNIQUE works
Duplicate Everything For Each Unique Value
Search Every Function in Google Sheets
Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet
3 More Ways to Count in Google Sheets
Related blog posts
Guides that explain UNIQUE in more depth.
Discover Misspelled Duplicates
The built-in feature can sometimes miss duplicates that have subtle differences, such as variations in spacing or formatting. Our formula-based approach, on the other hand, provides a more granular level of control
Read post →Figure Out Frequency of Numbers
Discover how to figure out the frequency of numbers, product IDs, text, emails and much more! Essential tips and tricks revealed.
Read post →How to Use Google Sheets for Advanced Project Management
Master Google Sheets for project management with these advanced tips. Check our free tutorials, tools, and templates for data management, task flow, and much more.
Read post →Related glossary terms
FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →SORTN
SORTN sorts a range and returns only the first N rows. You choose how many rows, which column drives the sort, and ascending or descending order. Leaderboards and best-seller reports use SORTN instead of manual sorting that goes stale.
Read guide →QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →FLATTEN
FLATTEN converts a two-dimensional range into a single column, reading across rows then down. It is useful after SPLIT or when you need every value from a block in one list for UNIQUE, COUNTIF, or mail merge prep.
Read guide →Done reading about UNIQUE?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.