A row is rarely a duplicate because one cell repeats. More often it is a duplicate because Name and Email repeat together, while City or Date may differ. This page shows how to remove duplicates in Google Sheets by checking several columns at once, with formulas first and an add-on option at the end.
Select your data, including the header row.
Go to Data > Data cleanup > Remove duplicates.
Tick "Data has header row".
Tick only the columns that define a duplicate, for example Name and Email.
Click Remove duplicates.
Sheets keeps the first matching row and deletes the others. There is no preview before it runs, so work on a copy of the sheet or be ready to press Ctrl+Z. Check how it treats upper and lower case, and trailing spaces, on a few rows of your own data before trusting it on the whole sheet.
If you want to see the duplicates before anything is deleted, use the formulas below.
All examples use this table in A1:C6 (header in row 1):
Name, Email, City
Priya Shah, priya@example.com, Sydney
Tom Lee, tom@example.com, Perth
Priya Shah, priya@example.com, Melbourne
"priya shah " (with a trailing space), PRIYA@example.com, Sydney
Tom Lee, tom@example.com, Perth
Here a duplicate means the same Name and Email. City is ignored.
Put this in D2 and fill down. It counts how many times this Name and Email pair has appeared so far, from the top of the list to this row:
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>1,"Duplicate","Unique")
The first time a pair appears, the count is 1, so the row is Unique. Every repeat counts 2 or more, so it is a Duplicate. On the sample, rows 2 and 3 are Unique and row 4 is a Duplicate. Row 6 is also a Duplicate. Row 5 shows Unique, because its name has a trailing space and COUNTIFS does not ignore it. Method 3 fixes that.
Filter column D on "Duplicate" to review them, then delete the rows.
If you would rather build a new list than delete from the old one, UNIQUE returns each distinct row once. To compare every column:
=UNIQUE(A2:C6)
To compare only Name and Email, give it just those two columns:
=UNIQUE(A2:B6)
The result spills into new cells and your original data stays untouched. It only returns the columns you pass in, so you lose City in the second version. If you need City back, use Method 4.
When the same person is typed slightly differently, build a combined key that is trimmed and lower-cased. In D2:
=LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))
Then in E2:
=IF(COUNTIF(D$2:D2,D2)>1,"Duplicate","Unique")
The "|" separator stops two different pairs from accidentally producing the same key. Now row 5 is caught: "priya shah |PRIYA@example.com" cleans to the same key as row 2. On the sample, E2 and E3 are Unique, and E4, E5 and E6 are Duplicate.
Once column E is filled, this copies the first occurrence of each pair, with all columns, into a clean list elsewhere:
=FILTER(A2:C6, E2:E6="Unique")
On the sample that returns the Priya Shah row from Sydney and the first Tom Lee row.
The formulas above keep the first occurrence, because the count runs from the top down. To keep the last occurrence instead, count from the current row to the bottom:
=IF(COUNTIF(D2:D$6,D2)>1,"Duplicate","Unique")
A row is a Duplicate if the same key appears again further down. On the sample, only rows 5 and 6 come out Unique.
Select A2:C100, go to Format > Conditional formatting, choose "Custom formula is", and enter:
=AND($A2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1)
This colours every row whose Name and Email appear more than once, including the first copy. On the sample, rows 2, 3, 4 and 6 are highlighted.
To leave the first copy uncoloured and highlight only the repeats, change the first range so it grows as you go:
=AND($A2<>"",COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1)
The most common mistake is the dollar signs. The column letters need a dollar sign ($A, $B) so the rule always looks at the same columns. In the first formula both ends of the range are locked. In the second, only the start is locked, so the range grows down the sheet. If your highlighting looks random, check these first.
Trailing and double spaces make two values look different. Use Data > Data cleanup > Trim whitespace, or the key column in Method 3.
Upper and lower case are treated as the same by COUNTIF and COUNTIFS, so "PRIYA@example.com" matches "priya@example.com".
Blank rows: the AND($A2<>"" ...) guard stops empty rows from being flagged as duplicates of each other.
Merged cells break sorting and filtering. Unmerge them first.
Formulas count text exactly. "Jon Smith" and "John Smith" are different, so near-duplicates need fuzzy matching, which is a separate job.
Formulas are fine for a few hundred rows. On large sheets they get slow, and deleting rows by hand is easy to get wrong.
Disclosure: Snakbacks, the publisher of this site, makes a free add-on called Duplicate Remover: Remove Duplicates in Sheets. You tick the columns that should match, choose whether to ignore case and extra spaces, and then pick what happens to the duplicates:
Highlight them, so you can review first.
Mark them, in a new "Duplicate?" column you can filter.
Copy them out to a new sheet and leave your data untouched.
Delete them, keeping the first or last copy. Undo works with Ctrl+Z or Version history.
It runs inside your sheet and can only see the spreadsheet you open it in. It is coming soon to the Google Workspace Marketplace.
For near-duplicates such as Jon Smith and John Smith, which exact-match formulas miss, Snakbacks also sells Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. FUZZYSCORE(a, b) gives a 0 to 1 similarity you can filter on.
Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.