Excel UNIQUE function
Returns the list of values without duplicates. The result spills down. In Spanish Excel it is called UNICOS.
Syntax
English Excel
=UNIQUE(array, [by_col], [exactly_once])
Spanish Excel (Mexico)
=UNICOS(matriz, [por_columna], [exactamente_una_vez])
Spanish Excel (Spain, Latin America)
=UNICOS(matriz; [por_columna]; [exactamente_una_vez])
| Argument | What it is |
|---|---|
| array | The range with duplicates. |
| by_coloptional | FALSE (default) compares rows. |
| exactly_onceoptional | TRUE returns only values that appear once. |
Example
In Excel 365 and 2021 a formula can return several values that "spill" into the cells below. UNIQUE(range) returns the list without duplicates. The cells it spills into must be empty, or you get #SPILL!.
=UNIQUE(B2:B11)
Common mistake
Excel 365 and 2021 only. If a cell the result must spill into is taken, you get #SPILL!.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice UNIQUE
Type the formula in the sheet and check it instantly. Spanish or English function names both work. See all lessons
UNIQUE
In Excel 365 and 2021 a formula can return several values that "spill" into the cells below. UNIQUE(range) returns the list without duplicates. The cells it spills into must be empty, or you get #SPILL!.
In E2, get the list of regions without duplicates. You only type in E2; the rest fills in by itself.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Salesperson | Region | Amount | Regions | ||
| 2 | Carmen | North | 18,500 | |||
| 3 | Jorge | South | 4,200 | |||
| 4 | Lucía | North | 3,900 | |||
| 5 | Andrés | Central | 21,000 | |||
| 6 | Paola | South | 17,800 | |||
| 7 | Ricardo | North | 6,400 | |||
| 8 | Elena | Central | 5,900 | |||
| 9 | Mateo | North | 19,900 | |||
| 10 | Sofía | West | 800 | |||
| 11 | Diego | Central | 4,600 | |||
| 12 |
Tip: while typing, click a cell or drag across several to insert their reference.
Frequently asked questions
- What is UNIQUE called in Spanish?
- In Spanish Excel it is UNICOS. The formula =UNIQUE(B2:B11) is written =UNICOS(B2:B11) (or with commas in Mexico).
- Why does UNIQUE use semicolons in some Excel versions?
- It depends on regional settings. Where the comma is the decimal mark (Spain, much of South America), Excel separates arguments with semicolons. English Excel and Mexican Spanish Excel use commas.
- What is the most common UNIQUE mistake?
- Excel 365 and 2021 only. If a cell the result must spill into is taken, you get #SPILL!.