Skip to content

Excel function · Lookup & reference

Excel UNIQUE function

UNIQUE removes repeated values from a column or table and lists each value once: the customers in an order list, the regions in a sales table. It is available in Microsoft 365 and Excel 2021.

In Turkish Excel: BENZERSİZ

What it does

UNIQUE writes each value of the range once, in the order it first appears, and spills the result below the formula. If you give it several columns, whole rows are compared: the same customer in two different regions stays as two rows.

When the third argument is TRUE, only values that occur exactly once come back, such as customers who ordered just once. When the second argument is TRUE, columns are compared instead of rows, for lists that run across. The comparison is not case-sensitive.

For an alphabetical list, wrap the result in SORT: =SORT(UNIQUE(B2:B8)). To see how often each value occurs, put COUNTIF next to it; for a unique list that meets a condition, apply FILTER first. In Excel 2019 and earlier, Data > Remove Duplicates gives a fixed list.

Syntax

English Excel=UNIQUE(array, [by_col], [exactly_once])
Turkish Excel=BENZERSİZ(dizi; [sütuna_göre]; [yalnızca_bir_kez])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
arrayRequiredThe range to remove repeats from, e.g. B2:B8. With several columns, rows are compared as a whole.
by_colOptionalFALSE or omitted: compares rows (lists running down). TRUE: compares columns (lists running across).
exactly_onceOptionalFALSE or omitted: lists every distinct value once. TRUE: lists only values that occur exactly once.

Examples

1List customers without repeats and count them

UNIQUE in E2 spills each customer name once. COUNTIF in F2 is filled down and counts how many orders each customer placed.

E2fx=UNIQUE(B2:B8)
List customers without repeats and count them: example table
ABCDEF
1DateCustomerAmountCustomerOrders
21-Oct-2026North Market3,200North Market3
33-Oct-2026Atlas Ltd1,850Atlas Ltd2
48-Oct-2026North Market4,100Blue Electric1
512-Oct-2026Blue Electric2,600West Build1
615-Oct-2026Atlas Ltd2,900
721-Oct-2026West Build1,450
827-Oct-2026North Market3,750
  • E2 (the result spills into E2:E5)=UNIQUE(B2:B8)
  • F2 (filled down to F5)=COUNTIF($B$2:$B$8,E2)

Seven orders come from four different customers; the list spills into E2:E5, ending with West Build. North Market placed 3 orders.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Find customers who ordered only once

With the third argument set to TRUE, values that occur more than once are dropped entirely. The second argument is left empty (,,).

E2fx=UNIQUE(B2:B8,,TRUE)
Find customers who ordered only once: example table
ABCDE
1DateCustomerAmountOne-time Customers
21-Oct-2026North Market3200Blue Electric
33-Oct-2026Atlas Ltd1850West Build
48-Oct-2026North Market4100
512-Oct-2026Blue Electric2600
615-Oct-2026Atlas Ltd2900
721-Oct-2026West Build1450
827-Oct-2026North Market3750
  • E2 (the result spills into E2:E3)=UNIQUE(B2:B8,,TRUE)

North Market and Atlas Ltd ordered more than once, so they are left out; only Blue Electric and West Build remain. It is a handy list of customers to follow up with.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#SPILL!

Cause: One of the cells the result spills into is not empty.

Fix: Clear the cells below the formula; the list grows as the number of distinct values grows.

Same value twice

Cause: One of the values has an invisible trailing space, or one is stored as text and the other as a number.

Fix: Clean the source as you pass it in: =UNIQUE(TRIM(B2:B8)).

#CALC!

Cause: exactly_once is TRUE and no value occurs only once.

Fix: Wrap the result in IFERROR and show a message.

Good to know

  • Empty cells in the range show up as 0 in the result; filter them out first: =UNIQUE(FILTER(B2:B50,B2:B50<>"")).
  • To count distinct values, use =ROWS(UNIQUE(B2:B8)) (ROWS).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

List the customers in the order list without repeats and write how many orders each one placed

Convert to table

Bring your order list into the tool and type this. Nexell lists each customer once, writes their order counts next to them with conditional count formulas and shows the result in a preview first.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between UNIQUE and Remove Duplicates?

Data > Remove Duplicates deletes the repeated rows from the source table for good. UNIQUE leaves the source alone; it builds the list without repeats somewhere else with a formula and keeps it up to date as the source changes.

How do I count distinct values in a column in Excel?

In Microsoft 365 and Excel 2021, use =ROWS(UNIQUE(B2:B8)). In older versions, =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8)) gives the same result, as long as the range has no empty cells.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free