How to Remove Duplicates in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 6 min read · includes 2 free exercises

In short: Select your data, then choose Data > Data cleanup > Remove duplicates. Or leave the data alone and list each row once with =UNIQUE(range). To mark repeats instead of deleting them, use =COUNTIF(A$2:A2, A2)>1.

Two ways to remove duplicates

Which method to use depends on whether you want to change the original data. The menu cleans the selected range itself. The UNIQUE formula builds a separate clean list and leaves the source untouched.

The example

Here is a list in cells A1:B6. The row Ann, Sales appears twice, and Ben appears with two different teams.

AB
1NameTeam
2AnnSales
3BenSupport
4AnnSales
5CaraSales
6BenSales

Way 1: the Remove duplicates menu

Following Google's help page for desktop:

  1. Select the range to check.
  2. Click Data, then Data cleanup, then Remove duplicates.
  3. Choose which columns to include and whether the data has a header row.
  4. Click Remove duplicates.

Google's page says cells with the same value count as duplicates even if their letter case, formatting or formulas differ. Selecting a single cell inside a range checks the whole range. Which columns you include matters: with both columns ticked, only the second Ann, Sales row is removed. With only the Name column ticked, every repeated name goes, including the second Ben, even though his team differs.

Way 2: the UNIQUE formula

To list each distinct row once without touching the source:

=UNIQUE(A2:B6)

This returns four rows: Ann Sales, Ben Support, Cara Sales and Ben Sales. Only the exact repeat of Ann, Sales is dropped, because UNIQUE compares whole rows. UNIQUE keeps the first occurrence of each row, in order. The UNIQUE guide covers the options.

Flag duplicates instead of deleting them

When you want to review repeats first, add a helper column. This formula counts how many times the name in row 4 appears from the top of the list down to that row:

=COUNTIF(A$2:A4, A4)>1

It returns TRUE for row 4, the second Ann. The $ before the row number locks the start of the range, so when you copy the formula down, the range grows one row at a time. The first occurrence always shows FALSE, and every later one shows TRUE.

When duplicates will not go away

Try it yourself

Use the table above (A1:B6). Type your formula into the box, starting with =, then press Check.

Exercise 1: Unique rows

Write a formula that lists each distinct row of A2:B6 once.

Hint: pass both columns as the range.

Exercise 2: Flag the repeat

Write a formula for row 4 that returns TRUE when the name in A4 has already appeared in the list from A2 down. Lock the start of the range.

Hint: COUNTIF(A$2:A4, A4) counts the occurrences so far.

What to learn next

Syntax and rules in this guide were checked against Google's Remove duplicates help page. See all guides.