Author: Oscar Cronquist Article last updated on May 19, 2022

unique function

The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and also compare columns to columns or rows to rows.

The UNIQUE function is in the Lookup and reference category and is only available to Excel 365 subscribers.

Formula in cell D3:

=UNIQUE(B3:B16)

UNIQUE Function Syntax

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

UNIQUE Function Arguments

Argument Text
array Required. Cell range or array.
[by_col] Optional. Boolean value: True or False.
True - Compares columns.
False (default) - Compares rows.
[exactly_once] Optional. Boolean value: True or False.
True - Unique rows or columns
False - Unique distinct rows or columns.

Table of Contents

1.0 Unique distinct values

unique distinct

Unique distinct values are all values except that duplicate values are merged into one distinct value.

Formula in cell D3:

=UNIQUE(B3:B5)

The UNIQUE function returns unique distinct values if you only use the first argument with a single-column cell range. The second and third arguments are optional.

Why does the UNIQUE function return a #NAME? error?

First, make sure you spelled the UNIQUE function correctly. If the UNIQUE function still returns a #NAME? error you probably own an earlier Excel version and can't use it unless you upgrade to Excel 365.

There is a small formula you can use if you don't have access to the UNIQUE function, check it out here: How to extract unique distinct values from a column [Formula]

Is the UNIQUE function available for Excel 2003, 2007, 2010, 2013, 2016, and 2019 users?

No, only Excel 365 subscribers have it. However, I made a small formula that works fine, check it out here: How to extract unique distinct values from a column [Formula]

Why does the UNIQUE function return a #SPILL! error?

The UNIQUE function returns an array of values and tries to automatically use the appropriate cell range needed to show all values. If one or more cells are occupied with other values the UNIQUE function returns #SPILL! error.

You have two options, delete or move the values that cause the error or deploy the UNIQUE function in another cell that has adjacent cells below empty.

Why does the UNIQUE function return a #CALC! error?

The UNIQUE function returns a #CALC! error if the output result has no values.

Can I use the UNIQUE function with an Excel Table and structured references?

Yes, you can. The UNIQUE function recalculates the output automatically if you add, edit or delete values in the Excel Table. It works fine with filtered Excel Tables as well.

Is the UNIQUE function case sensitive?

No, it is not case sensitive. Read theses articles if you need a case sensitive formula:

Back to top

1.1 Extract unique distinct values sorted from A to Z

unique distinct values sort

Formula in cell D3:

=UNIQUE(SORT(B3:B16))

Check out this article if you own an earlier Excel version and the SORT and UNIQUE functions are not available: Create a unique distinct alphabetically sorted list

Back to top

Explaining formula in cell D3

Step 1 - Sort values

The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])

The first argument is required, the list is sorted from A to Z if the sort order is omitted.

SORT(B3:B16)

becomes

