How To Check for Duplicates in Google Sheets (Step-by-Step Guide)

Finding and handling duplicates in Google Sheets is something almost everyone runs into—whether you’re cleaning a contact list, reviewing survey results, or checking for repeated IDs. Google Sheets gives you a few different ways to spot and manage duplicates, from quick visual checks to formulas and built-in tools.

This guide walks through practical methods to check duplicates in Google Sheets, what each method is good for, and when they might fit different types of users.

What “Duplicates” Mean in Google Sheets

Before you start, it helps to be clear on what you actually consider a duplicate:

• Duplicate cell: The exact same value appears more than once in a single column or row (e.g., the email [email protected] appears twice in column B).
• Duplicate row: Entire rows have identical values across some or all columns (e.g., the same person’s full record appears multiple times).
• Partial duplicate: Only some fields match, like the same email but a slightly different name spelling.

Google Sheets doesn’t automatically know which of these you care about. How you check for duplicates depends on what you’re trying to deduplicate:

• One column (e.g., emails, IDs, product codes)
• Multiple columns together (e.g., first name + last name + date)
• Entire rows

Method 1: Visually Highlight Duplicates with Conditional Formatting

This is the most common way to see duplicates at a glance—no formulas needed.

Highlight duplicates in a single column

1. Select the column you want to check (for example, click the A at the top of column A).

2. Go to Format → Conditional formatting.

3. In the right sidebar, under “Apply to range”, make sure it shows your selected column (e.g., A:A).

4. Under “Format rules”, open the dropdown and choose:

   • “Custom formula is”

5. In the formula box, enter:

   =COUNTIF($A:$A, A1) > 1 

6. Choose a fill color or text style to highlight duplicates.

7. Click Done.