After further research of the behavior and documentation, I can answer the question from high-level perspective:
This is rather an undocumented behavior than a defect.
Other answers reminded us of use of wildcard characters (
~) in Excel formulas. The question unanswered before is if they are expected also in conditional formatting. Microsoft documents the Duplicate Values mode of conditional formatting in the following articles:
Nowhere in these articles is mentioned that the internal algorithm searching for duplicates still respects wildcard characters (
~). On the contrary, support of wildcards is explicitly named in functionalities, where presence of wildcards is obvious and expected:
But back to duplicates: it can be discussed whether sample values
*736 are duplicates by definition. By common sense, no. In Excel, obviously yes. It is possible that Excel simply uses its standard search algorithm (which honors wildcard characters) also for searching of cells with duplicate values... and the results of showing duplicate values can be quite unexpected as you can see in the question or here:
Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.
And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.
Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.