Excel may not be finding duplicates because the rule is checking the wrong range, the entries only look alike, or the cells contain hidden spaces or different underlying values. For a quick check in Microsoft Excel for Microsoft 365 on Windows, select the data and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; if that misses a match, use the checks below to identify why.
Start with the range and the rule
Excel’s built-in Duplicate Values rule compares cell values within the range to which the conditional formatting rule applies. If you select only part of a list, Excel cannot flag a match that falls outside that selection. If the rule already exists, it may also be limited to an old range—for example, A2:A50 when your list now continues to A120.
Check the rule’s scope by selecting a cell in the list and opening Home > Conditional Formatting > Manage Rules. In the rule manager, inspect “Applies to” and confirm that it covers the intended cells. If necessary, edit the range or reselect the full list and create a new rule. In the Duplicate Values dialog, make sure the first option is set to Duplicate, not Unique.
Also check whether you selected one column or several. A duplicate-values rule applied to A2:C100 looks for repeated values anywhere in that entire block. It does not mean “find repeated records” by comparing each row as a whole. Conversely, selecting just column A will not notice matching values that appear only in column B.
Confirm what Excel considers a duplicate
A cell’s displayed appearance is not always its stored value. Two cells can show the same text but contain different characters; two dates can display the same day while one has a hidden time; and a number stored as text may behave differently from a numeric value in some comparisons. Number formatting can also make different values look identical: for example, 1.234 and 1.235 may both display as 1.23 if the cells show only two decimal places.
Excel’s ordinary duplicate checks are not case-sensitive: “North” and “north” are generally treated as the same text for this purpose. If capitalization matters to your definition, use a case-sensitive formula instead. Decide what counts as a duplicate before changing data: an exact repeated entry, an entry after trimming spaces, a case-sensitive match, or a repeated combination of fields are different tests.
Formatting alone does not make values duplicates. A date and a number can share an underlying serial value, while two dates that look alike can differ because one includes a time component. To inspect a suspicious cell, select it and examine the formula bar; changing the number format to General can also reveal underlying numeric values. For dates with time values, a helper formula such as INT can isolate the date portion, but only use that if time is not meaningful in your data.
Look for spaces and invisible characters
Extra spaces are a common cause of apparent mismatches. “Maple Street” and “Maple Street ” look identical in many cells, but the second value has a trailing space. Leading spaces create the same problem. Data copied from websites, PDFs, and other systems may also contain nonbreaking spaces, which look like ordinary spaces but are not removed by a simple visual inspection.
For a quick cleanup in a helper column, assume the original text is in A2. Enter this formula in B2 and fill it down:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to one; CLEAN removes many nonprinting characters; SUBSTITUTE changes the common nonbreaking space, character 160, into an ordinary space. This is useful for text, but it is not a universal repair for every unusual character or every language-specific whitespace character. Compare the cleaned results, and keep the original column until you have verified the cleanup.
To highlight duplicates based on cleaned text, first place the cleaned values in B2:B100. With those cleaned values in B2:B100, select B2:B100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:
=COUNTIF($B$2:$B$100,B2)>1
Choose a format and confirm the rule. The absolute references keep the comparison range fixed as Excel evaluates each cell; the relative B2 reference changes for each row. This highlights every occurrence of a repeated cleaned value, including the first one.
Check whether entries are numbers, text, or dates
Imported data often mixes types. One product code may be the text value “00125” while another is the number 125 displayed with a custom format that adds zeros. They may appear similar but are not the same stored value. Likewise, a cell containing a number entered as text can produce confusing results in formulas and comparisons. Standardize the data type before checking duplicates: decide whether codes should remain text, and ensure genuine quantities are numeric.
Dates deserve a closer look. Excel stores dates as numbers, and a date-time entry can include a fractional time that is hidden by the cell’s display format. For instance, two cells may both display 11/2/2026, even though one stores midnight and the other stores 3:00 p.m. They are different date-time values. If a duplicate means “same calendar day,” remove or ignore the time portion in a helper column rather than relying on what the cell displays.
When a mismatch is puzzling, compare the underlying values in a spare column or temporarily use a more revealing number format. You can also test whether a cell is numeric with ISNUMBER or whether it is text with ISTEXT. Avoid converting an entire column blindly: converting codes with leading zeros to numbers, for example, can destroy information that is part of the code.
Use a formula when the built-in rule is not enough
A formula-based conditional formatting rule gives you more control over the range and the definition of a match. With the values to check in A2:A100, select A2:A100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:
=COUNTIF($A$2:$A$100,A2)>1
Set a format and confirm. The formula returns TRUE when the current value occurs more than once in the comparison range. It also marks the first occurrence, which is often useful when you want to see every member of a duplicate group. Keep the selected range and formula reference aligned: if the selected range begins at A2, the formula should refer to A2, not A1.
To mark only the second and later appearances, assume the list still begins in A2. Select A2:A100 and use this rule:
=COUNTIF($A$2:A2,A2)>1
Here the comparison range grows as the rule moves down the list. The first occurrence is not highlighted; a later occurrence is. This can help identify which rows could be removed while preserving the first record. COUNTIF treats wildcard characters such as * and ? specially in its criteria, so if your data contains those characters as literal text, test the results or use a different comparison method.
For a case-sensitive check, assume the values are in A2:A100 and select that same range before creating a formula rule. Use:
=SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1
EXACT compares text with capitalization taken into account, and SUMPRODUCT counts the matches. This is slower on very large ranges than a simple rule, so use a bounded range rather than whole-column references where practical.
Find duplicate records across multiple columns
If the question is whether a whole record repeats, decide which fields define a matching record. For example, two rows might be duplicates only when both the customer ID in column A and the order date in column B match. A duplicate rule over A:B would instead flag a repeated ID or a repeated date anywhere in the selection, which is a different test.
With customer IDs in A2:A100 and order dates in B2:B100, select the rows you want to format and create a formula rule using:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
COUNTIFS counts rows where both conditions are true. In this example, it highlights each row whose ID-and-date combination occurs more than once. Add another range-and-criterion pair for each additional field that must match. Keep all compared ranges the same size, and ensure the formula’s row references correspond to the first row in the selected range.
Check filters, hidden rows, and visual formatting
Filtering does not change the underlying values, but it can hide some rows from view. A duplicate may be present in a filtered-out row, so clear or review filters before concluding that a value appears only once. Hidden rows can cause the same kind of confusion. Conditional formatting may still apply to cells that are hidden, but you cannot see their highlighting until they are visible.
Also distinguish “Excel did not find a duplicate” from “I cannot see the highlight.” The chosen fill or font color might be hard to distinguish from the cell’s existing formatting, another conditional formatting rule may affect the appearance, or the rule may apply to a different range. In Manage Rules, check both the applies-to range and the rule order. Try a highly visible format temporarily to see whether the rule is firing.
For a direct count, put a check formula in a spare column. With the list in A2:A100 and the value to test in A2, enter this in B2 and fill down:
=COUNTIF($A$2:$A$100,A2)
A result of 1 means the value occurs once according to COUNTIF; a result above 1 indicates multiple matches. This check separates a comparison problem from a formatting problem. If the count is surprising, inspect the range, data types, and text cleanup before deleting anything.
Do not confuse highlighting with removing duplicates
Conditional formatting only marks cells; it does not delete or merge records. If you want to remove repeated values, Excel’s Data > Remove Duplicates command changes the data, so make a copy of the workbook or the data first. Select the relevant table or range, open the command, and choose which columns define a duplicate. Selecting only an ID column can remove rows that share an ID even if other fields differ; selecting every column requires the entire row to match.
For a safer review, filter or highlight candidate duplicates first, verify that they are genuinely redundant, and then remove them. Keep in mind that two records with the same name are not necessarily errors: they may refer to different people. Establish a reliable key—such as an account number or a combination of fields—rather than treating every repeated word as a duplicate record.
Frequently Asked Questions
Why does Excel highlight values that are not next to each other?
Duplicates do not need to be adjacent. Excel compares values across the rule’s applied range, so matching values far apart in the list can both be highlighted.
Does Excel treat uppercase and lowercase text as different?
The built-in duplicate-value rule is generally case-insensitive, so entries such as “West” and “west” count as duplicates. Use an EXACT-based formula rule if capitalization must distinguish entries.
Why do two cells that look identical have different counts?
They may contain leading or trailing spaces, nonbreaking spaces, hidden characters, different data types, or hidden time values. Inspect the formula bar and test a cleaned helper column before changing the source data.
Why are duplicates missed after I add new rows?
The existing rule may still apply only to the original range. Open Home > Conditional Formatting > Manage Rules and expand “Applies to,” or recreate the rule over the complete list. Excel tables can make expanding data easier to manage, but confirm the rule’s actual range.
Can Excel find duplicate rows instead of duplicate cells?
Yes. Use a formula such as COUNTIFS to compare the fields that define a repeated record. A standard Duplicate Values rule across multiple columns checks individual cell values, not complete row combinations.
When to use another approach
For a one-time check, the built-in Duplicate Values rule is usually the fastest option. Use a formula rule when you need case sensitivity, a growing occurrence count, or a combination of fields. Use a helper column when the source needs normalization, and keep that column visible during review so the matching logic is transparent. For recurring imports, a repeatable cleanup process is more dependable than manually fixing individual cells each time.
Excel for Mac has the same core conditional formatting feature, though the ribbon layout and dialog appearance can vary slightly by release; the Windows path above may look different. Excel for the web also supports conditional formatting, but its rule-editing controls may differ from the desktop app. If a workbook is shared across versions, verify the rule after opening it in the version people will use.
Related
- Python pandas Excel Formatting: A Practical Guide to Styles
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel IRR #DIV/0! Error: Why It Happens and How to FixIf Excel shows #DIV/0! beside IRR, the error is usually in one of the cash-flow formulas or cells:…
- Excel Unable to Remove a Page Break? Find the Right FixIf Excel will not remove a page break, first check whether it is manual or automatic: select the…
- Excel Scatter Plot Not Using X Values? Fix ItIf an Excel scatter plot is not using your intended X values, change the chart to an XY…
- Excel Text Box Disappeared? How to Find and Restore a Missing BoxAn Excel text box that disappears is often still in the workbook: it may be hidden in the…
- Excel Name Already Exists Error: Causes and FixesThe Excel name already exists message usually means the name you are trying to create or bring into…
- Excel UNIQUE Spill Error: Causes, Fixes, and ExamplesIf Excel shows #SPILL! for a UNIQUE formula, it means Excel cannot place the formula’s results in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.