SORT({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "})

and returns

{"Davydenko, Nikolay ";"Del Potro, Juan Martin ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Djokovic, Novak ";"Djokovic, Novak ";"Federer, Roger ";"Federer, Roger ";"Murray, Andy ";"Murray, Andy ";"Roddick, Andy ";"Roddick, Andy ";"Verdasco, Fernando ";"Verdasco, Fernando "}

Step 2 - Extract unique values

UNIQUE(SORT(B3:B16))

becomes

UNIQUE({"Davydenko, Nikolay ";"Del Potro, Juan Martin ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Djokovic, Novak ";"Djokovic, Novak ";"Federer, Roger ";"Federer, Roger ";"Murray, Andy ";"Murray, Andy ";"Roddick, Andy ";"Roddick, Andy ";"Verdasco, Fernando ";"Verdasco, Fernando "})

and returns

{"Davydenko, Nikolay "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Murray, Andy "; "Roddick, Andy "; "Verdasco, Fernando "}

The UNIQUE function may return an array if more than one value is returned. This will make the formula expand automatically to adjacent cells as far as needed.

Back to top

How do I return a unique distinct list sorted from Z to A?

Formula in cell D3:

=UNIQUE(SORT(B3:B16,,-1))

Back to top

1.2 Extract unique distinct values ignoring blanks

unique distinct values blanks

Formula in cell D3:

=UNIQUE(FILTER(B3:B16,B3:B16<>""))

Check out this article if you own an earlier Excel version and the FILTER and UNIQUE functions are not available: Extract a unique distinct list and ignore blanks

Explaining formula in cell D3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:B16,B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

becomes

FILTER{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

{"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}

Step 2 - Extract unique distinct values

UNIQUE(FILTER(B3:B16,B3:B16<>""))

becomes

UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "})

and returns

{"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "}

Back to top

1.3 Extract unique distinct values sorted from A to Z ignoring blanks

unique distinct values sorted and blanks

Formula in cell D3:

=SORT(UNIQUE(FILTER(B3:B16,B3:B16<>"")))

Check out this article if you own an earlier Excel version and the FILTER and UNIQUE functions are not available:
Create a unique distinct sorted list containing both numbers text removing blanks

Explaining formula in cell D3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:B16,B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

becomes

FILTER{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

{"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}

Step 2 - Extract unique distinct values

UNIQUE(FILTER(B3:B16,B3:B16<>""))

becomes

UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "})

and returns

{"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "}

Step 3 - Sort values

The SORT function has the following arguments: SORT(array,[sort_index],[sort_order],[by_col])

The first argument is required, the list is sorted from A to Z if the sort order is omitted.

SORT(UNIQUE(FILTER(B3:B16,B3:B16<>"")))

becomes

SORT({"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "})

and returns

{"Davydenko, Nikolay "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Murray, Andy "; "Roddick, Andy "; "Verdasco, Fernando "}

Back to top

1.4 Count unique distinct values

unique function count distinct values

Formula in cell F3:

=ROWS(UNIQUE(B3:B16))

The formula above works fine if your cell range doesn't contain any blank cells, the formula below takes care of blanks.

Formula in cell F3:

=ROWS(UNIQUE(FILTER(B3:B16,B3:B16<>"")))

The ROWS function counts the number of rows that the UNIQUE function returns, that number is how many distinct values there are in cell range B3:B16.

This article explains a formula that works for earlier Excel versions: Count unique distinct values

Back to top

2.0 Unique values

unique values1

Unique values are values that exist only once in a list. The image above shows a list in column B. Item "AA" has a duplicate and is not unique, however, item "BB" exists only once and is unique.

2.1 Extract unique values

unique values

Formula in cell D3:

=UNIQUE(B3:B15,,TRUE)

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

The third argument takes logical values True or False. True means unique value, in other words, values that only exist once in the list. False means extracting unique distinct values.

There are only two different values that exist once in cell range B3:B15. All other values have duplicates.

Check out this article if you own an earlier Excel version and the UNIQUE function is not available:
How to filter unique values from a list [Formula]

Back to top

2.2 Extract unique values sorted from A to Z

unique values sort

Formula in cell D3:

=SORT(UNIQUE(B3:B14,,TRUE))

Check out this article if you own an earlier Excel version and the UNIQUE function is not available: Filter unique values sorted from A to Z

Explaining formula in cell D3

Step 1 - Extract unique values

The UNIQUE function has the following arguments:
UNIQUE(array,[by_col],[exactly_once])

UNIQUE(B3:B14,,TRUE)

becomes

UNIQUE({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Roddick, Andy "},,TRUE)

and returns

{"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando "}

Step 2 - Sort values

The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])

The first argument is required, the list is sorted from A to Z if the sort order is omitted.

SORT(UNIQUE(B3:B14,,TRUE))

becomes

SORT({"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando "})

and returns

{"Davydenko, Nikolay "; "Federer, Roger "; "Verdasco, Fernando "}

Back to top

2.3 Extract unique values ignoring blanks

unique values blank

The UNIQUE function returns 0 (zero) if there is exactly one blank in the list, however, it disappears if there are two or more blanks in the list.

To remove the blank use the following formula.

Formula in cell D3:

=UNIQUE(FILTER(B3:B16, B3:B16<>""), , TRUE)

Check out this article if you own an earlier Excel version and the UNIQUE function is not available: How to filter unique values from a list [Formula] It works fine with blanks.

Explaining formula in cell D3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:B16,B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}, {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

and returns

{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}

Step 2 - Extract unique values

The UNIQUE function has the following arguments:
UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE)

becomes

UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "},,TRUE)

and returns

{"Federer, Roger ";"Davydenko, Nikolay "}

Back to top

2.4 Extract unique values sorted from A to Z ignoring blanks

unique values blanks and sort

Formula in cell D3:

=SORT(UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE))

Check out this article if you own an earlier Excel version and the UNIQUE function is not available: Filter unique values sorted from A to Z It seems to work fine with blanks.

Explaining formula in cell D3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or critera.

FILTER(B3:B16,B3:B16<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}, {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")

and returns

{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}

Step 2 - Extract unique values

The UNIQUE function has the following arguments: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE)

becomes

UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "},,TRUE)

and returns

{"Federer, Roger ";"Davydenko, Nikolay "}

Step 3 - Sort values

The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])

The first argument is required, the list is sorted from A to Z if the sort order is omitted.

SORT(UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE))

becomes

SORT({"Federer, Roger ";"Davydenko, Nikolay "})

and returns

{"Davydenko, Nikolay"; Federer, Roger "}

Back to top

3.0 Unique distinct rows

unique distinct rows1

Unique distinct rows are all rows except that duplicate rows are merged to one value.

Back to top

3.1 Extract unique distinct rows

unique distinct rows

Formula in cell E3:

=UNIQUE(B3:C6)

Check out this article if you own an earlier Excel version and the UNIQUE function returns a #NAME! error:
Filter unique distinct records

The UNIQUE function recognizes automatically a multicolumn cell range and returns unique distinct rows. The image above shows the UNIQUE function returning three rows, row 4 and 6 are merged into one row.

Back to top

3.2 Extract unique distinct rows ignoring blank rows

unique distinct rows blanks 1

Formula in cell E3:

=UNIQUE(FILTER(B3:C6, (C3:C6<>"") * (B3:B6<>"")))

Check out this article if you own an earlier Excel version and the UNIQUE function returns a #NAME! error:
Filter unique distinct row ignore blanks

Explaining formula in cell E3

Step 1 - Filter non-empty rows

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:C6, (C3:C16<>"") * (B3:B6<>"")

becomes

FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, ({1; 2; 2; ""; 2}<>"")*({"AA"; "BB"; "AA"; ""; "BB"}<>""))

becomes

FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, {1;1;1;0;1})

and returns

{"AA", 1; "BB", 2; "AA", 2; "BB", 2}

Step 2 - Extract unique distinct rows

UNIQUE(FILTER(B3:C16,(C3:C16<>"")*(B3:B16<>"")))

becomes

UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2})

