Conditional formatting duplicates doesn't highlight them

[Jan Karel Pieterse]

I have the strangest thing. I have a workbook with three columns listing 4 rows of formulas preceded by an apostrophe, to convert them to text. Each row contains the same formula. I can prove it by doing a comparison =B2=A2, which yields TRUE. But if I try to highlight them using Conditional formatting, highlight duplicates, nothing gets highlighted! (see attached file and screen-shot below).

What am I missing?

[#2]

@Jan Karel Pieterse 

It looks like Excel is trying to be too clever once again. Even though the apostrophes cause the values to be text, I suspect that Excel still sees formulas.

If you delete all the = signs from A2:C5, the cells will be highlighted.

But it gets weirder. If you remove the = from A2 and B2, Excel will highlight A2, B2 and C2.

I'd report a bug...

[#3]

@Hans Vogelaar I figured as much, but thought I'd ask in case I missed something obvious. Bug reported!

[#4]

@Jan Karel Pieterse 

Very interesting case.  It appears Excel reads the '*' as a wildcard in this context. It seems to do the same for '?' as well.  Initially, I thought it was something with the functions so that's why you see some made up functions in the cap below.

[#5]

@Patrick2788 Nice find. I wonder though, since both formulas in A2 and B2 are identical, the presence of wild-chard characters shouldn't make a difference; they are still duplicates. Looks like their algorithm doesn't escape wild-card characters?

Anyways, I tried some more combinations and here it gets interesting. I have this:

Next, I do a search and replace on row 4, removing the = signs. This is what happens:

Huh????

[#6]

@Jan Karel Pieterse 

It's interesting that this quirk doesn't seem to be documented in any support article nor in the more in depth articles at https://learn.microsoft.com/. I even dug through some old Excel user guides in the wayback machine with no luck.  For grins I ran it by co-pilot and it merely confirmed * and ? are treated as wildcards.  Co-pilot tends to be all over the place in Excel (it's not yet aware of newer functions like TAKE, DROP, WRAPROWS, etc.) but can be OK on facts (Oddly, it is aware of thunks.).

[#7]

@Patrick2788 

Yes it is odd indeed. IMO the fact it seems to interpret them as wildcard characters is a bug leading to unexpected behavior potentially giving both false positives and false negatives. People rely on the highlight duplicates feature to do precisely that: highlight the duplicates.

That was what I was trying to do with these formulas (placed in the cell as text): spot which were different!