Find Duplicates in Google Sheets: A Practical Guide

Name: How to find duplicates in Google Sheets
Uploaded: 2026-03-06
Duration: 48 s
Description: Learn practical, step-by-step methods to identify, flag, and remove duplicate data in Google Sheets using COUNTIF, conditional formatting, and array formulas—optimized for students, professionals, and small teams.

Learn practical, step-by-step methods to identify, flag, and remove duplicate data in Google Sheets using COUNTIF, conditional formatting, and array formulas—optimized for students, professionals, and small teams.

How To Sheets Team

March 6, 2026·5 min read

Sort-Google-Sheets Search-Google-Sheets Find-Duplicates-Google-Sheets Conditional-Formatting-Google-Sheets Filters-Google-Sheets

Find Duplicates in Sheets - How To Sheets — Photo by Mikhail Nilov via Pexels

Quick AnswerSteps

Goal: learn how to google sheets find duplicate entries in a dataset and flag them for review. You can spot duplicates using a COUNTIF-based rule, highlight them with conditional formatting, or extract unique rows with FILTER and UNIQUE. This quick guide shows practical steps for single-column and multi-column data to identify and manage duplicates.

What counts as a duplicate in Google Sheets

Duplicate data in Google Sheets can be exact matches (same value in the same column or across columns) or duplicates across rows after normalization. For example, “Apple” and “apple” are the same item when you ignore case; extra spaces, tabs, or non-breaking spaces can make duplicates appear distinct. To determine what counts as a duplicate, establish a rule set before you start. This aligns with the keyword google sheets find duplicate and helps you build reliable checks.

According to How To Sheets, a clear definition of duplication is essential for consistent data cleaning. If your dataset contains IDs, treat identical IDs as duplicates; for names, you may ignore case and spacing. Defining rules up front makes formulas and conditional formatting predictable and repeatable across projects.

Practically, begin with a small sample of your data to identify common duplication patterns: exact text matches, numeric values that look identical but differ in decimal form, or multi-column duplicates (for example, a combination of First Name and Last Name). Document these rules in a note within the sheet to guide the dedup workflow.

Core methods to identify duplicates in Google Sheets

There isn’t a single magic formula for every scenario, but there are several dependable approaches you can mix and match. The most common starting point is COUNTIF, which checks whether a value appears more than once in a given range. For a single-column check (A:A), you can flag duplicates in a helper column (e.g., B2) with: =IF(COUNTIF($A:$A, A2)>1, "DUPLICATE", ""); this is easy to audit and works well for moderate-sized datasets.

If you need to identify duplicates when two columns together define a record (for example, A and B), use COUNTIFS. In B2 you might write: =IF(COUNTIFS($A:$A, A2, $B:$B, B2) > 1, "DUPLICATE", ""). This captures identical pairs, not just a single column value. For large datasets, consider using a dynamic named range or a filter to limit the scope.

You can also create a unique list of values and compare counts: =FILTER(A:A, COUNTIF(A:A, A:A)>1). For a more compact approach, ARRAYFORMULA can apply a rule to an entire column without dragging. Finally, you can combine with TEXTJOIN or CONCAT to check multi-column duplicates across rows.

Highlight duplicates with Conditional Formatting

Conditional Formatting (CF) is a visual, non-destructive way to reveal duplicates. Start by selecting the range you want to check (for a single column, A2:A100). Then go to Format > Conditional formatting and set the rule to Custom formula is =COUNTIF($A:$A, $A2)>1. Choose a bold fill color. For multi-column duplicates (A and B), apply a rule with =COUNTIFS($A:$A, $A2, $B:$B, $B2) > 1 and apply a consistent color across the relevant cells or the entire row.

Tips for CF:

Apply to entire range and anchor references properly to avoid mis-highlighting.
Use a light background for print-friendly sheets and a bold color for screens.
Combine with TRIM and LOWER/UPPER in the data to improve reliability.

Finding duplicates across multiple columns or sheets

When duplicates can occur across multiple columns and even across sheets, you need broader checks. In the same sheet, you can create a helper column with a combined key: =A2 & "|" & B2 and then count how many times that key appears: =IF(COUNTIF($C:$C, C2) > 1, "DUPLICATE", ""). To check across sheets, use COUNTIFS with sheet references: =IF(COUNTIFS(Sheet1!$A:$A, A2, Sheet1!$B:$B, B2) > 1, "DUPLICATE", ""). If your data sits in different Google Sheets files, you can use IMPORTRANGE to pull ranges into a single view or a QUERY over a union of ranges.

Removing duplicates vs flagging: trade-offs

Flagging duplicates with a marker is safe and reversible; you preserve data and can review each case. Removing duplicates with the built-in Remove duplicates tool is faster but irreversible unless you undo or revert from a version history. A recommended workflow is to flag first, review the flagged rows, and then delete or create a clean, unique dataset. For repeated runs, consider storing a cleaned copy in a separate tab or file.

A practical workflow: from data import to clean sheet

Import your data into Google Sheets and scan for obvious truncations or misspellings.
Create a helper column that normalizes values (e.g., =TRIM(UPPER(A2))). This reduces false duplicates due to spaces or case differences.
Add a duplicate flag using COUNTIF or COUNTIFS depending on whether you’re checking one column or multiple columns.
Apply conditional formatting to highlight flagged rows or cells.
Extract duplicates to a new tab with FILTER and UNIQUE for review.
Decide whether to remove duplicates or to retain them with a flag. Keep a backup version.
Validate post-clean results by re-running the dedup check on the updated data.
If your workflow repeats, automate steps 2-5 with an Apps Script or a small add-on.

