Google Sheets Duplicates Not Removing? Here’s What To Do

You’re trying to remove duplicates in Google Sheets, but duplicates still remain, or only some rows are removed.
This usually shows up as identical-looking values not being treated as duplicates or inconsistent results after using “Remove duplicates.”

This guide fixes it step by step.

Why the Issue Happens

• Hidden spaces or non-printable characters
• Text vs number mismatch
• Case sensitivity differences
• Partial column selection instead of full dataset
• Duplicate rows not truly identical (extra columns differ)
• Formulas generating slightly different values
• Using REMOVE DUPLICATES incorrectly
• Data not standardized before deduplication

Step-by-Step Fixes

Step 1: Select the Correct Range

If you don’t select the full dataset, duplicates won’t be removed properly.

Wrong:

• Selecting only column A

Correct:

A1:D100 → Data → Data cleanup → Remove duplicates

Choose all relevant columns.

Step 2: Use Built-in Remove Duplicates Properly

Go to:

• Data → Data cleanup → Remove duplicates

Then:

• Check all columns that define a duplicate
• Click Remove duplicates

If you select only one column, duplicates may still exist across rows.

Step 3: Remove Hidden Spaces

Values may look identical but contain extra spaces.

Fix:

=TRIM(A2)

To clean entire column:

=ARRAYFORMULA(TRIM(A2:A100))

Apply this before removing duplicates.

Step 4: Remove Non-Printable Characters

Imported data often contains hidden characters.

Fix:

=CLEAN(A2)

Combine with TRIM:

=TRIM(CLEAN(A2))

Step 5: Fix Text vs Number Mismatch

Example:

• 100 (number) vs "100" (text)

These are treated as different values.

Fix:

=VALUE(A2)

Or standardize format:

• Format → Number

Step 6: Use UNIQUE Function (Better Method)

Instead of manual removal:

=UNIQUE(A2:A100)

For multiple columns:

=UNIQUE(A2:C100)

This dynamically removes duplicates.

Step 7: Handle Case Sensitivity

By default, Google Sheets treats “Sales” and “sales” as different.

Fix:

=UNIQUE(ARRAYFORMULA(LOWER(A2:A100)))

This standardizes case before removing duplicates.

Step 8: Remove Duplicates Based on Specific Columns

If you want uniqueness based on one column only:

=UNIQUE(A2:A100)

If based on multiple columns:

=UNIQUE(A2:C100)

Choose columns carefully.

Step 9: Fix Formula-Based Duplicates

If values come from formulas, slight differences may exist.

Example:

• 100 vs 100.0001

Fix:

=ROUND(A2, 2)

Standardize values before deduplication.

Step 10: Identify Duplicates Before Removing

Use COUNTIF to detect duplicates:

=COUNTIF(A:A, A2)

If result >1 → duplicate exists.

Highlight using conditional formatting if needed.

Common Mistakes

• Selecting only one column instead of full dataset
• Ignoring hidden spaces or characters
• Not standardizing text vs numbers
• Assuming visually identical values are actually identical
• Using REMOVE DUPLICATES without cleaning data
• Not handling case differences
• Expecting UNIQUE to modify original data (it creates new output)

Pro Tips / Better Alternatives

Use SORT + UNIQUE Together

=SORT(UNIQUE(A2:A100))

Removes duplicates and sorts results.

Use QUERY for Advanced Deduplication

=QUERY(A1:C100, "SELECT A, B, C GROUP BY A, B, C", 1)

Works well for structured datasets.

Highlight Duplicates First

Use conditional formatting:

=COUNTIF(A:A, A2)>1

Helps identify before removal.

Use Helper Columns for Cleaning

Create a cleaned version:

=TRIM(CLEAN(LOWER(A2)))

Then remove duplicates on cleaned data.

Avoid Full Column References in Large Data

Instead of:

=UNIQUE(A:A)

Use:

=UNIQUE(A2:A1000)

Improves performance.

Bottom Line

If duplicates aren’t removing, fix in this order:

1. Select full dataset
2. Clean spaces and hidden characters
3. Fix text vs number mismatch
4. Standardize case
5. Use UNIQUE instead of manual removal
6. Verify duplicates using COUNTIF

Most issues come from data inconsistencies, not the tool itself.
Clean the data first, and duplicate removal will work reliably.