The UNIQUE function returns each single value contained in a column (no duplicate).
- You can return unique values in rows, just by selecting a range a cells
- Or, in columns
- You can also return values that are present only once in the source.
=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.
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 😀
- Just write the UNIQUE in a cell
- 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)
This function also works when you select more than 1 column.
- Write the name of the function in a cell
- Select the 2 columns
- Validate
As you can see, the result takes in consideration the content of the 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 😀👍
Why I have SPILL Error?
The SPILL error occurs when the function does not nave enough room to return the result.
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)
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.
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
10/12/2023 @ 17:50
Figured it out...use "FILTER" also...
=UNIQUE(FILTER(A2:A6,C2:C6="ALPHA COMPANY"))
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.
24/03/2023 @ 11:49
How can I use this within a calculation eg using countif but only counting unique values?
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
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?"
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
18/08/2022 @ 06:00
unique not work in my excel
18/08/2022 @ 14:44
Try with Excel Online
01/11/2021 @ 02:21
Great content and a novel way of using UNIQUE to automatically delete duplicated values in excel, thankyou!!