Why Excel Duplicate Values Conditional Formatting Misses Formula-Like Text
Question details
The user wants to understand why Excel's built-in Duplicate Values conditional-formatting rule does not consistently highlight duplicate entries stored as text when those entries resemble formulas.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user applies the Duplicate Values conditional-formatting rule to cells containing text values with leading apostrophes, equal signs, asterisks, question marks, or formula-like strings.
- Observed behavior
- Some values that appear identical are not highlighted as duplicates. Removing equal signs can change which cells Excel highlights.
Confirm that the affected cells contain text rather than active formulas by selecting a cell and checking the Formula Bar. If the values begin with an apostrophe, Excel stores them as text even though the apostrophe is not displayed in the cell.
Use a formula-based conditional-formatting rule for exact text matches
Use this method when the built-in Duplicate Values rule treats formula-like text or wildcard characters unexpectedly.
Excel's Duplicate Values rule may interpret asterisks (*) and question marks (?) as wildcard characters in some formula-like text values. An EXACT-based rule compares the displayed text literally and avoids wildcard matching.
Select the cells to check for duplicates, such as A2:C5. Make sure the active cell in the selection is the upper-left cell, A2 in this example.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
In the formula box, enter =SUMPRODUCT(--EXACT(A2,$A$2:$C$5))>1. Replace A2 and $A$2:$C$5 with the first cell and complete range of your own selection.
Select Format, choose a fill color or other formatting, and then click OK twice to apply the rule.

Remove or replace formula-like characters when the values do not need them
Use this workaround only when the equal signs or wildcard characters are not required as part of the stored text.
Create Exact Duplicate Highlighting in WPS Spreadsheets
If Excel conditional formatting handles your formula-like text inconsistently, try WPS Office as a free, lightweight alternative. WPS Spreadsheets provides a familiar spreadsheet interface and conditional-formatting tools, helping you move existing worksheets and apply formula-based duplicate checks with minimal adjustment.

Frequently Asked Questions
Why does Excel not highlight duplicate text that starts with an equal sign?
Excel may process formula-like text specially inside the built-in Duplicate Values rule, even when the value is stored as text with a leading apostrophe. Use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format for more predictable matching.
Do asterisks and question marks affect Excel duplicate formatting?
They can. Excel may interpret * and ? as wildcard characters in this conditional-formatting context, which can cause entries that look identical to be skipped or evaluated unexpectedly.
How can I highlight duplicate text literally in Excel?
Select your range and create a formula rule from Home > Conditional Formatting > New Rule. For a range beginning at A2, use =SUMPRODUCT(--EXACT(A2,$A$2:$C$5))>1 and set the Applies to range to match your data.
Will the EXACT duplicate formula treat upper- and lowercase text as the same?
No. EXACT is case-sensitive, so "Text" and "text" are not treated as duplicates. Convert values to a consistent case first if you need case-insensitive matching.




