Duplicate Values Not Working in Excel? How to Fix It

If Excel is not highlighting duplicate values, first select the full range you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. If the rule is already there, check its “Applies to” range and whether the entries really match: extra spaces, text-versus-number differences, and formulas can make apparent duplicates behave unexpectedly.

What Excel means by a duplicate

Excel’s built-in Duplicate Values rule looks for repeated values within the range covered by the rule. By default, it highlights every matching occurrence, not just the second and later copies. If “North” appears in A2, A5, and A9, all three cells can be marked. This is a formatting rule: it changes how cells look, but it does not remove data or change the values.

A duplicate is assessed in relation to the selected range. If the same item appears once in column A and once in column B, a rule applied only to column A will not compare the two columns. Similarly, a rule applied to A2:A50 will not find a matching entry in A51. Before changing the data, establish whether you want to find repeats within one column, across several columns, or across a whole table.

Also distinguish duplicates from similar-looking entries. Excel’s usual text comparisons are not case-sensitive, so “Maple” and “MAPLE” are generally treated as a match. But a trailing space, a different punctuation mark, or a value stored as text instead of a number can make two cells that look alike on screen compare differently.

Apply the built-in Duplicate Values rule

On Excel for Microsoft 365 on Windows, use these steps to create a fresh rule:

Select the cells to compare. For a list with a header in A1, you might select A2:A100 rather than including the heading.
Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the dialog, leave Duplicate selected, choose a format, and select OK.

To test the result, enter the same simple value in two cells inside the selected range. If the cells highlight, the rule works and the original issue is likely the range or the underlying contents of the entries that did not match. If they do not, inspect the rule manager and confirm that the cells are included in the rule.

On Excel for Mac, the Conditional Formatting command is also on the Home tab, though dialog placement can differ slightly by release. In Excel for the web, use Home > Conditional Formatting and choose the duplicate-values rule if it is available in your version. If you do not see the same rule options, open the workbook in the desktop app to manage the rule in more detail.

Check the rule’s range and priority

A correct rule can appear broken when it is attached to the wrong cells. Select one of the cells that should be highlighted, then choose Home > Conditional Formatting > Manage Rules. Set the display option to show rules for the current selection or worksheet, and inspect the Applies to field. It should cover the data you intend to compare, such as =$A$2:$A$100. If the range stops early, extend it; if it includes unrelated columns or a heading, adjust it.

When the table grows, a rule created for a fixed range may not automatically include newly added rows, particularly if the new cells were pasted outside the formatted area. Add the rows to the Applies to range or create the list as an Excel table so formatting can extend with new table rows. Check for overlapping rules too. In Manage Rules, a different conditional-formatting rule, its order, or a rule configured to stop processing other rules can affect what you see. Temporarily changing the format to a vivid fill can help distinguish a subtle color choice from a rule that is not firing.

Look for spaces and mismatched data types

Two cells can display the same text while containing different characters. A common cause is a leading or trailing space, often introduced when data is copied from a website or exported system. Nonbreaking spaces are another possibility; they may look like ordinary spaces but are not removed by every basic cleanup method. Compare the values in the formula bar, widen the column, or test the cells in a spare area before editing the original list.

Numbers can also be stored as text. For example, a cell containing the numeric value 123 and another containing the text string “123” may display identically but behave differently in calculations and data operations. Look for green error indicators, inconsistent alignment, or number-format differences, but do not rely on appearance alone. Convert the column consistently using an appropriate method, such as the warning menu’s Convert to Number option when offered, or Data > Text to Columns for suitable imported data. Make a copy first if the values are important, because conversions can alter identifiers such as codes with leading zeros.

If you suspect extra spaces, use a helper column to clean a copy of the data and check whether the cleaned results match. TRIM removes ordinary extra spaces at the beginning and end and reduces repeated internal spaces, but it does not remove every unusual whitespace character. For imported text containing nonbreaking spaces, SUBSTITUTE may be needed to replace that specific character before trimming. Check the result before replacing source values, especially when spaces are meaningful in the data.

Use a formula when the built-in rule is not enough

