UNIQUE Function – Google Sheets

The UNIQUE function is a quick way to remove duplicate lines from your 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 to remove 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 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

⚠️ This function’s output can spill below, and sometimes to the right, of its cell.

Example 1 – One Column with Duplicates

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

Example 2 – Two Columns with Duplicates

Secondly, we have a two-column list with two duplicates in the first column, and 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

You can also remove duplicates through an option in the menus as an alternative to UNIQUE. 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.