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.
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 😀
- Just write the UNIQUE function
- 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)
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.
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.
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)
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.
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!!