The UNIQUE function is a quick way to remove duplicate lines from a table of data in a spreadsheet. 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 in which it originally 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 you aren't left with the clutter of a pivot table. However, it 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 but does it well.

When using UNIQUE, start your formula in a cell that will be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the cell where you typed it. If you place your cursor in that cell, the formula can be changed.

Video Explanation

Purpose

Removes duplicate rows in a specified range.

Syntax

=UNIQUE(range)

  • range - The rows that are to be included in the function

Examples

Tip: The contents of every cell in a row have to match all of the contents in another row in order for the row to be considered a duplicate.

Example 1

A simple one-column list with two duplicates.

Shows the starting data and the formula typed into the last cell.
A
1Albert
2Jed
3Martha
4June Bug
5Flipper
6Jed
7Albert
8=UNIQUE(A1:A7,a1:a4<4)
9
10
11
12
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.
A
1Albert
2Jed
3Martha
4June Bug
5Flipper
6Jed
7Albert
8Albert
9Jed
10Martha
11June Bug
12Flipper

Example 2

Two-column list with two duplicates in first column, one pair of duplicates in the second column. Although Jed is a duplicate, Schmed is not. Both columns need to be duplicates.

Shows the starting data and the formula typed into the last cell
AB
1AlbertSchmalbert
2JedLead
3MarthaSchmartha
4June BugSchune Bug
5FlipperSkipper
6JedSchmed
7AlbertSchmalbert
8=UNIQUE(a1:b7)
9
10
11
12
13
14
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.
AB
1AlbertSchmalbert
2JedLead
3MarthaSchmartha
4June BugSchune Bug
5FlipperSkipper
6JedSchmed
7AlbertSchmalbert
8AlbertSchmalbert
9JedLead
10MarthaSchmartha
11June BugSchune Bug
12FlipperSkipper
13JedSchmed

 Live examples in Sheets 

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