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