A formula-based conditional-formatting rule gives more control over blanks, the range, and whether to mark every copy or only later occurrences. To highlight repeated nonblank values in A2:A100, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the data in A2:A100 and the first selected cell A2, enter:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

Choose Format to set the appearance, then select OK. The dollar signs keep the comparison range fixed as the rule evaluates each row, while A2 changes to the current cell. The blank check prevents empty cells from being highlighted as repeated values. If your list starts on a different row, use its first data cell in the formula and update the range to match.

To mark only the second and subsequent occurrence, select A2:A100 and use this formula with the same data layout:

=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)

Here the comparison range expands as the rule moves down the list. The first appearance has a count of one and is left unformatted; a later appearance has a count above one and is highlighted. COUNTIF is not case-sensitive, and wildcard characters such as an asterisk or question mark can have special meaning in its criteria. If those characters are part of literal text you need to compare precisely, test the results rather than assuming COUNTIF treats them as ordinary characters.

Formatting duplicates is not the same as removing them

Conditional formatting helps you review repeated entries without changing the worksheet. Data > Remove Duplicates, by contrast, deletes repeated rows from the selected data range and keeps one occurrence. It is a data-cleaning command, not a highlighting rule, and its result may be difficult to undo after you continue editing. Save a copy before using it on important records.

When you choose Remove Duplicates, Excel lets you select which columns determine whether rows count as duplicates. If you select only an email column in a table, rows with repeated email addresses may be removed even if other fields differ. If you select every column, rows generally need to match across all selected columns to count as duplicates. Confirm the selected columns and the header option before proceeding, and make sure the range represents complete records rather than a single column detached from related data.

For a non-destructive review, filter or highlight the repeated values first, inspect the affected records, and decide what should happen to each one. Two identical-looking customer names, for example, may represent separate people; repeated invoice numbers may be a genuine data problem. A duplicate detector identifies a pattern, not whether removing a record is safe.

A practical troubleshooting sequence

If the rule still seems unreliable, work through the checks in order rather than repeatedly recreating it:

Test two identical, simple values inside the range. This separates a rule problem from a data-cleaning problem.
Confirm the selected cells and the rule’s Applies to range include both copies.
Check for leading or trailing spaces, hidden characters, inconsistent punctuation, and text-versus-number storage.
Review conditional-formatting rules for overlap, unexpected order, or a format that is too subtle to notice.
Check whether the values come from formulas. A formula may return an empty string or may display a rounded result that differs from its underlying value.
Try a formula-based rule on a small test range, then expand it once the result is correct.

Formula results deserve special attention. Two cells may both display 10.0 because of number formatting while their underlying values are 10.04 and 10.03. A duplicate check compares stored results, not just the rounded display. Conversely, a formula returning an empty string is not the same thing as a truly empty cell in every Excel operation. If displayed precision is what matters, decide how to normalize or round the values before checking for repeats; do not round original data without considering its purpose.

Frequently Asked Questions

Why does Excel highlight only some duplicate values?

The rule may cover only part of the list, or some entries may differ because of spaces, hidden characters, punctuation, or data types. Check the Applies to range first, then compare the actual cell contents rather than only their displayed appearance.

Does Excel’s Duplicate Values rule highlight both copies?

Yes. The built-in rule normally highlights every occurrence that has a match in the selected range. Use a formula-based rule with an expanding COUNTIF range if you want to highlight only the second and later occurrences.

How do I find duplicates across two columns?

Select both columns before applying the built-in rule if you want Excel to compare values across that selected range. If each row contains a multi-column record and a duplicate means the entire row matches, use Remove Duplicates with the relevant columns selected or create a rule designed to compare the complete record.

Why are blank cells being highlighted as duplicates?

A formula-based rule can count blank cells unless it excludes them. Add a nonblank condition, as in the AND formula above. If a built-in rule marks blanks unexpectedly, inspect the range for cells that contain formulas returning an empty string or other contents that appear blank.

Can Excel find duplicates without deleting anything?

Yes. Use Conditional Formatting to highlight them, or use a helper column to count matches. These methods let you review the results without removing records. Remove Duplicates is a separate command that changes the data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.