How to Remove Duplicates in Google Sheets

Delete duplicate rows with the built-in tool, pull a clean unique list with a formula, or just highlight the duplicates so you can review them first. Here's each way.

By the Quiriz Team · Published July 25, 2026 · 4 min read

The quick version: select your data, go to Data → Data cleanup → Remove duplicates, pick the columns, and click. Here are all three approaches, including one that doesn't touch your original.

1. The built-in tool

Select the range, then Data → Data cleanup → Remove duplicates. Tick whether row 1 is a header and which columns should count toward a duplicate — leave all ticked to remove fully identical rows, or one column to dedupe on just that field. Click Remove duplicates and Sheets reports how many it deleted.

2. The UNIQUE function (keeps the original)

To pull a clean list without altering your data, use =UNIQUE(A2:A100) in an empty spot — it spills the distinct values. Sort it too: =SORT(UNIQUE(A2:A100)).

3. Highlight without deleting

To review first, use conditional formatting. Select the range, Format → Conditional formatting → Custom formula is, and enter:

=COUNTIF(A:A, A1) > 1

Every duplicate gets colored; nothing is removed.

🛠 Free tool: comparing or cleaning data pasted from a sheet? the free Duplicate Remover dedupes pasted data in your browser (nothing uploaded), and Remove Blank Rows tidies whitespace.

Analyze your Sheets data with AI

Once it's clean, ask questions about your Google Sheets in plain English with =QUIRIZ(). Free to start.

Try Quiriz free →

Frequently asked questions

How do I remove duplicates in Google Sheets?

Select the range, go to Data > Data cleanup > Remove duplicates, confirm which columns define a duplicate and whether there's a header row, then click Remove duplicates.

How do I get a unique list without deleting the original?

Use =UNIQUE(range) in an empty area — for example =UNIQUE(A2:A100). It spills a de-duplicated list and leaves your source data untouched. Combine with SORT: =SORT(UNIQUE(A2:A100)).

How do I highlight duplicates without removing them?

Use conditional formatting with a custom formula: select the range, Format > Conditional formatting > Custom formula is, and enter =COUNTIF(A:A, A1) > 1. Duplicates get colored so you can review before deleting.

Can I remove duplicates based on one column only?

In Data cleanup, tick only that column when choosing what defines a duplicate. Sheets then treats rows as duplicates whenever that column matches.