UNIQUE Function – Google Sheets

The UNIQUE function is a quick way to remove duplicate lines from a table of data in a spreadsheet. It is from the group of filter functions available in Google Sheets. The function works from the top of your data down, discarding every row that is a repeat while keeping the first instance of each row in the order it initially occurred. When finished with the function, you end up with a new list of data with the duplicates removed.

Another way of removing duplicates is to create a pivot table. However, using the UNIQUE function is a faster and simpler method of removing duplicates, and your spreadsheet doesn’t leave you with the clutter of a pivot table. However, it is less versatile as a pivot table has hundreds of other uses in addition to just removing duplicates. The UNIQUE function is a one-and-done function that does one thing well.

Video Explanations

Purpose

This function removes duplicate rows in a specified range.

Syntax

=UNIQUE(range)

• range – The rows for the function

Examples

Tip: This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper-left cell.

Example 1

Firstly, we have a simple one-column list with two duplicates.

Example 2

Secondly, we have a two-column list with two duplicates in the first column, one pair of duplicates in the second column. Although Jed is a duplicate, Schmed is not. We want to evaluate both columns for duplicates.

Live Examples in Sheets

Go to this spreadsheet for several examples of the UNIQUE function that you can study and use anywhere you would like.

Remove Duplicates with a Menu Option

As an alternative to UNIQUE, You can also remove duplicates through an option in the menus. If you go to Data, Data cleanup, then Remove duplicates, you get the following menu.

This option does not return a new list. It removes the duplicates from the existing list instead.