The UNIQUE function returns each single value contained in a column (no duplicate).
- Write the UNIQUE function in a cell
The UNIQUE function is available only for Excel Microsoft 365 or Excel Online
- Select your range of cells
No complex technique, you just select your list of values
- [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.
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)
And this function works also 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 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 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.
Stin
Frédéric LE GUEN
Cindy
shafi
Frank
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!!