How to remove duplicates in Google Sheets (4 methods)
Whether you're cleaning up an imported customer list, deduplicating a survey response export, or preparing data for analysis, removing duplicates is one of the most common Sheets tasks. This guide covers 4 methods: the built-in Remove Duplicates feature (fastest), the UNIQUE function (non-destructive), conditional formatting (visual detection), and COUNTIF (find duplicates before deleting).
⚡ Quick Answer
Select your data range, go to Data → Data cleanup → Remove duplicates. Choose which columns Sheets should check for matches (all columns by default), then click Remove duplicates. Sheets deletes duplicate rows and shows a summary of how many were removed.
📋 4 methods in this guide
- Built-in Remove Duplicates feature Fastest
- UNIQUE function Non-destructive
- Highlight duplicates with conditional formatting Visual
- Find duplicates with COUNTIF first Safest
Built-in Remove Duplicates feature
The fastest way — takes about 10 seconds. Google Sheets has a native feature that permanently deletes duplicate rows from a range. Best when you're sure you want the duplicates gone and don't need to review them first.
Select your data range
Click and drag to highlight all rows including the header. If your entire sheet is one dataset, click any cell within it and press Ctrl+A to select the whole range.
Open Data Cleanup
A dialog opens with duplicate removal options.
Confirm header row
Check the Data has header row box if your first row contains column names. This prevents Sheets from treating your header as a data row.
Choose which columns to check
By default, all columns are checked — meaning Sheets only removes rows that are duplicates across ALL columns. If you want to deduplicate based on specific columns (like just Email, ignoring differences in Name or Phone), uncheck the columns you don't want considered.
Click Remove duplicates
Sheets removes matching duplicate rows and shows a summary: "X duplicate rows found and removed. Y unique rows remain." The first occurrence of each unique value is kept; subsequent duplicates are deleted.
Removed rows are permanently deleted from your data. You can undo immediately with Ctrl+Z, but if you make other changes first, undo becomes unreliable. Always make a copy of your sheet (File → Make a copy) before running Remove Duplicates on important data.
UNIQUE function (non-destructive)
UNIQUE returns only distinct values from a range without modifying the original data. Perfect for building a deduplicated view alongside your raw data, or when you want to preserve the source for auditing.
Choose an output location
Pick an empty area of your sheet (or a new sheet) where the unique values will appear. Make sure enough empty rows exist below — UNIQUE spills its output.
Write the UNIQUE formula
For a single column of values. For multi-column deduplication:
Returns unique combinations across all four columns.
Optionally add SORT
Get sorted unique values in one formula:
Unlike Method 1, this doesn't delete anything. The original data stays intact, and the UNIQUE output updates automatically if the source range changes.
Highlight duplicates with conditional formatting
Sometimes you want to see duplicates without deleting them — to review, investigate, or decide which to keep. Conditional formatting highlights them visually.
Select the column with potential duplicates
Click the column header to select the whole column, or select a specific range.
Open Conditional Formatting
Set custom formula rule
Under Format cells if, choose Custom formula is. Enter:
Adjust $A$2:$A$100 to match your actual range. The dollar signs lock the range so the formula works consistently across all cells.
Pick a highlight color
Choose a background color (red or yellow works well). Click Done. Any cell with a value appearing more than once in your range gets highlighted.
Find duplicates with COUNTIF first (safest)
Before deleting anything, add a helper column that flags duplicates. Then you can review, sort by the flag, and decide manually which duplicates to keep or remove.
Add a helper column
In an empty column (say column E), add a header like "Duplicate Count" in row 1.
Write a COUNTIF formula
In E2, enter:
Or use ARRAYFORMULA to fill the whole column at once:
Sort or filter by the count
Sort your data descending by the count column. Rows with count > 1 are duplicates and appear at the top. Now you can review each and decide manually which occurrence to keep.
Which method should you use?
| Situation | Best method |
|---|---|
| Clean up quickly, don't need to review | Method 1 — Remove Duplicates |
| Keep source intact, build deduplicated view | Method 2 — UNIQUE function |
| Just want to see duplicates visually | Method 3 — Conditional Formatting |
| Need to review before deleting | Method 4 — COUNTIF helper column |
| Deduplicate across multiple workbooks | UNIQUE + IMPORTRANGE |
Common issues
"John Smith" and "John Smith " (with trailing space) are treated as different values. Before deduplicating, clean whitespace by wrapping values with TRIM: create a helper column with =TRIM(A2), use those cleaned values for deduplication.
Remove Duplicates and UNIQUE treat "APPLE" and "apple" as the same value (case-insensitive matching). If you need case-sensitive deduplication, use =UNIQUE(A2:A100, FALSE, TRUE) — the third argument enables exact matching.
Frequently asked questions
How do I remove duplicates in Google Sheets?
Select your data range, go to Data menu, click Data cleanup, then Remove duplicates. Choose which columns Sheets should check for matches (all columns by default), then click Remove duplicates. Sheets deletes duplicate rows and displays a summary of how many rows were removed and how many unique rows remain.
How does the UNIQUE function work in Google Sheets?
UNIQUE returns only distinct rows from a range, filtering out duplicates. Syntax: =UNIQUE(range). Non-destructive — leaves the source range untouched and outputs unique values to a new location. Perfect for building deduplicated views without modifying original data. Combine with SORT for sorted unique lists: =SORT(UNIQUE(A2:A100)).
Can I highlight duplicates in Google Sheets without deleting them?
Yes. Use conditional formatting with a custom COUNTIF formula. Format menu → Conditional formatting → Custom formula: =COUNTIF($A$2:$A$100, A2)>1. Choose a highlight color. Any cell with a value appearing more than once in the range gets highlighted. This shows duplicates visually without changing your data.
How do I remove duplicates but keep the first occurrence?
Google Sheets' built-in Remove duplicates always keeps the first occurrence and removes subsequent duplicates — this is the default behavior. To keep the LAST occurrence instead, sort your data in reverse order first, remove duplicates, then re-sort to your preferred order. UNIQUE similarly returns each value's first occurrence.
Can I remove duplicates from just one column while keeping other columns intact?
Yes. In the Remove duplicates dialog, uncheck the columns you don't want to consider. If you check only column A, Sheets removes rows where column A duplicates — even if columns B, C, D have different values. Only the first row for each unique column A value is kept, with its original B, C, D values.