How to Remove Duplicates in Google Sheets
=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.
| A | B | |
|---|---|---|
| 1 | Name | Team |
| 2 | Ann | Sales |
| 3 | Ben | Support |
| 4 | Ann | Sales |
| 5 | Cara | Sales |
| 6 | Ben | Sales |
Way 1: the Remove duplicates menu
Following Google's help page for desktop:
- Select the range to check.
- Click Data, then Data cleanup, then Remove duplicates.
- Choose which columns to include and whether the data has a header row.
- 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
- Hidden spaces.
"Ann"and"Ann "are different values. Run Data cleanup > Trim whitespace first, or use TRIM. - Different formats. A number and the same number stored as text do not match.
- Wrong columns selected. Decide which columns define a duplicate before you confirm.
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.
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.
What to learn next
- UNIQUE: remove duplicates from a list.
- Count unique values: count distinct items in a range.
- COUNTIF: count the rows that match a condition.
- Highlight duplicates: color repeated values automatically.
- Highlight a row based on a cell: color a whole row when one cell matches.
Syntax and rules in this guide were checked against Google's Remove duplicates help page. See all guides.