The UNIQUE function in Google Sheets can be used to find and return unique values from a range or array. If you only need to identify the recurring values, use our guide covering how to highlight duplicate values in Google Sheets.
This function can be used to identify duplicate values in a data set, or to find unique values across multiple data sets.
=UNIQUE(range, [by_column], [exactly_once])
- range – this is the range of cells that you want to find unique values in;
- by_column – this is an optional argument that specifies whether to filter the data by rows or columns. False by default.
- exactly_once – this is an optional argument that specifies whether the function should return only values with no duplicates. False by default.
By default, the UNIQUE function returns unique values within a specified data set.
1. If you notice that the UNIQUE function doesn’t work and that duplicate values are still returned, check your values for any hidden text – for example, leading or trailing spaces. ” New York” and “New York” are considered to be unique values.
2. Double-check that your numeric values are formatted the same way for the UNIQUE function to process the data as intended – dates as dates, currency as currency, etc.
3. The UNIQUE function returns a #REF error if there’s not enough space for the function to display the output.
How to Apply the UNIQUE Function
To use the UNIQUE function, you need to provide a range or array of values as the first argument. The function will then return an array of unique values from that range.
The second and third arguments are optional and were designed to provide you with more flexibility.
Here is an example of how to use the UNIQUE function to deduplicate a list of US cities we picked to use as a running example.
How to Apply the UNIQUE Function to Return Only Non-Recurring Values
The UNIQUE function will return unique values by default, but you can use the third argument to specify that you only want values that occur exactly once.
This is particularly useful if you want to identify duplicate values in a data set.
For example, if you have a list of values in cells A1 to A11, and you want to find out which values only occur once, you would use the following formula:
The result would be a list of cities that only occur once in the range A1 to A11. In this case, it would be Houston and Phoenix.
If there are no values that meet this criterion, the function will return an empty array.
The UNIQUE function in Google Sheets can be a powerful tool for identifying duplicate values in one or multiple data sets. Armed with this guide, you can deduplicate your data in just a few simple steps.