Why Duplicate Values Aren’t Working in Microsoft Excel

Why Duplicate Values Aren't Working in Microsoft Excel

Duplicate values usually seem not to work in Excel because the rule is looking at the wrong range, the cells do not contain truly matching values, or you are using a different feature than you intend. For highlighting, select the data and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; for permanent removal, use Data > Remove Duplicates.

First identify which duplicate feature you mean

Excel has several ways to deal with repeated values, and they do different jobs. The built-in Duplicate Values rule adds visual formatting; it does not delete anything. Remove Duplicates deletes repeated entries from the selected data. A formula such as COUNTIF can label, count, or conditionally format matches according to rules you control.

This distinction explains a common misunderstanding: if duplicate entries remain in the cells after you apply Duplicate Values, that is expected. The rule only changes how qualifying cells look. Conversely, Remove Duplicates may leave one copy of each value, rather than highlight every copy. Decide whether you want to find, mark, count, or delete repeats before troubleshooting.

Highlight: use Conditional Formatting when you want to see duplicates without changing the data.
Delete: use Remove Duplicates when you want Excel to remove repeated records.
Count or flag: use a formula when you need a count, a custom test, or a result that updates as values change.

Check the selected range and rule scope

The built-in highlighting rule applies to a range. If only one cell was selected when the rule was created, or if the selected range stops before the rows you are checking, values outside that range will not be highlighted. Select the complete list, including all relevant rows in the column, before creating the rule. If your list has headers, select the data cells rather than the header unless you deliberately want the header included.

To inspect an existing rule, select a cell in the data and go to Home > Conditional Formatting > Manage Rules. Check the rule’s Applies to range. If it ends at row 100 while your new data continues to row 130, the added rows may not be covered. Edit the range to include the missing cells, then apply the change.

Rules can also appear not to work because another conditional-formatting rule changes the same cells’ appearance. In Manage Rules, review the rules that apply to the selected range and their order. A duplicate rule may be present but its fill or font style may be hard to distinguish, or another rule may determine the visible format. Choose an obvious format temporarily to verify whether the rule is triggering.

Understand what Excel counts as a duplicate

A duplicate is not always a cell that merely looks similar on screen. Excel compares cell contents, and small differences can matter: a leading or trailing space, a different punctuation mark, an extra character, or a value stored as text instead of a number. For example, Smith and Smith followed by a space can look identical in a narrow cell but are not the same text string.

Formatting is another source of confusion. A date displayed as 1/2/2026 may be stored as a date serial number, while a similar-looking entry imported as text may not behave the same way in a comparison. Number formatting can also make distinct values look alike: 1.2 and 1.20 display identically with two decimal places, but they represent the same numeric value; two numbers rounded for display can look the same while retaining different underlying values.

To investigate, click a suspect cell and inspect its formula bar and number format. Widen the column to reveal hidden characters or use a helper column to clean the data. Check for inconsistent data types as well as visible spelling. When values come from an export, web page, or another system, imported spaces and inconsistent formats are especially common.

Clean spaces and imported text before comparing

TRIM removes ordinary extra spaces at the start and end of text and reduces repeated internal spaces to single spaces. CLEAN removes many nonprinting control characters. Neither function is a universal cure: some sources use nonbreaking spaces, which may look like ordinary spaces but are not removed by TRIM alone. Also, CLEAN does not remove every possible Unicode character.

With the uncleaned value in A2, this helper formula replaces a nonbreaking space with a normal space, then applies CLEAN and TRIM:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Fill the formula down a helper column, then check for duplicates in the cleaned results. Keep the original column until you have confirmed that cleaning has not changed meaningful text. For instance, TRIM will collapse repeated spaces inside a name or label; that is useful for accidental spacing but may not be appropriate for every dataset.

Excel’s COUNTIF function is not case-sensitive, so values such as East and EAST count as matching for a COUNTIF-based duplicate test. The built-in duplicate rule is also generally used for ordinary value matching, not as a case-sensitive text audit. If letter case is meaningful in your data, use a deliberate case-sensitive comparison rather than assuming the standard duplicate tool distinguishes it.

Use a formula when the built-in rule is too limited

A formula gives you control over the comparison range and what counts as a match. With values in A2:A20, select A2:A20 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the following formula and choose a format:

=COUNTIF($A$2:$A$20,A2)>1

The dollar signs keep the comparison range fixed as the rule evaluates each row; A2 remains relative so it changes to A3, A4, and so on. This flags every occurrence of a repeated value, including its first appearance. If you want to highlight only the second and later occurrences, use this formula for the same selected range:

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

