Often, we import data into our spreadsheets from different sources. Whether it's online or from other software, there are bound to be instances where data repeats. In these cases, you will need to clean your data to remove the repeated values, which can become very tedious when you do it manually.

In these cases, using the UNIQUE function in Google Sheets is the intelligent thing to do. This article discusses the UNIQUE function, how to use it, and ways to nest it with other functions.

What Is UNIQUE in Google Sheets?

The UNIQUE function in Google Sheets is a handy function that helps you to find the unique rows in a dataset while discarding any duplicate data.

This function is ideal if you frequently work with a large amount of data. It lets you find the values that appear just once in your spreadsheet. It works great alongside other essential Google Sheets skills and functions. The formula for UNIQUE uses three arguments, but the cell range is the only necessary argument.

The Syntax for UNIQUE in Google Sheets

Here is the syntax to follow to use the UNIQUE function in Google Sheets:

=UNIQUE( range, filter-by-column, exactly-once)

This is what each of the arguments represent:

  • range - this is the cell address or range of the data we wish to perform the function on.
  • filter-by-column - this argument is optional, and we use it to specify whether we would like the data to be filtered by columns or rows.
  • exactly-once - this is an optional parameter as well and determines whether we want to have entries without any duplicates. FALSE means that we wish to include the values that have a duplicate just once. TRUE means we want to eliminate entries with any number of copies.

To use the formula, ensure that you format the numeric values correctly, meaning that percentages should be formatted as percentages, currency as currency, etc.

How to Use the UNIQUE Function in Google Sheets

Here are the steps you need to follow to use the UNIQUE function in Google Sheets:

A basic example of hot to use UNIQUE in Google Sheets
  1. Click on an empty cell where you wish to input the formula.
  2. Type in =UNIQUE( to start the formula.
  3. Type or click and drag over the applicable cell range.
  4. Finish off the formula using a closing bracket.
  5. Press Enter to execute the formula.

Here is another example to demonstrate the UNIQUE formula.

Using the UNIQUE function with two columns

In this example, we are working with two columns instead of one. When we execute the formula in the data, it looks for unique values in the two combined columns rather than looking through them individually.

You can observe this in action with the name Ryan Gosling and Ryan Reynolds. Although both share the same first name, the last name is unique, meaning the two values are separate. Learning this function should also help you to master UNIQUE in Excel too.

Nesting With the UNIQUE Function

Similar to most other functions in Google Sheets, the UNIQUE function can be paired with other formulas to increase its functionality. Here are a few ways in which you can do this.

UNIQUE With SORT

Nesting the UNIQUE function with SORT

We used one of the previous examples here. Using the UNIQUE formula with two columns provides us with expected results. Now we use SORT with UNIQUE to find the unique values in the dataset and sort them in alphabetical order.

UNIQUE With TRANSPOSE

Nesting the UNIQUE function with TRANSPOSE

You can only use the UNIQUE function with vertical data, so you will need to convert the horizontal data to vertical before using the UNIQUE function on it. TRANSPOSE in Google Sheets works similarly to Transpose in Excel. So, you can use it to make your output horizontal.

Make sure to have the necessary space for the data to be displayed before writing the formula. If you wish to convert the data back to its original form, you can double down on using the TRANSPOSE function.

Tips for the UNIQUE Function

Here are a few tips for using the UNIQUE function in Google Sheets:

  • Make sure you give enough space for the process to display the contents of the data set. If the data can’t fit into the space provided, Google Sheets will show a #REF! Error.
  • If you wish to delete all the values that the UNIQUE function displays, clear the initial cell where you input the formula.
  • If you wish to copy the values that the UNIQUE formula returned, first copy them using Ctrl + C or by right-clicking and clicking on and selecting Copy. To paste the values:
    1. Click on Edit
    2. Then on Paste and Paste Special.
    3. Click on Paste values only. The formula will be removed, and only keeps the values.

Wrapping Up the UNIQUE Function

The UNIQUE function is a simple yet convenient function to use in Google Sheets, especially if you work with massive spreadsheets where you have to find a lot of unique values.

You can use this function and plenty of other Sheets features to calculate and impact important business decisions. There's still plenty more to learn about this complex program, don't be afraid to dive into learning more to drive your success.