Google Sheets has two ways to remove duplicates: the Remove duplicates command, which deletes rows from your data, and the UNIQUE function, which builds a clean copy somewhere else. They look interchangeable, but on the same list they gave different results in our tests, because they don’t agree on what counts as a duplicate. This guide shows both, and what each one kept.
The test list is a set of contacts in A1:D13, with the kinds of near-duplicates real data has:
| A: Name | B: Email | C: City | D: Amount | |
|---|---|---|---|---|
| 2 | Ana Lopez | [email protected] | Madrid | 120 |
| 3 | Ben Ross | [email protected] | Boston | 80 |
| 4 | ana lopez | [email protected] | Madrid | 120 |
| 5 | Ana Lopez | [email protected] | Madrid | 120 |
| 6 | Cara Diaz | [email protected] | Lima | 60 |
| 7 | “Ben Ross ” (a space at the end) | [email protected] | Boston | 95 |
| 8 | Dev Patel | [email protected] | Pune | 200 |
| 9 | Ana Lopez | [email protected] | Seville | 120 |
| 10 | (empty row) | |||
| 11 | Cara Diaz | [email protected] | Lima | 60 |
| 12 | Eli Stone | [email protected] | Leeds | 80 |
| 13 | Dev Patel | [email protected] | Pune | 200 |
Rows 5, 11 and 13 are exact copies of earlier rows. Row 4 differs only in upper and lower case, row 7 only by a space you can’t see, and row 9 by the city.
Tested in Google Sheets. Everything on this page was run on November 14, 2026, and the results below are the ones Google Sheets returned. How we test
Method 1: Data cleanup > Remove duplicates
Select the data, then choose Data > Data cleanup > Remove duplicates. In the dialog, tick “Data has header row” if the first row holds titles, choose which columns to compare, and click Remove duplicates. The steps are in Google’s help page.
We ran the same command on copies of the list through the Google Sheets API, whose documentation describes the same rule as the help page. Comparing all four columns, it removed 4 rows: 4, 5, 11 and 13. These 8 rows were left:
Ana Lopez [email protected] Madrid 120
Ben Ross [email protected] Boston 80
Cara Diaz [email protected] Lima 60
Ben Ross_ [email protected] Boston 95 (with the trailing space)
Dev Patel [email protected] Pune 200
Ana Lopez [email protected] Seville 120
(empty row)
Eli Stone [email protected] Leeds 80
- The first copy is kept and the later ones are deleted. The rows move up to close the gaps.
- Row 4, “ana lopez” with “[email protected]”, was removed. Remove duplicates ignores upper and lower case. Google’s help says so: cells with “different letter cases, formatting or formulas” count as duplicates.
- The empty row stayed. It only had one copy.
Comparing one column deletes data in the others
With only the Name column ticked, the command removed 5 rows, including row 9: Ana Lopez in Seville. Her Madrid row was kept because it came first, and the Seville city and its amount were gone. Only compare a single column if the other columns really are copies too.
Found in testing: “Ben Ross ” with a trailing space survived every Remove duplicates run. After Data > Data cleanup > Trim whitespace, which changed exactly 1 cell, the same name-only comparison removed 6 rows instead of 5, and Ben Ross appeared once. Trim first, then remove duplicates. Trim whitespace doesn’t remove non-breaking spaces, according to Google’s help page; our guide to extra spaces shows how to deal with those.
Remove duplicates deletes rows from your data. If you aren’t sure which columns to compare, run it on a copy of the tab first, or use the helper column further down to see which rows it would affect.
Method 2: UNIQUE, a clean copy that updates
UNIQUE leaves the original data alone and lists each distinct row in another place. In an empty cell:
=UNIQUE(A2:D13)
It returned 9 rows, one more than Remove duplicates:
| Kept by | Remove duplicates | UNIQUE |
|---|---|---|
| “ana lopez” / “[email protected]” (row 4) | Removed | Kept |
| “Ben Ross ” with a trailing space | Kept | Kept |
| Ana Lopez in Seville | Kept | Kept |
| The empty row | Kept | Kept (an empty line in the result) |
Found in testing: UNIQUE treats “Ana Lopez” and “ana lopez” as different values, while Remove duplicates treats them as the same. On a single column, =UNIQUE(A2:A13) listed both spellings. So did COUNTUNIQUE, which returned 7. If you clean the same list both ways and compare the counts, they won’t match.
To make UNIQUE ignore case and stray spaces, clean the values inside the formula:
=UNIQUE(ARRAYFORMULA(LOWER(TRIM(FILTER(A2:A13, A2:A13<>"")))))
This returned 5 names: ana lopez, ben ross, cara diaz, dev patel and eli stone. They come back in lower case, so use it to count or check, not to display. Removing the empty value with FILTER, and listing values that appear only once, are covered in UNIQUE without blanks.
Which one to use
- Remove duplicates for a one-off clean-up of data you own. It’s case-insensitive, deletes rows, and doesn’t update when new rows arrive.
- UNIQUE for a list that should stay clean as data changes, such as a dropdown source or a summary. It’s case-sensitive and never touches the original.
See the duplicates before deleting them
A helper column shows which rows a removal would affect. In E2, copied down:
=COUNTIF($A$2:A2, A2)>1
It returned TRUE on rows 4, 5, 9, 11 and 13: every name that already appeared higher up. The first copy shows FALSE, so filtering the column for TRUE gives exactly the rows to delete. Like Remove duplicates, COUNTIF ignores case, which is why row 4 is TRUE. Row 9 is TRUE too, because only the name is compared.
To compare whole rows, check every column with COUNTIFS:
=COUNTIFS($A$2:A2, A2, $B$2:B2, B2, $C$2:C2, C2, $D$2:D2, D2)>1
It returned TRUE on rows 4, 5, 11 and 13: exactly the four rows Remove duplicates deleted when it compared all the columns. Seville is no longer treated as a copy. To color duplicates instead of flagging them, see how to highlight duplicates.
Count how many duplicates there are
| Formula | Result | What it counts |
|---|---|---|
=COUNTUNIQUE(A2:A13) |
7 | Distinct names, case-sensitive |
=ROWS(UNIQUE(A2:A13)) |
8 | The same plus the empty value |
=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13, A2:A13&"")) |
6 | Distinct names, ignoring case |
=COUNTA(A2:A13)-COUNTA(UNIQUE(FILTER(A2:A13, A2:A13<>""))) |
4 | Extra copies (case-sensitive) |
=COUNTIF(A2:A13, "Ana Lopez") |
4 | Includes “ana lopez” |
=COUNTIF(A2:A13, "Ben Ross") |
1 | Misses “Ben Ross “ |
Three formulas for “how many different names” gave 6, 7 and 8. Each is right for its own definition, so decide first whether case and empty cells matter.