How to Remove Duplicates in Google Sheets (4 Methods, 2026)
Duplicate rows skew counts, totals, and reports. Google Sheets can strip them out in a couple of clicks — or, if you’d rather keep the original data intact, generate a clean de-duplicated copy with a formula. Here are four methods for different situations.
1. Remove Duplicates with the Built-In Tool (Deletes in Place)
The fastest way — best when you want to clean the data directly.
- Select the range that may contain duplicates (include the header row).
- Go to Data > Data cleanup > Remove duplicates.
- Check Data has header row if your selection includes headers.
- Select which columns to analyze:
- Leave all columns checked to remove only fully identical rows.
- Check specific columns (e.g., just Email) to treat rows as duplicates when those columns match, even if other columns differ.
- Click Remove duplicates.
Sheets deletes the extra rows and tells you how many it removed and how many unique rows remain. The first occurrence of each duplicate is kept.
Warning: This edits your data in place. Duplicate the sheet first if you might need the original.
2. Get a Clean Copy with UNIQUE (Non-Destructive)
To keep your source data untouched and produce a de-duplicated list elsewhere, use the UNIQUE function in an empty cell:
=UNIQUE(A2:C100)This outputs every distinct row from the range. Because it’s a live formula, the output updates automatically as the source changes. To de-duplicate a single column:
=UNIQUE(A2:A100)3. Find Duplicates Before Deleting (Conditional Formatting)
To see duplicates first without removing anything:
- Select your range.
- Format > Conditional formatting > Custom formula is.
- Enter:
=COUNTIF($A$2:$A$100, A2) > 1- Pick a fill color and click Done.
Every value that appears more than once is highlighted, so you can review before acting. (More on this in our conditional formatting guide.)
4. Flag Duplicates in a Helper Column (COUNTIF)
To label rows instead of coloring them, add a helper column:
=IF(COUNTIF($A$2:A2, A2) > 1, "Duplicate", "Unique")Because the range start is locked ($A$2) but the end (A2) is relative, this marks the second and later occurrences as “Duplicate” while keeping the first as “Unique” — handy if you then want to filter and delete only the flagged rows.
5. Troubleshooting
Rows that look identical aren’t treated as duplicates
Hidden differences: trailing spaces, different capitalization, or a number stored as text vs. a real number. Clean with TRIM and check that number columns are actually numeric. The built-in tool matches values exactly.
Remove duplicates greyed out / not available
You likely have merged cells in the selection, or no data selected. Unmerge cells and reselect the range.
UNIQUE returns a #REF! error
There isn’t enough empty space for the output to “spill.” Clear the cells below and to the right of the formula.
Related Google Sheets guides: How to use conditional formatting · How to use the QUERY function · How to use VLOOKUP · How to create a pivot table · How to use the IF function
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
Get Carly Today →Or explore our free tools
