Home » Function » Dynamic Arrays » UNIQUE Function – Remove duplicates with formulas

# UNIQUE Function – Remove duplicates with formulas

Last Updated on 08/12/2021 by Frédéric LE GUEN

The function UNIQUE returns the unique values contained in a column. It is only available only for Excel Microsoft 365 or Excel Online.

## Extract without duplicates manually

In Excel, it has always been possible to extract without duplicated values with the menu Data > Remove Duplicates in the ribbon.

However, if the data is refreshed, you must redo the manipulation again and again and again 😒😕

A long time ago, Mike Girvin, another Microsoft MVP, has created a very complex array formula to extract unique values ​​from a formula. But, in 2018, Microsoft has released the UNIQUE function to simplify the way to return a list of unique values.

## The UNIQUE function

The UNIQUE function is so simple to use 😀

1. Just write the UNIQUE function
2. And select the column containing the values ​​with duplicates.

Immediately, the UNIQUE function returns a list of values corresponding to the values without duplicates.

=UNIQUE(A2:A10)

And this function works also when you select more than 1 column. Here for instance, when we select 2 columns, the function returns unique values based on the content of the 2 columns.

## UNIQUE when your data are refreshed

When your data are inside a Table, the reference is dynamic. That means if you add, or delete a value in the initial column, the result is updated automatically.

## Extract single items

UNIQUE can also extract the values present only once in a list of data. Here, you must fill the third argument of the function.

=UNIQUE(A2:A10,,TRUE)

## UNIQUE can return SPILL error

In some situations, the UNIQUE function can return a SPILL error. This happens when the function hasn't enough cells to return the result.

1. Vannak
05/05/2023 @ 15:54

how would I delete a cell using an Excel's macro? Let's say Column A's has one of the names that matches hte on Column's B; and I want to automatically delete the name in Column A's if it's matched the name on in Column B.

2. Stin
24/03/2023 @ 11:49

How can I use this within a calculation eg using countif but only counting unique values?

• Frédéric LE GUEN
27/03/2023 @ 09:50

I don't understand. If you want to know the unique value, the result is obviously 1. The UNIQUE function returns also the values that are present only once in your list

3. Cindy
29/12/2022 @ 19:16

This would be extremely helpful in a current spreadsheet I am working on but it also does not work in my excel. How do I get it "online?"

• Frédéric LE GUEN
29/12/2022 @ 19:20

You simply need to open your browser on http://www.office.com and login with your credential to have Excel OnLine

4. shafi
18/08/2022 @ 06:00

unique not work in my excel

• Frédéric LE GUEN
18/08/2022 @ 14:44

Try with Excel Online

5. Frank
01/11/2021 @ 02:21

Great content and a novel way of using UNIQUE to automatically delete duplicated values in excel, thankyou!!

# UNIQUE Function – Remove duplicates with formulas

Last Updated on 08/12/2021 by Frédéric LE GUEN

The function UNIQUE returns the unique values contained in a column. It is only available only for Excel Microsoft 365 or Excel Online.

## Extract without duplicates manually

In Excel, it has always been possible to extract without duplicated values with the menu Data > Remove Duplicates in the ribbon.

However, if the data is refreshed, you must redo the manipulation again and again and again 😒😕

A long time ago, Mike Girvin, another Microsoft MVP, has created a very complex array formula to extract unique values ​​from a formula. But, in 2018, Microsoft has released the UNIQUE function to simplify the way to return a list of unique values.

## The UNIQUE function

The UNIQUE function is so simple to use 😀

1. Just write the UNIQUE function
2. And select the column containing the values ​​with duplicates.

Immediately, the UNIQUE function returns a list of values corresponding to the values without duplicates.

=UNIQUE(A2:A10)

And this function works also when you select more than 1 column. Here for instance, when we select 2 columns, the function returns unique values based on the content of the 2 columns.

## UNIQUE when your data are refreshed

When your data are inside a Table, the reference is dynamic. That means if you add, or delete a value in the initial column, the result is updated automatically.

## Extract single items

UNIQUE can also extract the values present only once in a list of data. Here, you must fill the third argument of the function.

=UNIQUE(A2:A10,,TRUE)

## UNIQUE can return SPILL error

In some situations, the UNIQUE function can return a SPILL error. This happens when the function hasn't enough cells to return the result.

1. Vannak
05/05/2023 @ 15:54

how would I delete a cell using an Excel's macro? Let's say Column A's has one of the names that matches hte on Column's B; and I want to automatically delete the name in Column A's if it's matched the name on in Column B.

2. Stin
24/03/2023 @ 11:49

How can I use this within a calculation eg using countif but only counting unique values?

• Frédéric LE GUEN
27/03/2023 @ 09:50

I don't understand. If you want to know the unique value, the result is obviously 1. The UNIQUE function returns also the values that are present only once in your list

3. Cindy
29/12/2022 @ 19:16

This would be extremely helpful in a current spreadsheet I am working on but it also does not work in my excel. How do I get it "online?"

• Frédéric LE GUEN
29/12/2022 @ 19:20

You simply need to open your browser on http://www.office.com and login with your credential to have Excel OnLine

4. shafi
18/08/2022 @ 06:00

unique not work in my excel

• Frédéric LE GUEN
18/08/2022 @ 14:44

Try with Excel Online

5. Frank
01/11/2021 @ 02:21

Great content and a novel way of using UNIQUE to automatically delete duplicated values in excel, thankyou!!