Formatting Issues

Why Excel Duplicate Values Conditional Formatting Misses Formula-Like Text

Amos GikundaAmos Gikunda Nov 12, 2026 868 views

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.

Why Excel Duplicate Values Conditional Formatting Misses Formula-Like Text
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the full data range

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.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

3
Enter an exact-match duplicate formula

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.

4
Choose the highlight format

Select Format, choose a fill color or other formatting, and then click OK twice to apply the rule.

Use a formula-based conditional-formatting rule for exact text matches
Exact text comparison: The EXACT function treats uppercase and lowercase letters as different characters. If case differences should count as duplicates, standardize the text case first or use a helper column.
Free Microsoft Office alternative

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.

Apply formula-based conditional formatting for precise duplicate-text checks.Use familiar spreadsheet commands and a Microsoft Office-style interface.Open, edit, and save Microsoft Excel .xlsx and .xls files.Move existing workbook data to WPS Spreadsheets with seamless format compatibility.
microsoft office alternative - wps office

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.