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

Video thumbnail
YouTube Video About The Unique Function
Video thumbnail
YouTube Video About Identifying Duplicates

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.

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

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.

  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

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.

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