How to find unique values in Excel - formula examples | Excel UNIQUE function - tips and notes
Exel Training Video For Absolute Beginner Number 1
#excel #microsoft #microsoftexcel #office #word #o #powerpoint #business #cursodeexcel #data #msexcel #curso #cursoexcel #powerbi #exceltips #motivation #datascience #microsoftoffice #n #aprenderexcel #planilhas #dicasexcel #windows #dashboard #cursos #ado #exceltraining #like #excelbasico #dataanalytics
#ktm #online #spreadsheets #smallbusiness #accounting #training #dataanalysis #finance #software #media #vespa #dataentry #python #businessintelligence #spreadsheet #datavisualization #truquesexcel #technology #excelavan #contabilidad #empresarios #covid #vba #studentlife #vespaexcel #learning #education #exc #bhfyp #r
How to find unique values in Excel - formula examples
Extract unique values that occur only once
Find distinct values that occur more than once
Find unique values in multiple columns (unique rows)
Get a list of unique values sorted in alphabetical order
The UNIQUE function in Excel returns a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc.
The function is categorized under Dynamic Arrays functions. The result is a dynamic array that automatically spills into the neighboring cells vertically or horizontally.
The syntax of the Excel UNIQUE function is as follows:
UNIQUE(array, [by_col], [exactly_once])
The below examples show some practical uses of the UNIQUE function in Excel. The main idea is to extract unique values or remove duplicates, depending on your viewpoint, in the simplest possible way.
If you are pursuing an opposite goal, i.e. are looking to get a list of values that appear in a given range more than one time, then use the UNIQUE function together with FILTER and COUNTIF:
How this formula works:
At the heart of the formula, the FILTER function filters out duplicate entries based on the count of occurrences, returned by the COUNTIF function. In our case, the result of COUNTIF is this array of counts:
Find unique values in multiple columns (unique rows)
In situation when you want to compare two or more columns and return the unique values between them, include all the target columns in the array argument.
For instance, to return the unique First name (column A) and Last name (column B) of the winners, we enter this formula in E
Get a list of unique values sorted in alphabetical order
How do you usually alphabetize in Excel? Right, by using the inbuilt Sort or Filter feature. The problem is you need to re-sort every time your source data changes, because unlike Excel formulas that recalculate automatically with every change in the worksheet, the features have to be re-applied manually.
With the introduction of dynamic array functions this problem is gone! What you need to do is simply warp the SORT function around a regular UNIQUE formula, like this:
Find unique values in multiple columns and concatenate into one cell
When searching in multiple columns, by default, the Excel UNIQUE function outputs each value in a separate cell. Perhaps, you'll find it more convenient to have the results in a single cell?
To achieve this, instead of referencing the entire range, use the ampersand (&) to concatenate the columns and put the desired delimiter in between.
As an example, we are concatenating the first names in A2:A10 and the last names in B2:B10, separating the values with a space character (" "):
=UNIQUE(A2:A10&" "&B2:B10)
As the result, we have a list of full names in one column:
Get a list of unique values based on criteria
To extract unique values with condition, use the Excel UNIQUE and FILTER functions together:
The FILTER function limits the data only to values that meet the condition.
The UNIQUE function removes duplicates from the filtered list.
Here's the generic version of the filtered unique values formula: