This guide covers everything you need to know about the Google Sheets UNIQUE function, including its definition, syntax, use cases, and how to use it.
What is the UNIQUE Function? How Does It Work?
The UNIQUE function in Google Sheets returns unique rows from a given range, eliminating any duplicates. The primary purpose of this function is to streamline and simplify data analysis by providing a quick and easy way to isolate unique values from a list or range of data.
The UNIQUE function identifies and returns unique rows in the provided source range, discarding duplicates in the process. It’s important to know that the rows are returned in the order in which they first appear in the source range. This means the function does not rearrange or sort the original data but maintains the integrity of the initial data order.
This function is particularly useful when working with large data sets where manually identifying unique values would be time-consuming and prone to errors, enhancing your ability to analyze and interpret data effectively.
UNIQUE Syntax
The syntax and arguments for the function are as follows:
UNIQUE(range, [by_column], [exactly_once])
Each part of the UNIQUE function has a specific role:
- range: This is the data that the function will filter for unique entries. It is a required argument and can be a single cell, a range of cells, or an array constant.
- by_column: This is an optional argument that determines whether the function will filter the data by columns or by rows. If you set this argument to TRUE, the function will filter by columns. If you set it to FALSE or omit it, the function will filter by rows. The default value is FALSE.
- exactly_once: This is also an optional argument that determines whether the function will return only entries that appear exactly once in the range. If you set this argument to TRUE, the function will return only unique entries. If you set it to FALSE or omit it, the function will return all entries, including duplicates. The default value is FALSE.
Here are some usage notes related to the syntax and arguments:
- The range argument must be specified for the function to work. If you don’t specify a range, the function will return a #N/A error.
- If you specify a single cell as the range, the function will simply return the value of that cell, regardless of the values of the by_column and exactly_once arguments.
- The by_column and exactly_once arguments are not case-sensitive. You can enter them in lowercase, uppercase, or a combination of both.
- If you set both the by_column and exactly_once arguments to TRUE, the function will return the unique columns that contain only unique entries.
- If you set the by_column argument to TRUE and the exactly_once argument to FALSE, the function will return all columns, including those with duplicate entries.
- If you set the by_column argument to FALSE and the exactly_once argument to TRUE, the function will return the unique rows that contain only unique entries.
- If you set both the by_column and exactly_once arguments to FALSE, the function will return all rows, including those with duplicate entries.
Examples of How to Use the UNIQUE Function
Here are some practical examples of how to use the UNIQUE function in Google Sheets:
Example #1: Removing Duplicate Entries
Let’s say you have a list of names in column A, and you want to remove the duplicate entries. To do this, you can use the UNIQUE function. In cell B1, you would enter the following formula:
=UNIQUE(A2:A100)
This formula will generate a list of unique names from the range A2 to A100. If there are any duplicate names in this range, the UNIQUE function will remove them, leaving only one instance of each name.
Example #2: Getting Unique Combinations
If you have two columns of data and you want to get the unique combinations, you can use the UNIQUE function. For instance, if you have a list of names in column A and a list of cities in column B, and you want to know the unique combinations of name and city, you would enter the following formula in cell C1:
=UNIQUE(A2:B100)
This formula will return a list of unique combinations of names and cities from the range A2 to B100.
Example #3: Sorting Unique Values
You can also use the UNIQUE function in combination with other functions to further manipulate your data. For example, if you want to get a list of unique names from column A and want this list to be sorted in alphabetical order, you would use the UNIQUE function combined with the SORT function. The formula you would enter in cell B1 is:
=SORT(UNIQUE(A2:A100))
This formula will return a list of unique names from A2 to A100, sorted in alphabetical order. Remember, the UNIQUE function is case-sensitive, so it will treat entries with different capitalization as unique entries.
UNIQUE: Common Mistakes & Problems
When using the UNIQUE function in Google Sheets, there are a number of common mistakes and problems that users often encounter. Understanding these common issues can help you avoid them and use the UNIQUE function more effectively:
- Non-Uniform Data Types: The UNIQUE function can only handle one data type at a time. If you have a range that includes both text and numbers, the function may not work as expected. Make sure that all the data in your range is of the same type.
- Merging Cells: The UNIQUE function does not work with merged cells. If you have any merged cells in your range, you should unmerge them before using the UNIQUE function.
- Using the Function with Large Datasets: The UNIQUE function can slow down your Google Sheets significantly if used with very large datasets. If you notice that your sheet is running slowly, it may be because of the use of this function.
- Empty Cells: The UNIQUE function will also consider empty cells. If you have empty cells in your range, they will be included in the output. If you don’t want empty cells to be included, you need to remove them from your range.
- Not Considering Case Sensitivity: The UNIQUE function is case-sensitive. This means that if you have duplicate values, but they are in different cases (for example, “Apple” and “apple”), the function will consider them as two different values.
- Ignoring the Order: The UNIQUE function outputs the unique values in the order they appear in the original range. If you need the unique values in a specific order, you will have to sort them after using the UNIQUE function.
Why UNIQUE Is Not Working? Troubleshooting Common Errors
If you’re facing issues while using the UNIQUE function in Google Sheets, it’s possible that you’re encountering some common errors. This section will help you identify these errors, understand their causes, and provide solutions to rectify them.
#N/A Error
Cause: This error occurs when the UNIQUE function can’t find any unique values in the range you’ve specified. It’s often due to the range either being empty or all the values within the range are duplicates.
Solution: Make sure you’re applying the UNIQUE function to a range that contains at least one unique value. If you’re sure the range does contain unique values, double-check to ensure you’ve entered the range correctly.
#VALUE! Error
Cause: This error usually appears when the argument given to the UNIQUE function is not a valid range. This could be due to a typo in the range or the range referring to cells that don’t exist.
Solution: Review your formula to ensure the range you’ve specified is valid. Make sure you’ve not made a typographical error and that the range refers to cells that exist on your sheet.
#REF! Error
Cause: This error is usually caused by a reference to a cell that isn’t valid in the context of your formula. For instance, it can occur if your formula is trying to reference a cell that has been deleted.
Solution: Check your formula for any references to cells that don’t exist. If you find any, replace them with valid cell references.
#DIV/0! Error
Cause: This error is less common with the UNIQUE function but can occur if your formula is trying to divide by zero, which is not possible in mathematics.
Solution: Review your formula to ensure it’s not causing any division by zero. If your formula includes any division operations, make sure the denominator is never zero.
#NAME? Error
Cause: This error occurs when Google Sheets doesn’t recognize the name of the function. This is often due to a spelling mistake in the function name.
Solution: Double-check your formula to make sure you’ve correctly spelled “UNIQUE”. The function names in Google Sheets are case-insensitive, but they must be spelled correctly.
#NUM! Error
Cause: This error usually appears when the function encounters an invalid number or numerical operation. With UNIQUE, it can occur if you’re trying to find unique numerical values within a range that contains non-numerical values.
Solution: Check the range you’re applying the UNIQUE function to. If it contains non-numerical values, remove them or alter your range to only include numerical values.
By understanding these common errors, their causes, and their solutions, you should be well-equipped to troubleshoot any issues you encounter while using the UNIQUE function in Google Sheets.
Using UNIQUE With Other Google Sheets Functions
Combining the UNIQUE function with other Google Sheets functions can help you perform more complex data analysis tasks. Here are a few examples of how you can use the UNIQUE function together with other functions:
With COUNTIF
The COUNTIF function can be used with UNIQUE to count the number of times unique values appear in a range.
Usage: =ARRAYFORMULA(COUNTIF(range,UNIQUE(range)))
Example: If you have a list of names and you want to know how many times each name appears in the list, you can use the following formula: =ARRAYFORMULA(COUNTIF(A1:A10, UNIQUE(A1:A10))). This formula will return the count of each unique name in the range A1:A10.
With CONCATENATE
The CONCATENATE function can be used with UNIQUE to merge unique values from a range into one cell.
Usage: =ARRAYFORMULA(CONCATENATE(UNIQUE(range)))
Example: If you have a list of names and you want to merge all unique names into one cell separated by commas, you can use the following formula: =ARRAYFORMULA(CONCATENATE(UNIQUE(A1:A10)&”, “)). This formula will return a single cell with all unique names from the range A1:A10 separated by commas.
With SUMIF
The SUMIF function can be used with UNIQUE to sum values associated with each unique value in a range.
Usage: =ARRAYFORMULA(SUMIF(range,UNIQUE(range),sum_range))
Example: If you have a list of names in column A and associated sales in column B, and you want to know the total sales for each unique name, you can use the following formula: =ARRAYFORMULA(SUMIF(A1:A10,UNIQUE(A1:A10),B1:B10)). This formula will return the sum of sales for each unique name in the range A1:A10.
For more details on the UNIQUE function, check out the official documentation at the Google Docs Editors Help Center.
