UNIQUE Function – Remove duplicates with formulas

UNIQUE Function – Remove duplicates with formulas
Last Updated on 04/05/2024
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 values without duplicates using 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)

How to use the UNIQUE function

This function also works 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 updated?

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 only.

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 values present once

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.

Related article

10 Comments

  1. John
    10/12/2023 @ 17:36

    Hi there, very nice explanation on this. How about an extension of this to include filtering or criteria on another column? For example, the unique list of Names from Col A when Customer = "ALPHA COMPANY" would be "JOHN"...or the unique list of Names from Col A when Region = "SOUTH" would be JOHN and JENNIFER. Thanks!!

    A B C D
    1 Name Region Customer Product
    2 JOHN SOUTH ALPHA COMPANY PRODUCT A
    3 JENNIFER SOUTH BETA COMPANY PRODUCT A
    4 SARAH NORTH BETA COMPANY PRODUCT D
    5 BOB NORTH BETA COMPANY PRODUCT C
    6 JOHN SOUTH ALPHA COMPANY PRODUCT B

    Reply

    • John
      10/12/2023 @ 17:50

      Figured it out...use "FILTER" also...

      =UNIQUE(FILTER(A2:A6,C2:C6="ALPHA COMPANY"))

      Reply

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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 04/05/2024

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 values without duplicates using 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)

How to use the UNIQUE function

This function also works 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 updated?

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 only.

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 values present once

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.

Related article

10 Comments

  1. John
    10/12/2023 @ 17:36

    Hi there, very nice explanation on this. How about an extension of this to include filtering or criteria on another column? For example, the unique list of Names from Col A when Customer = "ALPHA COMPANY" would be "JOHN"...or the unique list of Names from Col A when Region = "SOUTH" would be JOHN and JENNIFER. Thanks!!

    A B C D
    1 Name Region Customer Product
    2 JOHN SOUTH ALPHA COMPANY PRODUCT A
    3 JENNIFER SOUTH BETA COMPANY PRODUCT A
    4 SARAH NORTH BETA COMPANY PRODUCT D
    5 BOB NORTH BETA COMPANY PRODUCT C
    6 JOHN SOUTH ALPHA COMPANY PRODUCT B

    Reply

    • John
      10/12/2023 @ 17:50

      Figured it out...use "FILTER" also...

      =UNIQUE(FILTER(A2:A6,C2:C6="ALPHA COMPANY"))

      Reply

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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 *