Common pitfalls and troubleshooting

Leading/trailing spaces and non-breaking spaces can masquerade as distinct values; always TRIM and CLEAN.
Differences in case matter; normalize with UPPER/LOWER if required.
Numbers stored as text may fail numeric comparisons; convert with VALUE.
Hidden characters, zero-width spaces, or non-breaking spaces are common culprits; use CLEAN.
Importing data from external sources may carry different formats; always check data types.

According to How To Sheets, the best practice is to build dedup logic into a reusable template to ensure consistency across projects.

Automation and advanced options

For repeatable dedup processes, Apps Script offers automation. A simple script can scan rows, build a composite key, and write a flag column or a separate dedup sheet. You can also explore add-ons that offer dedup workflows. When you're ready, test scripts on a copy of your data to avoid data loss.

Sample Apps Script snippet:

function markDuplicates() {
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var keys = {};
  for (var i = 1; i < data.length; i++) {
    var key = (data[i][0]||'') + '|' + (data[i][1]||'');
    if (keys[key]) {
      sh.getRange(i+1, 3).setValue('DUPLICATE');
    } else {
      keys[key] = true;
    }
  }
}

Tools & Materials

Google account with access to Google Sheets(Must be signed in to Google account)
Dataset or spreadsheet to deduplicate(Have a test copy or backup before making changes)
Stable internet connection(Required for cloud-based Sheets and online help resources)
Apps Script editor (optional)(Use if you plan to automate dedup checks with code)

Steps

Estimated time: 60-90 minutes

1
Create a cleaned key
In a new helper column, build a normalized key for each row, e.g., =TRIM(UPPER(A2)) & "|" & IF(B2<>":", TRIM(UPPER(B2)), ""). This reduces false duplicates caused by spaces or case differences.
Tip: Use a dedicated sheet or a named range for the helper column to keep formulas manageable.
2
Flag single-column duplicates
In another column, apply a COUNTIF rule to flag duplicates in the primary column, e.g., =IF(COUNTIF($A:$A, A2)>1, "DUPLICATE", "").
Tip: Copy the formula down once you confirm the pattern works for your data.
3
Flag multi-column duplicates
If duplicates depend on multiple fields, use COUNTIFS on the key columns, e.g., =IF(COUNTIFS($A:$A, A2, $B:$B, B2) > 1, "DUPLICATE", "").
Tip: Ensure your criteria ranges cover the full dataset to avoid false negatives.
4
Apply conditional formatting
Select the target range and add a conditional formatting rule with a formula such as =COUNTIF($A:$A, $A2)>1 for duplicates in a single column, or =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1 for multi-column duplicates.
Tip: Choose a high-contrast color and test on a sample first.
5
Extract duplicates for review
Use a FILTER to create a duplicate list, e.g., =FILTER(A:B, COUNTIF(A:A, A:A)>1). This creates a quick review sheet without altering the original data.
Tip: Always operate on a copy when performing deletions.
6
Decide on removal vs flag-only
If you must remove, use the built-in Remove duplicates feature or copy a cleaned version to a new sheet. If you’re not sure, keep a flag and review later.
Tip: Version history is your friend—save a restore point before bulk operations.
7
Validate results after cleanup
Re-run the dedup checks on the cleaned data to verify no missed duplicates remain. Check edge cases like merged cells or data types.
Tip: Run tests on a subset to confirm correctness before applying to the full dataset.
8
Automate for future datasets
If you expect frequent dedup tasks, encapsulate the steps in Apps Script or a Google Sheets add-on to repeat the workflow with a single click.
Tip: Document the workflow so teammates can reproduce it easily.

Pro Tip: Always work on a copy of your data when testing dedup rules.

Warning: Watch out for leading/trailing spaces and non-breaking spaces that hide duplicates.

Note: Enable version history so you can revert unintended deletions quickly.

Pro Tip: Normalize data (case, spacing) before comparing to improve accuracy.

Pro Tip: Combine with data validation to prevent duplicates from being introduced.

FAQ

What is the simplest way to find duplicates in Google Sheets?

The simplest methods are Conditional Formatting or COUNTIF. These approaches quickly flag or highlight duplicates in a single-column dataset and can be extended to multi-column checks with COUNTIFS.

How can I find duplicates across multiple columns?

Use COUNTIFS to compare multiple columns together, for example COUNTIFS(A:A, A2, B:B, B2) > 1 flags duplicate pairs. You can apply this in a helper column or within conditional formatting.

How do I remove duplicates without losing data?

Use the Remove duplicates feature on a copy of your data, or create a unique list with UNIQUE, then copy back if needed. Always back up before deleting rows.

Can I check duplicates across different sheets?

Yes. Use COUNTIFS with sheet references or IMPORTRANGE to bring multiple ranges into one view for dedup checks. Testing on a copy is recommended before committing to the original sheets.

Why might my duplicates still show after applying a rule?

This usually happens due to extra spaces, inconsistent casing, or non-printing characters. Normalize with TRIM, CLEAN, and UPPER/LOWER before comparing.

Is there a way to automate dedup checks with Apps Script?

Yes. A small Apps Script can build a composite key, scan for duplicates, and flag them or write results to a new sheet. Run on a copy first and version your data.