With data in A2:A7, containing North, West, North, East, West, West in that order, the second formula flags the third, fifth, and sixth rows. The first occurrence of each label is not flagged. If you instead want a label in a neighboring column, put this formula in B2 and fill it down through B7:

=IF(COUNTIF($A$2:$A$7,A2)>1,"Duplicate","")

For that example, the result is Duplicate for both North entries and all three West entries; East returns a blank. COUNTIF also treats the asterisk and question mark as wildcard characters in criteria, so values containing those characters may need special handling if they are meant to be literal text. A formula is useful for custom logic, but it must reference the correct range and start on the same row as the first selected cell.

Remove duplicates without losing the wrong records

To delete repeated records, select the data and choose Data > Remove Duplicates. Confirm whether the first row contains headers, then choose the columns Excel should use to decide whether rows are duplicates. Excel keeps one occurrence and removes the other matching rows, so make a copy of important data first. Removing duplicates changes the dataset; it is not just a display setting.

The selected columns determine what counts as a repeated row. If you select only an email column, rows with the same email can be treated as duplicates even if other columns differ. If you select name and email, Excel considers the combination of those selected values. Two rows with the same name but different email addresses are not duplicates by that two-column test.

For an audit trail, copy the data to another worksheet before removing rows, or use Conditional Formatting first to review the matches. After removal, Excel reports how many duplicate values were removed and how many unique values remain. If the result is unexpected, undo immediately and review the selected columns and header setting before trying again.

Why new rows or changed values are not highlighted

A conditional-formatting rule may cover the original list but not later additions. Check its Applies to range in Manage Rules. If the list grows frequently, consider converting it to an Excel table with Insert > Table, then review the rule after adding rows to make sure its scope includes them. Do not assume every existing rule will expand exactly as you expect in every editing situation.

Also check whether calculation is current when a formula-based rule depends on formulas elsewhere. Excel normally recalculates automatically, but a workbook set to manual calculation can show stale formula results. On the Formulas tab, check Calculation Options and select Automatic if that is appropriate for the workbook. A rule based only on fixed cell values will not need recalculation in the same way as a rule dependent on changing formulas.

Finally, check whether filters or hidden rows are affecting what you can see. A duplicate may be correctly highlighted in a row that is currently filtered out, or a fill color may be difficult to notice against the sheet’s existing formatting. Clear filters temporarily and use a contrasting highlight style while diagnosing the issue.

A practical troubleshooting sequence

Work through these checks in order rather than repeatedly recreating the same rule:

Confirm whether you want to highlight, count, flag, or delete duplicates.
Select the full data range and exclude headers unless they should be compared.
Inspect Conditional Formatting > Manage Rules and verify the Applies to range and visible format.
Compare the actual contents of two cells, checking for spaces, punctuation, data types, and hidden characters.
Test a cleaned helper column if the list was imported or assembled from multiple sources.
Use a COUNTIF formula when you need a controlled range or a first-occurrence-only test.
Before using Remove Duplicates, verify which columns define a duplicate and keep a backup of the original data.

This sequence separates a scope problem from a data-quality problem. If a formula flags a pair but the built-in rule does not, compare the rule’s range and formatting. If neither flags the pair, investigate whether the values are actually equivalent under the comparison you need.

Frequently Asked Questions

Why does Excel highlight only some duplicate values?
Usually the rule’s Applies to range does not include every row, or some values differ in spaces, punctuation, or stored data type. Check the range in Home > Conditional Formatting > Manage Rules and inspect the cell contents.

Does Duplicate Values delete duplicates?
No. The Conditional Formatting rule only changes the appearance of matching cells. To remove repeated records, use Data > Remove Duplicates, after checking the selected columns and making a backup if the data matters.

How do I highlight only the second occurrence?
For a list in A2:A20, select that range and create a formula-based conditional-formatting rule using =COUNTIF($A$2:A2,A2)>1. The first occurrence stays unhighlighted; later occurrences are flagged.

Why does Excel treat two identical-looking values as different?
They may contain invisible spaces, different punctuation, or different underlying types, such as a number and text that looks like a number. Check the formula bar, clean imported text in a helper column, and compare the cleaned values.

Why are new entries not highlighted?
The new rows may fall outside the rule’s Applies to range. Extend that range in Manage Rules, then verify the rule with a clearly visible format. For expanding lists, an Excel table can help keep data organized, but still check the rule’s scope.

Choosing the right fix

For a quick visual check, use the built-in Duplicate Values rule on the complete range. For repeated records you intend to remove, use Remove Duplicates only after defining which columns matter and protecting the original data. For messy imports, clean and standardize the values first; for custom matching behavior, use a formula and verify its references. In most cases, the feature itself is working—the mismatch is in the selected range, the comparison rule, or the contents Excel is being asked to compare.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.