How to Remove Duplicates in Google Sheets

Q: What is the formula to remove duplicates in Google Sheets?

=UNIQUE(A2:A100) returns the distinct values from that range into a new set of cells, without modifying the original data. It spills into as many rows as there are unique values.

Q: Is UNIQUE() case-sensitive?

UNIQUE() is case-sensitive - "Apple" and "apple" are treated as distinct values and both will appear in the output. If you need case-insensitive deduplication, normalize the column first with a helper column using =LOWER(A2) , then run UNIQUE() on that.

Q: How do I see which rows are duplicates without deleting anything yet?

Use COUNTIF as a helper column: =COUNTIF(A$2:A2, A2) > 1 returns TRUE for every occurrence after the first of a repeated value, letting you review before committing to a deletion.

Google Sheets has two separate ways to handle duplicates: a formula that returns a clean list without touching your original data, and a menu command that deletes duplicate rows permanently. Which one you want depends on whether you need to keep the source data intact.

Quick answer

=UNIQUE(A2:A100) returns the distinct values from a range as a formula result, leaving the original data untouched. To permanently delete duplicate rows instead, select your range and use Data > Data cleanup > Remove duplicates. UNIQUE() is case-sensitive by default - 'Apple' and 'apple' are treated as different values.

Need the same thing in another language?

See the full reference for Python, JavaScript, SQL, Bash, PowerShell, and more.

See every language →

The formula: UNIQUE()

=UNIQUE(A2:A100)

UNIQUE() spills the distinct values from the given range into as many cells below the formula as there are unique values. It does not alter the original range - useful when you want a clean reference list to build other formulas against while keeping the raw data intact.

The menu command: Data cleanup

Select the range you want to clean, then go to Data > Data cleanup > Remove duplicates. Sheets shows a preview of how many duplicate rows it found before you confirm, and you can choose whether the range has a header row so it isn't accidentally treated as data. Unlike UNIQUE(), this permanently deletes the duplicate rows.

Is UNIQUE() case-sensitive?

Yes - UNIQUE() treats "Apple" and "apple" as different values, so both survive in the output. If you need case-insensitive results, add a helper column with =LOWER(A2) filled down, then run UNIQUE() on that column instead.

Deduplicating across more than one column

=UNIQUE(A2:B100)

Passing a multi-column range to UNIQUE() treats a row as a duplicate only when every column in the range matches - if column A matches but column B differs, both rows are kept. The Remove duplicates menu option works the same way: select all relevant columns before running it.

Flagging duplicates without deleting anything yet

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

Filled down a column, this returns TRUE for every occurrence of a value after its first appearance - the anchored A$2:A2 range grows one row at a time as you copy the formula down, so each row only checks against the rows above it. Useful for reviewing what would be deleted before committing to Remove duplicates.

When rows that look identical don't get flagged as duplicates

Trailing spaces, a non-breaking space pasted in from a webpage, or a value stored as text in one cell and a number in another all look the same visually but compare as different. Paste the affected column into the Invisible Character Detector to see exactly what's different before trying Remove duplicates again.

Deduplicating a plain list, not a spreadsheet? Paste it here.

Open Remove Duplicate Lines →

Frequently Asked Questions

What is the formula to remove duplicates in Google Sheets?

=UNIQUE(A2:A100) returns the distinct values from that range into a new set of cells, without modifying the original data. It spills into as many rows as there are unique values.

Is there a built-in menu option instead of a formula?

Yes - select your range, then Data > Data cleanup > Remove duplicates. Unlike UNIQUE(), this permanently deletes the duplicate rows from the selected range rather than returning a separate list.

Is UNIQUE() case-sensitive?

UNIQUE() is case-sensitive - "Apple" and "apple" are treated as distinct values and both will appear in the output. If you need case-insensitive deduplication, normalize the column first with a helper column using =LOWER(A2), then run UNIQUE() on that.

How do I remove duplicates based on more than one column?

Select all the relevant columns as your range before running Remove duplicates from the Data menu, or wrap multiple columns in UNIQUE(): =UNIQUE(A2:B100) treats a row as a duplicate only when both columns match.

How do I see which rows are duplicates without deleting anything yet?

Use COUNTIF as a helper column: =COUNTIF(A$2:A2, A2) > 1 returns TRUE for every occurrence after the first of a repeated value, letting you review before committing to a deletion.

Why do rows that look identical still show up as different after Remove duplicates?

Trailing spaces, non-breaking spaces from a pasted web source, or a value stored as text versus a number in another row - all look the same visually but compare as different values. Paste the affected column into the Invisible Character Detector to find the exact difference.

What if I want to deduplicate a plain list, not a spreadsheet column?

Paste your list into the Remove Duplicate Lines tool and it returns a deduplicated list instantly in the browser, with a case-sensitivity option built in - no formula needed.