2 Ways To Get Unique values From a Range In Excel - Excel Tips and Tricks

Discover 2 ways to get unique values from a range in Excel.

In Excel there is no UNIQUE() function call, however, Excel offers various advanced features to manipulate data, including the powerful "Advanced Filter" function that allows users to filter data by unique values or multiple criteria. Another popular function is the "INDEX-MATCH" formula, which is used to look up and retrieve data from a table, providing more flexibility than the traditional "VLOOKUP" function. By mastering these functions, users can efficiently manage and extract valuable insights from their data in Excel.

1) Advanced Filter
2) Index-Match (Array Formula)

Unique Values Using Advanced Filter
1) Select Dataset
2) Data --- Sort and Filter --- Advanced
3) Copy to another location
4) Copy to is $B$1
5) Unique records only
6) OK

You can easily get unique values from your dataset in Microsoft 365 using a unique function like this.
Likewise, in Google sheet, you can use the unique function like this as well.
However, in the Excel standalone application, unique function is not available.
These are the 2 ways to get UNQIUE values from your dataset in the excel standalone application.

Unique Value Using Index-Match (array formula)
1) Select B2
2) Enter formula
=IFERROR(INDEX($A$2:$A$18, MATCH(0,COUNTIF($B$1:B1, $A$2:$A$18), 0)),"")
3) Ctrl + Shift + Enter
Note the curly braces in your formula which denotes array output.
4) Copy and paste the formula

Count number of occurrence
Use this formula
=COUNTIF(A2:A18,B2)



excel distinct values formula,find unique values in excel multiple columns,excel get unique values from column,extract unique values from excel column using formula without array,unique function in excel,unique function not available in excel,formula to automatically remove duplicates in excel,unique formula in excel 2016,
excel unique values in range formula,excel unique values in range,get unique values from a range in excel,how do i get unique values from different sheets in excel,pull unique values from a list in excel,
excel distinct values formula,excel get unique values from column,unique function in excel,unique function not available in excel,unique formula in excel 2016,find unique values in excel multiple columns,

https://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html