When working with data in excel, we see the same data repeat multiple times. In other words, we see duplicate entries in data. If you want to do data analysis the first step you need to do is to remove these duplicate entries. If not then you will end up getting up incorrect results. This lead to incorrect decisions. It will take a lot of time to clear the duplicates manually. There are multiple ways to identify and remove the duplicates. In this article let us see how to remove duplicates using the UNIQUE function in excel.
Difference between UNIQUE and Distinct
Before going to learn Unique function it is important to understand the difference between the terms Distinct and Unique. UNIQUE refers to the item/ value that is occurring only once in the given data set or range or array. On the other hand DISTINCT means list of all the items available in the given data set or range or array.
See the below example to understand the difference between unique and Distinct.
In the above image we see that our range is B3:B18. In the said range only Low1 and Medium values are occurring only once. Rest all values are occurring more than once. Therefore in a unique list we see only 2 values which are not repeating in the range and in a distinct list we see each value in the data set once irrespective of how many times it is present in the range.
What is the UNIQUE function in excel
UNIQUE function is a recent addition to the list of functions in excel. This function identifies the unique values from a range or an array or a data set. This is a dynamic array function. Means it returns an array of values instead of a single value. Dynamic in nature means you do not need to worry about the changes in the selected data set. The results will be automatically updated when any changes are made to the data set
Syntax of UNIQUE function in excel
The Syntax of Unique function is as follows.
=UNIQUE(array,[by_col],[exactly_once]
Arguments of Unique Function
UNIQUE function in excel takes 3 different arguments.
ARRAY : The Range / Array / the Data set from which we require unique values. This is a Mandatory / Required argument.
[By_Col] : This argument is a logical value indicating how to compare. This argument takes 2 values True (denoted by 1) and False ( denoted as 0). This is an optional argument.
- TRUE or 1 – To use when you want to compare data by columns.
- FALSE or 0 – To use when you want to compare data by rows. This is the default value excel use.
[Exactly_Once] : This is also a logical value. Returns the rows or columns “which occur only once“ in the range or data set or array. This argument also takes 2 values True (denoted by 1) and False ( denoted as 0). This is an optional argument.
- TRUE or 1 – Returns the row(s) or column(s) that occur “ONLY ONCE” in the range or array or dataset.
- FALSE or 0 – Returns all the distinct rows or columns in the range or array or dataset. This is the default value excel use.
Examples of using UNIQUE function in Excel
Example 1: UNIQUE with no Arguments
See the below image where the data range is from C2:C17. We use the unique function with out the optional arguments.
=Unique(C2:C17) – returns the values that are present in the given range of C2:C17.
Example 2: UNIQUE with Exactly Once Argument
In the same example used above, Let us see what Unique function returns when the “Exactly Once” argument is selected as True or 1. The function needs to be written as follows.
=Unique(C2:C17,,TRUE) or =Unique(C2:C17,,1) – Both are one and the same. In the given data range we have only 4 values which are coming only once. Oregon, Texas, New Mexico, Arkansas. Hence Unique function returns only these four values.
Example 3: UNIQUE with By Col Argument
By Default, Excel assumes that Unique it should apply Unique function on a Vertical List i.e. by Columns only. We need to tell excel to work on a horizontal list by using the By Col Argument.
=Unique(A2:I2,TRUE) or =Unique(A2:I2,1) – Both are one and the same. In the below image our data range is in horizontal way. Unique function in excel looks for duplicates in the given range horizontally when True is given in “by col” argument. Hence it returns 4 values.
Example 4: UNIQUE with By Col and Exactly Once Arguments
Now let us see how to use Unique function in excel with both by col and exactly once arguments. See the below image. Since there are only 2 values occurring only once in given range Unique function returns only South and north as output.
Example 5: Using UNIQUE in/ with a Table
Let us understand how to Unique function with excel tables. In case if you are not aware of creating and using tables in excel, see the article on tables.
Suggested Reading
What are tables in Excel and Why should you learn to use Tables in Excel
To use Unique function with a table, give the table name directly in the array argument followed by the column name as mentioned below. Remember to include column name in Square Brackets [ ]
=Unique(Table Name[Column Name])
Let us now see the this with an example. In the below example the data table is named as “Sales”. If we need to get unique values for State (Column E) the formula would be as follows. =Unique(Sales[State])
Conclusion
While these are not the only ways to use Unique function in excel. You can also Unique function in combination with various other functions like Filter, Sort etc along with Named Ranges in combination with the Spill operator as well.
What Do you Think of UNIQUE function in Excel. What are the other functions that you want to use or use the Unique function frequently. Share in the comments.
Do Not Forget to Share this with your Friends !!!