and returns

{"AA", 1; "BB", 2; "AA", 2}

Back to top

3.3 Extract unique distinct rows sorted from A to Z ignoring blank rows

unique distinct rows blanks and sort

Formula in cell E3:

=LET(x, UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

This formula sorts the array based on the first column and then on the second column.

Explaining formula in cell E3

Step 1 - Filter non-empty rows

The FILTER function is available for Excel 365 subscribers, it has the following arguments: FILTER(array, include, [if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>""))

becomes

FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, ({1; 2; 2; ""; 2}<>"")*({"AA"; "BB"; "AA"; ""; "BB"}<>""))

becomes

FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, {1;1;1;0;1})

and returns

{"AA", 1; "BB", 2; "AA", 2; "BB", 2}

Step 2 - Extract unique distinct rows

UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")))

becomes

UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2})

and returns

{"AA", 1; "AA", 2; "BB", 2}

Step 3 - Sort array

The SORTBY function lets you sort a multicolumn cell range based on the order of columns you define.

SORTBY(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 1), , INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 2), )

becomes

SORTBY({"AA", 1; "BB", 2; "AA", 2}, INDEX({"AA", 1; "BB", 2; "AA", 2}, 0, 1), , INDEX({"AA", 1; "BB", 2; "AA", 2}, 0, 2), )

becomes

SORTBY({"AA", 1; "BB", 2; "AA", 2}, {"AA";"BB";"AA"}, , {1;2;2}, )

and returns

{"AA",1;"AA",2;"BB",2}

Step 4 - Shorten formula

The LET function lets you name an expression that is repeated often in the formula, this allows you to shorten the formula considerably.

SORTBY(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 1), , INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 2), )

x - UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE)

LET(x, UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

Back to top

4.0 Unique rows

unique rows

Unique rows are rows that exist only once in a cell range. The image above shows that B4:C4 and B6:D6 are duplicates and are not in the result in cell range E3:F4.

Back to top

4.1 Extract unique rows

unique rows unique function

Formula in cell E3:

=UNIQUE(B3:C6,,TRUE)

The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False. The formula above is entered as a regular formula.

The following array formula works for older Excel versions than Excel 365.

Array formula in cell E3:

=INDEX($B$3:$C$6, SMALL(IF(COUNTIFS($B$3:$B$6, $B$3:$B$6, $C$3:$C$6, $C$3:$C$6)=1, MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))
How to enter an array formula

These steps are for earlier Excel versions.

  1. Copy the above array formula.
  2. Double-press with left mouse button on cell E3.
  3. Paste array formula.
  4. Press and hold CTRL + SHIFT simultaneously.
  5. Press Enter.
  6. Release all keys.

Back to top

4.2 Extract unique rows ignoring blank rows

unique rows ignoring blanks

Formula in cell E3:

=UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), FALSE, TRUE)

The formula above is entered as a regular formula.

The following array formula works for older Excel versions than Excel 365.

Array formula in cell E3:

=INDEX($B$3:$C$6, SMALL(IF(COUNTIFS($B$3:$B$6, $B$3:$B$6, $C$3:$C$6, $C$3:$C$6)=1, MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))
How to enter an array formula
  1. Copy the above array formula.
  2. Double-press with left mouse button on cell E3.
  3. PAste array formula.
  4. Press and hold CTRL + SHIFT simultaneously.
  5. Press Enter.
  6. Release all keys.

Explaining Excel 365 formula in cell E3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:C8,(C3:C8<>"")*(B3:B8<>""))

becomes

FILTER({"AA", 1; "BB", 2; "", ""; "AA", 2; "BB", 2; "", ""},({1; 2; ""; 2; 2; ""}<>"")*({"AA"; "BB"; ""; "AA"; "BB"; ""}<>""))

becomes

FILTER({"AA", 1; "BB", 2; "", ""; "AA", 2; "BB", 2; "", ""}, {1;1;0;1;1;0})

and returns

{"AA", 1; "BB", 2; "AA", 2; "BB", 2}

Step 2 - Extract unique rows

The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False meaning distinct values (not unique).

UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), FALSE, TRUE)

becomes

UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2}, FALSE, TRUE)

and returns

{"AA", 1; "AA", 2}

Back to top

4.3 Extract unique rows sorted from A to Z ignoring blank rows

unique rows ignoring blanks sort

This formula sorts the data set based on the first column (B) and the on the second column (C).

Formula in cell E3:

=LET(x, UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

The formula above is entered as a regular formula.

Explaining formula in cell E3

Step 1 - Filter non-empty values

The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])

The first argument is the cell range to be filtered, the second argument is the condition or criteria.

FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>""))

becomes

FILTER({"AA", 2; "BB", 2; 0, 0; "AA", 1; "BB", 2; 0, 0}, ({2;2;0;1;2;0}<>"")*({"AA";"BB";0;"AA";"BB";0}<>""))

becomes

FILTER({"AA", 2; "BB", 2; 0, 0; "AA", 1; "BB", 2; 0, 0},{1;1;0;1;1;0})

and returns

{"AA", 2; "BB", 2; "AA", 1; "BB", 2}

Step 2 - Extract unique rows

The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False meaning distinct values (not unique).

UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE)

becomes

UNIQUE({"AA", 2; "BB", 2; "AA", 1; "BB", 2}, , TRUE)

and returns

{"AA", 2; "AA", 1}

Step 3 - Sort array

The SORTBY function lets you sort a multicolumn cell range based on the order of columns you define.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 1), , INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 2), )

becomes

SORTBY({"AA", 2; "AA", 1}, INDEX({"AA", 2; "AA", 1}, 0, 1), , INDEX({"AA", 2; "AA", 1}, 0, 2), )

becomes

SORTBY({"AA", 2; "AA", 1}, {"AA"; "AA"}, , {2; 1}, )

and returns

{"AA", 1; "AA", 2}

Step 4 - Shrink formula

The LET function lets you name an expression that is repeated often in the formula, this allows you to shorten the formula considerably.

SORTBY(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 1), , INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 2), )

This part: UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE) is repeated three times in the formula.

x - UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE)

LET(x, UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

Back to top

5.0 Unique and unique distinct columns

unique unique distinct columns

Back to top

5.1 Extract unique distinct columns

unique distinct columns

Formula in cell E3:

=UNIQUE(C2:F3,TRUE)

The formula above is entered as a regular formula.

Back to top

5.2 Extract unique columns

unique columns

Formula in cell E3:

=UNIQUE(C2:F3,TRUE,TRUE)

The formula above is entered as a regular formula.

Back to top

6.0 Get Excel file

Get the Excel file


UNIQUE-functionv2.xlsx

Back to top

 

Excel '365' functions


EXPAND function
Increases a cell range or array by a specified number of columns and rows.
FILTER function
Extracts values/rows based on a condition or criteria.
LAMBDA function
Build custom functions without VBA, macros or javascript.
SEQUENCE function
Creates a list of sequential numbers
SORT function
Sorts values from a cell range or array
SORTBY function
Sorts a cell range or array based on values in a corresponding range or array.
STOCKHISTORY function
Downloads stock prices based on a stock quote
TAKE function
Returns a given number of rows or columns from a 2D cell range or array.
TOCOL function
Rearranges values in a 2D cell range to a single column.
TOROW function
Rearranges values from a 2D cell range to a single row.
UNIQUE function
Returns a unique or unique distinct list.
VSTACK function
Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
WRAPCOLS function
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
WRAPROWS function
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
XLOOKUP function
Search one column for a given value, and return a corresponding value in another column from the same row.
XMATCH function
Searches for an item in an array or cell range and returns the relative position.
Excel Function Reference
Provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas.