UNIQUEformula is used to return a list of unique values from a specified range. It is commonly used to remove duplicates from a dataset or to extract a list of unique values from a larger dataset.
- How to use
- Examples of using
UNIQUEformula not working?
- Similar formulas to
UNIQUE formula with the syntax shown below, it has 1 required parameter and 2 optional parameters:
- range (required):
The range of cells from which to extract unique values.
- by_column (optional):
If set to TRUE, the formula will return unique values by column. If set to FALSE or omitted, the formula will return unique values by row.
- exactly_once (optional):
If set to TRUE, the formula will only return values that appear exactly once in the input range. If set to FALSE or omitted, the formula will return all unique values in the input range.
ExamplesHere are a few example use cases that explain how to use the
UNIQUEformula in Google Sheets.
UNIQUE formula can be used to quickly remove duplicates from a dataset, making it easier to analyze and work with.
Extracting unique values
Sometimes you only need to work with a list of unique values from a larger dataset. The
UNIQUE formula makes this easy by extracting only the unique values from a range of cells.
Creating dropdown menus
UNIQUE formula can be used to create dynamic dropdown menus that only display unique options. This is especially useful when working with large datasets or when you want to ensure data consistency.
UNIQUEnot working? Here are some common mistakes people make when using the
UNIQUEGoogle Sheets Formula:
Incorrect range reference
One common mistake is referencing an incorrect range of cells. Double check that the range reference you provide includes all the cells you want to use in your calculation.
Another common mistake is forgetting to include all the necessary arguments. Make sure you have included the range argument and at least one of the by_column or exactly_once arguments.
Incorrect use of by_column and exactly_once
It's important to understand how the by_column and exactly_once arguments work. Make sure you are using them correctly to achieve the desired result.
Mixed data types in range
If the range you are using includes mixed data types (e.g. numbers and text), the UNIQUE formula may not work as expected. Consider using a separate formula to convert the data types before using UNIQUE.
Incorrectly nested formula
If you are using UNIQUE as part of a more complex formula, make sure it is nested correctly and all parentheses are closed properly.
The following functions are similar to
UNIQUE or are often used with it in a formula:
The FILTER formula in Google Sheets is used to filter a range of data based on specified conditions. It returns a new range that only includes the rows that meet the specified conditions. This formula is commonly used to extract specific data from a larger dataset.
SORTformula in Google Sheets is used to sort a range of data based on one or more columns. This function can be used to sort data in ascending or descending order, and can also sort by multiple columns. The sorted data can be used for further analysis or presentation.
COUNTIFformula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.
You can learn more about the
UNIQUE Google Sheets function on Google Support.