Check Sheet Logo
Check Sheet

UNIQUE

The UNIQUE formula 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.

Usage

Use the UNIQUE formula with the syntax shown below, it has 1 required parameter and 2 optional parameters:

=UNIQUE(range, by_column, exactly_once)
Parameters:
  1. range (required):
    The range of cells from which to extract unique values.
  2. 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.
  3. 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.

Examples

Here are a few example use cases that explain how to use the UNIQUE formula in Google Sheets.

Removing duplicates

The 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

The 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.

Common Mistakes

UNIQUE not working? Here are some common mistakes people make when using the UNIQUE Google 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.

Missing arguments

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.

Related Formulas

The following functions are similar to UNIQUE or are often used with it in a formula:

  • FILTER

    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.

  • SORT

    The SORT formula 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.

  • COUNTIF

    The COUNTIF formula 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.

Learn More

You can learn more about the UNIQUE Google Sheets function on Google Support.