Google Sheets UNIQUE Function Not Working? Here’s What To Do

Your UNIQUE function isn’t working, duplicates still appear, results are missing, or the formula returns unexpected output.
This usually happens when the data isn’t truly identical due to formatting, hidden characters, or structure issues.

This guide fixes it step by step.

Why the Issue Happens

• Hidden spaces or non-printable characters
• Text vs number mismatch (100 vs "100")
• Case differences (Sales vs sales)
• Extra columns included in range
• Formula applied to inconsistent data
• Duplicate rows not actually identical
• Using UNIQUE incorrectly for multiple columns
• Data not cleaned before applying UNIQUE

Step-by-Step Fixes

Step 1: Use Correct UNIQUE Syntax

Basic usage:

=UNIQUE(A2:A100)

For multiple columns:

=UNIQUE(A2:C100)

This returns unique rows, not just one column, when multiple columns are selected.

Step 2: Check If Values Are Truly Identical

What looks identical may not be.

Test:

=EXACT(A2, A3)

• TRUE → identical
• FALSE → hidden mismatch

Step 3: Remove Extra Spaces

Most common issue.

Fix:

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

Step 4: Remove Hidden Characters

Imported data often contains invisible characters.

Fix:

=UNIQUE(ARRAYFORMULA(TRIM(CLEAN(A2:A100))))

Step 5: Fix Text vs Number Mismatch

Example:

• 100 vs "100"

Fix:

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

Or standardize formatting.

Step 6: Standardize Case

By default, UNIQUE treats different cases as different values.

Fix:

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

or

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

Step 7: Remove Partial Differences in Rows

If using multiple columns:

=UNIQUE(A2:C100)

Even one different column value → treated as unique row.

Fix:

• Select only relevant columns
• Or isolate the column you want uniqueness on

Step 8: Use SORT with UNIQUE

Sometimes output looks inconsistent.

Fix:

=SORT(UNIQUE(A2:A100))

Step 9: Handle Blank Cells

Blanks may appear as duplicates.

Fix:

=UNIQUE(FILTER(A2:A100, A2:A100<>""))

Step 10: Avoid Full Column References

Using:

=UNIQUE(A:A)

can slow performance and cause inconsistencies.

Better:

=UNIQUE(A2:A1000)

Common Mistakes

• Not cleaning spaces or hidden characters
• Assuming visually identical values are identical
• Using multiple columns when only one matters
• Ignoring text vs number differences
• Not standardizing case
• Expecting UNIQUE to modify original data
• Using full-column ranges unnecessarily

Bottom Line

If UNIQUE isn’t working, fix in this order:

1. Remove spaces and hidden characters
2. Fix text vs number mismatch
3. Standardize case
4. Ensure correct column selection
5. Filter out blanks if needed
6. Use cleaned data before applying UNIQUE

Most issues come from data inconsistencies, not the function itself.
Clean the data, and UNIQUE will work reliably.

Google Sheets Fixes:

• VLOOKUP not working
• FILTER Function Issues
• Query Errors
• IMPORTRANGE Problems