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.
Contents
Video Explanations
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 | |
1 | Albert |
2 | Jed |
3 | Martha |
4 | June Bug |
5 | Flipper |
6 | Jed |
7 | Albert |
8 | =UNIQUE(A1:A7) |
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
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 |
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 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.

This option does not return a new list. It removes the duplicates from the existing list instead.
HI,
I want to use unique command but unfortunately its case sensitive, so can you plze help me in this.
Great question. Maybe add a new column and run the values through the UPPER function. Then use the UNIQUE function on that new column.