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

UNIQUE Function – Remove duplicates with formulas

Reading time: 2 minutes
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.

Menu Remove duplicates

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)

Demo of the UNIQUE function

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.

Demo of the UNIQUE function with 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.

Demo of the UNIQUE function in a Table

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)

Extract only Unique values

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.

SPILL Error

8 Comments

  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.

    Reply

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

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

    Reply

    • 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

      Reply

  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?"

    Reply

    • 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

      Reply

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

    unique not work in my excel

    Reply

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

      Try with Excel Online

      Reply

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

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

UNIQUE Function – Remove duplicates with formulas

Reading time: 2 minutes
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.

Menu Remove duplicates

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)

Demo of the UNIQUE function

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.

Demo of the UNIQUE function with 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.

Demo of the UNIQUE function in a Table

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)

Extract only Unique values

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.

SPILL Error

8 Comments

  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.

    Reply

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

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

    Reply

    • 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

      Reply

  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?"

    Reply

    • 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

      Reply

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

    unique not work in my excel

    Reply

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

      Try with Excel Online

      Reply

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

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *