# UNIQUE function in Excel

> UNIQUE returns each distinct value in a range once, in the order it first appears.

Source: https://spreadsheetpractice.com/functions/unique · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Excel 2021, 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** A list of distinct values or rows. It spills.
- **Google Sheets:** Available in Google Sheets with the same arguments.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=UNIQUE(C2:C7)` | West (spills 3 × 1) | Each region once: West, East, North |
| F2 | `=UNIQUE(B2:B7,FALSE,TRUE)` | Contoso (spills 2 × 1) | Customers who ordered only once |
| G2 | `=COUNTA(UNIQUE(B2:B7))` | 4 | How many different customers ordered |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Order | Customer | Region |  | Regions | One-time customers | Customers |
| 2 | 1001 | Bluebird Cafe | West |  | West | Contoso | 4 |
| 3 | 1002 | Northwind | East |  | East | Tailspin |  |
| 4 | 1003 | Bluebird Cafe | West |  | North |  |  |
| 5 | 1004 | Contoso | North |  |  |  |  |
| 6 | 1005 | Northwind | East |  |  |  |  |
| 7 | 1006 | Tailspin | West |  |  |  |  |

## 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.

## Related functions

- [SORT](https://spreadsheetpractice.com/functions/sort): SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.
- [FILTER](https://spreadsheetpractice.com/functions/filter): FILTER returns only the rows of a range that pass a test, such as every order from the West region.
- [COUNTA](https://spreadsheetpractice.com/functions/counta): COUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text.

## 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.

## Practice

A graded UNIQUE exercise ("Distinct regions") is on [the UNIQUE page](https://spreadsheetpractice.com/functions/unique). It is checked instantly, then re-checked on changed data.

Lessons that use UNIQUE:

- [Lesson 26: UNIQUE & SORT](https://spreadsheetpractice.com/learn/unique-sort) (Pro)
- [Lesson 32: Self-building summary tables](https://spreadsheetpractice.com/learn/summary-tables-unique-sumifs) (Pro)
- [Lesson 44: Capstone: sales report](https://spreadsheetpractice.com/learn/capstone-sales-report) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
