How to Remove Duplicates in Google Sheets — 4 Methods (2026)
Home› How to Remove Duplicates in Google Sheets
Google Sheets How-To Guide

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.

Method 1

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.

1

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.

2

Open Data Cleanup

Data→Data cleanup→Remove duplicates

A dialog opens with duplicate removal options.

3

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.

4

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.

5

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.

This action is destructive but reversible

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.

Method 2

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.

1

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.

2

Write the UNIQUE formula

=UNIQUE(A2:A100)

For a single column of values. For multi-column deduplication:

=UNIQUE(A2:D100)

Returns unique combinations across all four columns.

3

Optionally add SORT

Get sorted unique values in one formula:

=SORT(UNIQUE(A2:A100))

Unlike Method 1, this doesn't delete anything. The original data stays intact, and the UNIQUE output updates automatically if the source range changes.

Method 3

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.

1

Select the column with potential duplicates

Click the column header to select the whole column, or select a specific range.

2

Open Conditional Formatting

Format→Conditional formatting
3

Set custom formula rule

Under Format cells if, choose Custom formula is. Enter:

=COUNTIF($A$2:$A$100, A2)>1

Adjust $A$2:$A$100 to match your actual range. The dollar signs lock the range so the formula works consistently across all cells.

4

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.

Method 4

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.

1

Add a helper column

In an empty column (say column E), add a header like "Duplicate Count" in row 1.

2

Write a COUNTIF formula

In E2, enter:

=COUNTIF($A$2:$A$100, A2)

Or use ARRAYFORMULA to fill the whole column at once:

=ARRAYFORMULA(IF(A2:A100="","",COUNTIF(A2:A100, A2:A100)))
3

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?

SituationBest method
Clean up quickly, don't need to reviewMethod 1 — Remove Duplicates
Keep source intact, build deduplicated viewMethod 2 — UNIQUE function
Just want to see duplicates visuallyMethod 3 — Conditional Formatting
Need to review before deletingMethod 4 — COUNTIF helper column
Deduplicate across multiple workbooksUNIQUE + IMPORTRANGE

Common issues

Whitespace makes values look different when they're the same

"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.

Case sensitivity varies by method

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.