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.

 A
1Albert
2Jed
3Martha
4June Bug
5Flipper
6Jed
7Albert
8=UNIQUE(A1:A7)
9 
10 
11 
12 
Shows the starting data and the formula typed into the last cell.
 A
1Albert
2Jed
3Martha
4June Bug
5Flipper
6Jed
7Albert
8Albert
9Jed
10Martha
11June Bug
12Flipper
The first seven rows are the original data, and the last five are the function’s dynamic results. The function is still in cell A8, and you can see it if you select that cell.

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.

  A B
1 Albert Schmalbert
2 Jed Lead
3 Martha Schmartha
4 June Bug Schune Bug
5 Flipper Skipper
6 Jed Schmed
7 Albert Schmalbert
8 =UNIQUE(A1:B7)
9    
10    
11    
12    
13    
Shows the starting data and the formula typed into the last cell
 AB
1AlbertSchmalbert
2JedLead
3MarthaSchmartha
4June BugSchune Bug
5FlipperSkipper
6JedSchmed
7AlbertSchmalbert
8AlbertSchmalbert
9JedLead
10MarthaSchmartha
11June BugSchune Bug
12FlipperSkipper
13JedSchmed
The first seven rows are the original data and the last six rows are the dynamic results of the function. The function is still in cell A8 and you can see it if you select that cell.

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.

Remove duplicates menu
Options for removing duplicates

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

2 thoughts on “UNIQUE Function – Google Sheets”

    • Great question. Maybe add a new column and run the values through the UPPER function. Then use the UNIQUE function on that new column.

      Reply

Leave a Comment