UNIQUE function
UNIQUE returns each distinct value in a range once, in the order it first appears. 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 19, 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
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.