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 showing the UNIQUE function

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
1 Albert
2 Jed
3 Martha
4 June Bug
5 Flipper
6 Jed
7 Albert
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
1 Albert
2 Jed
3 Martha
4 June Bug
5 Flipper
6 Jed
7 Albert
8 Albert
9 Jed
10 Martha
11 June Bug
12 Flipper

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
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
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.
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 Albert Schmalbert
9 Jed Lead
10 Martha Schmartha
11 June Bug Schune Bug
12 Flipper Skipper
13 Jed Schmed

 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.