Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Dupes error
Excel & Google SheetsWhat Dupes means
When a duplicate-detection formula returns no matches, the values look identical on screen but differ in the data the formula actually evaluates. Common culprits are leading or trailing spaces, inconsistent case sensitivity expectations, mixed data types (number vs. text), or a range reference that does not cover the full list.
Common causes
- Extra spaces around values (e.g., ' Apple' vs 'Apple') make COUNTIF treat them as different strings.
- One cell holds a number and another holds the same digits stored as text, so they do not match numerically.
- The lookup range in COUNTIF or MATCH is accidentally anchored to the wrong column or does not extend to all rows.
- Hidden non-printing characters (line breaks, soft hyphens) were imported from another system and are invisible in the cell.
- Intentional partial duplicates (same name, different ID) are being flagged as non-duplicates because the comparison column is wrong.
Example fix
Broken
=COUNTIF(A2:A100,A2)>1Fixed
=COUNTIF(A:A,TRIM(A2))>1Expanded range to full column so new rows are included, and added TRIM so leading/trailing spaces do not hide true duplicates.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Wrap both sides of the comparison in TRIM to strip leading/trailing spaces: =COUNTIF(TRIM(A:A), TRIM(A2))>1.
- 2Use =ISNUMBER(A2) across the column to confirm all values share the same data type; convert text-numbers with VALUE() or multiply by 1.
- 3Verify the COUNTIF range covers every row in the list. Prefer a full-column reference like A:A rather than A2:A50 to avoid missing new rows.
- 4Use CLEAN(TRIM(A2)) to strip non-printing characters before comparing.
- 5If matching on multiple columns, concatenate them: =COUNTIFS(A:A,A2,B:B,B2)>1 instead of checking a single field.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Dupes and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Why does Conditional Formatting highlight duplicates differently than my COUNTIF formula?
The built-in Duplicate Values rule in Excel uses a different internal comparison than COUNTIF — it compares displayed text, not stored values. Use a COUNTIF-based CF rule for consistent, formula-controlled detection.
Will COUNTIF catch duplicates that differ only in case (e.g., 'apple' vs 'Apple')?
COUNTIF and COUNTIFS are case-insensitive, so 'apple' and 'Apple' are treated as duplicates. If you need case-sensitive duplicate detection, use EXACT() combined with SUMPRODUCT: =SUMPRODUCT((EXACT(A:A,A2))*1)>1.
How do I highlight duplicates across two separate columns or sheets?
Use COUNTIF with the second range as the lookup: =COUNTIF(Sheet2!A:A,A2)>0. Apply this as a Conditional Formatting formula rule on the first column.