If you’ve ever worked with long lists in Google Sheets and needed to find out which values appear only once, or just want a cleaner list without duplicates, the UNIQUE function is your new best friend. This simple but powerful formula helps you extract only the distinct values from a column or range, and it updates automatically when your data changes. No more sorting or removing duplicates manually!
Whether you’re sorting names, product categories, email addresses, or anything in between, the UNIQUE function makes your spreadsheets smarter and more efficient. In this guide, we’ll show you exactly how to use it with real examples, practical tips, and a beginner-friendly tone.
What Is the UNIQUE Function?
The UNIQUE function pulls out distinct values from a range of data—removing duplicates and giving you a clean list. It’s especially helpful when summarising data or creating dropdown lists for forms and dashboards.
UNIQUE Function Syntax
=UNIQUE(range)
range: The column or set of cells you want to filter for unique values.
Real-Life Example: Unique Customer Names
Let’s say you run an online business and want to know how many different customers placed orders this month. You have a list of customer names that may include duplicates. With UNIQUE, you can quickly get a list of just the individual names.
Sample Data
| Order ID | Customer Name |
|---|---|
| 001 | Alice |
| 002 | Bob |
| 003 | Alice |
| 004 | David |
| 005 | Ella |
| 006 | Bob |
Goal:
Get a list of unique customer names from the second column.
Formula:
=UNIQUE(B2:B7)
This will return a list of names without any duplicates:
- Alice
- Bob
- David
- Ella
Step-by-Step: How to Use the UNIQUE Function
- Open your Google Sheet and enter your data (or use an existing list).
- Click on an empty cell where you want your unique list to appear.
- Type
=UNIQUE(range)replacing “range” with your actual cell range (e.g.,B2:B100). - Press Enter. The unique list will populate automatically.
Bonus: Use UNIQUE with Multiple Columns
You can also use UNIQUE across multiple columns to get unique rows. Just select the entire range instead of a single column.
Example:
=UNIQUE(A2:B7)
This would return unique combinations of order IDs and customer names.
Common Use Cases for the UNIQUE Function
- Generate a list of unique product names from an inventory list
- Build dropdown menus using only distinct options
- Summarize attendance by extracting unique student names
- Analyze customer activity without counting repeat purchases
Benefits of Using UNIQUE
- No duplicates: Automatically removes repeated values.
- Dynamic updates: Reflects new or changed data instantly.
- Simple to use: One function, no complicated setup.
- Great for summaries: Ideal for dashboards, reports, and forms.
Common Errors to Avoid
- #REF! Error: Happens if there’s not enough space below the formula to display the results.
- Blank rows: These may be counted as unique if they differ from other blanks (due to spaces or invisible characters).
UNIQUE Function Cheat Sheet (Quick Reference)
- Function:
=UNIQUE(range) - What it does: Returns a list of values with duplicates removed
- Best for: Cleaning up lists and summarizing data
- Supports multiple columns: Yes ✅
- Updates automatically: Yes ✅
The UNIQUE function in Google Sheets is a must-know for anyone working with lists. Whether you’re a business owner, a teacher, or just organizing a personal spreadsheet, it saves time and keeps your data neat. It’s one of those simple tools that can make a big difference in how you work with information—and once you use it, you’ll wonder how you ever managed without it!