Forum Discussion
Conditional formatting duplicates doesn't highlight them
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?
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.
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????
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.).
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...
HansVogelaar I figured as much, but thought I'd ask in case I missed something obvious. Bug reported!
