UNIQUE function
Quick answer
UNIQUE returns each distinct value in a range once, in the order it first appears.
- Syntax
=UNIQUE(array, [by_col], [exactly_once])- Most common use
=UNIQUE(C2:C7)returns West (Each region once: West, East, North)
Point it at one column for a clean list of names or regions, or at several columns to get each distinct combination. The list is live: add a new customer to the data and it shows up in the result.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- A list of distinct values or rows. It spills.
- Google Sheets
- Available in Google Sheets with the same arguments.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range to remove duplicates from. With several columns, a row only counts as a duplicate if every column matches. |
by_col | Optional | TRUE compares columns instead of rows. The default, FALSE, compares rows. |
exactly_once | Optional | TRUE returns only values that appear exactly once. The default, FALSE, returns every distinct value. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =UNIQUE(C2:C7) | Westspills 3 × 1 | Each region once: West, East, North |
| F2 | =UNIQUE(B2:B7,FALSE,TRUE) | Contosospills 2 × 1 | Customers who ordered only once |
| G2 | =COUNTA(UNIQUE(B2:B7)) | 4 | How many different customers ordered |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order | Customer | Region | Regions | One-time customers | Customers | |
| 1001 | Bluebird Cafe | West | West | Contoso | 4 | |
| 1002 | Northwind | East | East | Tailspin | ||
| 1003 | Bluebird Cafe | West | North | |||
| 1004 | Contoso | North | ||||
| 1005 | Northwind | East | ||||
| 1006 | Tailspin | West |
Practice UNIQUE
From lesson 26, UNIQUE & SORT. Graded instantly, then re-checked on changed data.
Distinct regions
E2, list each region once.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |
| A 0 at the end of the list | The range includes empty cells, which UNIQUE returns as 0. Point it at only the filled rows, or drop blanks first: =UNIQUE(FILTER(C2:C50,C2:C50<>"")). |
| Duplicates that look identical | UNIQUE ignores case but not stray spaces, so "West" and "West " both appear. Clean the column with TRIM first. |
| #NAME? | UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365. In older versions, use Data > Remove Duplicates on a copy of the data. |
Tips
- Wrap it in SORT for an alphabetical list: =SORT(UNIQUE(C2:C7)).
- Feed it two columns, such as B2:C7, to get each distinct customer and region pair.
Questions
How do I count unique values in Excel?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(B2:B7)). If the range has blank cells, filter them out first so the blank isn't counted as a value.
Is UNIQUE case-sensitive?
No. "west" and "West" count as the same value, and the version that appears first is the one returned.
How is UNIQUE different from Remove Duplicates?
Remove Duplicates deletes rows from your data once. UNIQUE leaves the data alone and returns a live list that updates when the data changes.
Lessons that use UNIQUE
Answers that use UNIQUE
In other languages
UNIQUE is UNICOS in Spanish Excel and EINDEUTIG in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .