UNIQUE Function – Remove duplicates with formulas

UNIQUE Function – Remove duplicates with formulas
Last Updated on 28/08/2023
Reading time: 2 minutes

The UNIQUE function returns each single value contained in a column (no duplicate).

  1. Write the UNIQUE function in a cell

    The UNIQUE function is available only for Excel Microsoft 365 or Excel Online

  2. Select your range of cells

    No complex technique, you just select your list of values

  3. [optional] Return only single values

    This function can also return only the single value

=UNIQUE(Range of cells,,Option for single value)

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 😒😕

How to use the UNIQUE function

The UNIQUE function is so simple to use 😀

  1. Just write the UNIQUE in a cell
  2. And select the column containing the values ​​with duplicates.

Immediately, the 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.

  1. Write the name of the function in a cell
  2. Select the 2 columns
  3. Validate

As you can see, the result takes in consideration the content of the 2 columns 😯👍

Demo of the UNIQUE function with 2 columns

What happens 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 in the third argument of the function.

=UNIQUE(A2:A10,,TRUE)

Extract only Unique values

Conclusion

The UNIQUE function in Excel efficiently extracts distinct values from a range, streamlining data analysis and reducing duplication.

It simplifies decision-making and enhances insights by providing a consolidated view of unique elements within the dataset, optimizing workflow and enhancing overall productivity.

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 28/08/2023

The UNIQUE function returns each single value contained in a column (no duplicate).

  1. Write the UNIQUE function in a cell

    The UNIQUE function is available only for Excel Microsoft 365 or Excel Online

  2. Select your range of cells

    No complex technique, you just select your list of values

  3. [optional] Return only single values

    This function can also return only the single value

=UNIQUE(Range of cells,,Option for single value)

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 😒😕

How to use the UNIQUE function

The UNIQUE function is so simple to use 😀

  1. Just write the UNIQUE in a cell
  2. And select the column containing the values ​​with duplicates.

Immediately, the 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.

  1. Write the name of the function in a cell
  2. Select the 2 columns
  3. Validate

As you can see, the result takes in consideration the content of the 2 columns 😯👍

Demo of the UNIQUE function with 2 columns

What happens 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 in the third argument of the function.

=UNIQUE(A2:A10,,TRUE)

Extract only Unique values

Conclusion

The UNIQUE function in Excel efficiently extracts distinct values from a range, streamlining data analysis and reducing duplication.

It simplifies decision-making and enhances insights by providing a consolidated view of unique elements within the dataset, optimizing workflow and enhancing overall productivity.

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 *