Google Sheets provides several methods to find and remove duplicate values — from the built-in Remove Duplicates feature to formulas and Apps Script for more complex deduplication requirements.
Method 1: Data > Data Cleanup > Remove Duplicates
Select your data range including headers. Go to Data → Data Cleanup → Remove Duplicates. Choose whether your data has a header row and which columns to check for duplicates. Click Remove Duplicates. Google Sheets removes all rows where the selected columns have identical values, keeping the first occurrence.
Method 2: UNIQUE Function
The UNIQUE function returns a deduplicated list without modifying the original data: =UNIQUE(A2:A100). This is non-destructive — the original range is unchanged and the unique values appear in a new column. UNIQUE also works on multi-column ranges, treating each row as a unit.
Method 3: COUNTIF to Highlight Duplicates
Before removing duplicates, highlight them to review: in a helper column, enter =COUNTIF($A$2:$A$100,A2)>1. This returns TRUE for any value that appears more than once. Use conditional formatting based on this column to visually highlight all duplicate rows for review before deletion.
Remove Duplicate Lines from Text Data
For deduplicating text lists outside of Google Sheets — keyword lists, email addresses, product names — Format Pilot’s text utilities include a remove duplicate lines feature. Paste your list, click Remove Duplicates, and copy the clean result.
Frequently Asked Questions
Does Remove Duplicates in Google Sheets keep the first or last occurrence?
Google Sheets keeps the first occurrence of each duplicate and removes all subsequent ones. If the order matters and you need to keep the last occurrence instead, sort your data in reverse order first, run Remove Duplicates, then re-sort in the original order.