Skip to content
ExcelExcel 2021+

UNIQUE function in Excel

Returns a list of unique values in a list or range

=UNIQUE(array, [by_col], [exactly_once])

UNIQUE syntax and parameters

Three arguments, one required.

  • arrayarrayRequired

    The range or array from which to return unique rows or columns

  • by_colbooleanOptional

    The by_col argument is a logical value indicating how to compare.TRUE will compare columns against each other and return the unique columnsFALSE (or omitted) will compare rows against each other and return the unique rows

  • exactly_oncebooleanOptional

    The exactly_once argument is a logical value that will return rows or columns that occur exactly once in the range or array. This is the database concept of unique.TRUE will return all distinct rows or columns that occur exactly once from the range or arrayFALSE (or omitted) will return all distinct rows or columns from the range or array

UNIQUE examples

Formulas you'll actually reuse.

  1. The distinct customer list from an orders sheet, in first-seen order:

    =UNIQUE(A2:A200)

  2. Customers who ordered exactly once (exactly_once = TRUE):

    =UNIQUE(A2:A200, FALSE, TRUE)

UNIQUE in Google Sheets

Same name — your formula ports as-is.

Try UNIQUE in the playground

Edit the example — nothing to install.

Preloaded with the UNIQUE formula from Example 1 — change anything and watch it respond.

Loading the editor…

Related functions

More ways Excel gets this done.