UNIQUE
The UNIQUE function is used to extract unique values from a range or array in Excel. It eliminates duplicate values and returns only the distinct values present in the input data.
Syntax 🔗
=UNIQUE(array, [by_col])
array | The range or array from which you want to extract unique values. |
by_col (Optional) | A logical value that specifies whether to extract unique values by columns. Defaults to FALSE if omitted. |
About UNIQUE 🔗
In the realm of data manipulation within Excel, the UNIQUE function emerges as a proficient tool for isolating one-of-a-kind values from a dataset. By applying UNIQUE to a designated range or array, you swiftly filter out repetitive entries, leaving behind a refined list of distinct values. This function finds utility in scenarios demanding the segregation of unique elements, fostering clarity and streamlined analysis within spreadsheets.
Examples 🔗
Suppose you have a list of fruits in cells A2:A10 (Apple, Orange, Banana, Apple, Orange, Mango, Pear, Banana, Banana) and you wish to extract the unique fruits without duplicates. The UNIQUE formula would be: =UNIQUE(A2:A10)
In another scenario, you have a 2x3 array in cells B2:D3 (1, 2, 3, 2, 3, 1), and you want to retrieve the unique numbers horizontally. The UNIQUE formula would be: =UNIQUE(B2:D3, TRUE)
Notes 🔗
When using the UNIQUE function, ensure that the input array or range is correctly specified to obtain the desired set of unique values. The function eliminates duplicates and rearranges the output in ascending order by default. You have the flexibility to toggle the orientation of extraction between rows and columns by adjusting the by_col argument.
Questions 🔗
The UNIQUE function efficiently sifts through a dataset and retains only distinct values, discarding any duplicates present within the specified array or range.
Can the UNIQUE function handle multi-column arrays for extracting unique values?Yes, the UNIQUE function can process multi-column arrays for extracting distinct values. By setting the by_col argument to TRUE, you can choose to extract unique values vertically (by columns) rather than horizontally (by rows).
Yes, by default, the UNIQUE function organizes the extracted unique values in ascending order. This helps in presenting the unique elements in a structured and comprehensible format within the spreadsheet.