How to Remove Duplicates in Google Sheets

Duplicate data can quietly clutter your spreadsheets, making analysis harder than it needs to be. Whether you’re cleaning up a contact list or reviewing sales records, Google Sheets offers several straightforward ways to remove duplicates and keep your dataset tidy. This guide walks through the quickest methods, from the built-in tool to formulas and mobile steps, so you can choose what works best for your workflow.

Methods covered: 5 ·
Built-in tool steps: 3 ·
Mobile app removal: Supported ·
Formula used: UNIQUE ·
Add-ons available: Multiple

Four methods, one pattern: each approach handles duplicate detection differently, so picking the right one depends on whether you need a one-time cleanup or a dynamic solution.

How do I quickly delete duplicates in Google Sheets?

Using the built-in Remove Duplicates tool

• Select the range of cells or the entire sheet. Zapier (automation platform) notes that this is the fastest method for a one-off cleanup.
• Navigate to Data > Data cleanup > Remove duplicates.
• In the dialog box, choose which columns to check for duplicates. Zapier confirms that you can limit the check to specific columns.
• Click Remove duplicates. Google Sheets will report how many duplicates were removed (Zapier).

Selecting the data range

You can select a single column, multiple columns, or the entire sheet. The tool automatically expands the range to include all data. Zapier advises ensuring headers are included so they aren’t treated as data.

Confirming removal

After clicking Remove, a summary appears stating the number of duplicate rows removed. The first occurrence of each value is kept automatically. Zapier confirms that this is a non-destructive process for the first instance.

The pattern: For a one-time cleanup, the built-in tool is the fastest method, keeping the first instance of every duplicate row automatically.

How do I filter for duplicates in Google Sheets?

Using filter view to find duplicates

• Apply a filter by selecting the data range and clicking Data > Create a filter.
• Click the filter icon in the column header and choose Filter by condition > Custom formula.
• Enter a formula like =COUNTIF(A:A, A1)>1 to show only duplicate values in that column. Coursera (online education platform) explains that COUNTIF counts occurrences and highlights those appearing more than once.
• You can also use =COUNTIF($A$2:$A$100, A2)>1 to lock a bounded range. Zapier recommends absolute references with dollar signs for reliable filtering.

Applying a filter to show duplicates

Once the filter is applied, rows meeting the condition are shown; duplicates are displayed while unique rows are hidden. OWOX (data analytics platform) notes that this method allows you to review duplicates before deleting them manually.

How to delete duplicates in Google Sheets but keep one?

Difference between removing all duplicates and keeping one

The built-in Remove Duplicates tool always keeps the first occurrence of each duplicate row. Zapier explains that no extra configuration is needed — the tool automatically retains one instance per duplicate group.

Using Remove Duplicates tool

Follow the same steps as the quick deletion method. The tool inherently preserves the first row and deletes subsequent duplicates. Zapier confirms that this is by design.

Using UNIQUE formula

• In an empty column, enter =UNIQUE(A1:A100) to extract distinct values from a single column. OWOX describes UNIQUE as generating a table with fewer rows because duplicates are filtered out.
• For multiple columns, use =UNIQUE(A1:D100) to get unique rows across the range. OWOX says the function dynamically updates when source data changes.

The catch: UNIQUE creates a separate output — the original data stays untouched. You’ll need to copy and paste values if you want to replace the original range. OWOX recommends this for ongoing datasets where duplicates appear over time.

How to remove duplicates in Google Sheets using formula?

UNIQUE function

Already covered above — the simplest formula for deduplication. Apply it to any range and get a clean list of unique entries. OWOX provides an example: =UNIQUE(B3:B12) for a single column.

COUNTIF to identify duplicates

• In a helper column, use =COUNTIF(A:A, A1)>1 to flag duplicates with TRUE/FALSE. Coursera explains that the formula counts how many times a value appears and returns TRUE when the count exceeds one.
• For a bounded range: =COUNTIF($B$2:$B$15, B2)>1 — the dollar signs lock the lookup range. Zapier recommends this for precise control.

Array formulas

Advanced users can combine ARRAYFORMULA with COUNTIF to scan multiple columns. OWOX shows that =ARRAYFORMULA(COUNTIF($A:$Z, $A1:$Z1)>1) can flag across a wide range, though manual deletion is still required after identification.

How to highlight duplicates in Google Sheets?

Conditional formatting for duplicates

• Select the range (e.g., A2:A100).
• Go to Format > Conditional formatting.
• Under “Format cells if” choose Custom formula is.
• Enter =COUNTIF(A:A, A1)>1. Coursera notes that this formula works relative to the selected range.
• Choose a fill color (e.g., green) and click Done.

Using custom formula rule

The same pattern can be applied to highlight entire rows by referencing the first cell of each row: =COUNTIF($A$2:$A$15, $A2)>1. Zapier shows that the dollar sign locks the column, while the row reference adjusts.

Highlighting entire rows

To highlight entire rows where duplicates exist, use a custom formula that checks the first column and applies to the whole range. For example, apply to A2:D100 with formula =COUNTIF($A$2:$A$100, $A2)>1. Coursera confirms this approach.

“The built-in Remove duplicates tool is the most straightforward approach for most users, removing exact duplicate rows in seconds.”

— Google Help Center (official product documentation)

“Using UNIQUE is ideal when you need a dynamic list that updates automatically as you add new data.”

— OWOX (data analytics platform)

The pattern: Each method serves a different scenario. The built-in tool is fastest for one-time cleanup. UNIQUE is best for ongoing datasets. Conditional formatting gives you visual control, and the mobile version ensures you can deduplicate on the go. Why this matters: Choosing the right method saves time and reduces the risk of deleting data you intended to keep.

För en översikt av grundläggande Google Sheets-användning kan du också konsultera en steg-för-steg-guide som jämför Sheets med Excel.

Frequently asked questions