How to Filter Duplicates in Google Sheets

Published on

Filtering duplicates is often necessary when editing or removing data in Google Sheets. While the built-in command (Data > Data Cleanup > Remove duplicates) is convenient for removing duplicates, it doesn’t allow you to view them beforehand. Filtering duplicates offers this advantage.

Once you’ve filtered duplicates, you can select the visible rows, right-click, and delete them. Alternatively, you can apply a fill color to make them stand out.

Before You Start

The term “duplicate” can be categorized into three scenarios:

  1. Column-Based Duplicates: Duplicate records are identified when the same value appears multiple times within a specific column.
  2. Custom/Selected Column-Based Duplicates: Duplicate records are identified when selected columns have the same values in each row, regardless of other data in the row.
  3. Row-Based Duplicates: Duplicate records are identified when an entire row (all columns) is repeated multiple times.

You’ll need different formulas for each scenario to filter duplicates. Let’s dive into examples for each case.

How to Filter Column-Based Duplicates in Google Sheets

Imagine you have a list of names in column A with “Name” in cell A1:

Name
Saket
Saket
Saket
Elizabeth
Elizabeth
Ben
Emily

To filter duplicates in column A:

  1. Select column B (a blank column).
  2. Go to Data > Create a Filter.
  3. Click the filter icon in B1 and choose Filter by Condition > Custom formula is.
  4. Enter the formula:
    =COUNTIF($A$2:A2, A2) > 1
  5. Click Done.
Filter custom formula field settings

This formula counts the occurrences of values in column A up to the current row.

  • $A$2 is the fixed start of the range.
  • A2 dynamically adjusts as the formula processes each row.

The formula returns TRUE wherever the count exceeds 1, effectively filtering duplicates.

Output after filtering duplicates in a single column in Google Sheets using a custom formula

How to Filter Custom/Selected Column-Based Duplicates in Google Sheets

Suppose you have a dataset like this in A1:B:

First NameLast Name
SaketGupta
SaketGupta
SaketPatel
RosemaryWilson
RosemaryWilson
EthanJohnson

Here, a duplicate occurs when the same first name and last name appear more than once in the rows.

To filter these duplicates:

  1. Select column C (a blank column).
  2. Go to Data > Create a Filter.
  3. Use the following custom formula:
=COUNTIFS($A$2:A2, A2, $B$2:B2, B2) > 1

This formula counts the occurrences of first and last names in columns A and B up to the current row, and returns TRUE if the count is greater than 1. The formula adjusts dynamically in the filtered range because we used a fixed start and relative end range, along with relative criteria.

Example of filtering custom/selected column-based duplicates in Google Sheets using a custom formula

How to Filter Row-Based Duplicates in Google Sheets

In the following dataset in A1:E, let’s filter rows where all columns—ID, Name, Department, Advance, and Date—are identical.

IDNameDepartmentAdvanceDate
101SaketSales250024/12/2024
101SaketSales250024/12/2024
101SaketSales250021/01/2025
102ElizabethMarketing100024/12/2024
103ElizabethHR100024/12/2024

To filter row-based duplicates, use the following formula:

=COUNTIFS($A$2:A2, A2, $B$2:B2, B2, $C$2:C2, C2, $D$2:D2, D2, $E$2:E2, E2) > 1

This approach is similar to the selected column-based method from the second example above. While it works, specifying many columns can be tedious. Here’s a dynamic alternative:

=COUNTIF(TRANSPOSE(QUERY(TRANSPOSE($A$2:E2),,9^9)), TRANSPOSE(QUERY(TRANSPOSE(A2:E2),,9^9)))>1

This formula dynamically evaluates the entire range, eliminating the need to list each column individually.

Example of filtering row-based duplicates in Google Sheets using a custom formula

Deleting or Highlighting Filtered Rows

After filtering duplicates, you can take one of the following actions:

  1. Delete Filtered Rows:
    • Select the visible rows (excluding the header).
    • Right-click and choose Delete selected rows.
  2. Highlight Filtered Rows:
    • Select the visible rows.
    • Click the paint bucket icon in the toolbar to apply a fill color.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.