42

If I let Excel highlight two duplicate values PT_INTERNAL2859736, then also unique value *736 gets highlighted. Why this happens and how can I stop highlighting the unique value as duplicate?

I thought the Duplicate Values rule is reliable until I found this case.

enter image description here

The problem can be reproduced based on the image.

Now, if you reproduced the behavior, try to delete one of PT_ values. The other will lose the highlighting but the *736 will keep it!

         enter image description here

Is this an expected behavior of the Duplicate Values functionality (usefulness of which I am overlooking)? Or is this rather a defect which has to be reported?

  • 12
    It appears as if the * asterisk is seen as a wildcard character. Never encountered this "feature" before. Here someone did have the same issue – Saaru Lindestøkke Mar 5 at 15:38
  • 3
    @SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated... – miroxlav Mar 5 at 15:44
50

It is indeed because * is treated as a wildcard.

The way around it, is to use a formula to insert a tilde (~) in order to escape the asterisk (*) for your conditional formatting:

=COUNTIF(A:A,SUBSTITUTE(A1,"*","~*"))>1
  • 3
    Thank you. You pointed to well-known functionality, which I actually could not find connected to conditional formatting I asked about. I did a further research and attempted to answer important part of my question whether this is a bug or not. – miroxlav Mar 5 at 22:38
  • 19
    I'm always surprised when I'm reminded that the escape character in Excel is ~. – BruceWayne Mar 6 at 20:35
  • @BruceWayne: what else would it be? @ is taken, # is taken, ! is taken, $, ^, &, and * are all taken - what other special keys are open? – Selkie Mar 7 at 17:33
  • 6
    @Selkie - I'm used to `\` in Python and Javascript at the very least. (Edit: Ha, even in comments on StackExchange uses it as the escape character). [it also makes formatting tricky here it seems...] – BruceWayne Mar 7 at 17:35
  • 6
    And regex uses `\` (edit: and, it appears comments in SU do too) – cybernetic.nomad Mar 7 at 17:36
30

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 PT_INTERNAL2859736 and *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:

enter image description 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.

  • 2
    I would say this is in fact a defect since Excel's behavior is inconsistent. Even if we excuse Case 2 & 3 of your example as "format duplicate values" means "format values that find a duplicate but not the found duplicates". Still the two features described in your first link (Find and remove duplicates, official Office docs!) don't apply the same logic. "Remove duplicates" does not remove wildcard matches. If you follow the Office docs you would (1) highlight duplicates, (2) click on remove duplicates, (3) still have highlighted duplicates (where you have to find the counterparts yourself). – hsan Mar 6 at 9:59
  • 4
    I'm pretty sure if you took the time to carefully document and submit this, it would be closed for Works as Designed. – Hannover Fist Mar 6 at 19:47
  • 1
    While I upvoted the currently higher-voted answer as well, this one is my favorite, as it actually argues that this is not a bug. I do appreciate the other answers' inclusion of solutions to getting '*' to behave as a normal character though. – zr00 Mar 7 at 17:57
  • 1
    I say WNB (why not both). Just because it's very poorly documented (which applies to everything in Excel anyway) doesn't mean that it isn't a mistake in the first place to use a tilde as escape. – Carl Witthoft Mar 7 at 18:18
14

If you Google for excel asterisk wildcard conditional formatting you'll find someone with the same issue.

There the proposed solution is to use a custom formula to check if the value is a duplicate.

The formula looks as follows:
=SUMPRODUCT(--(("~"&A2)=("~"&$A$2:$A$4)))>1

enter image description here

And the result is:

enter image description here

  • Sorry for extending original question, I thought the issues are connected. It would be good if that could be understood as a whole because the unique value is highlighted also when duplicates are not so it is easy to get into troubles with that, when not checking values in detail, but relying on formatting. – miroxlav Mar 5 at 15:56
  • 1
    @miroxlav all fine and dandy, but what will it bring the community (you including) when someone here answers "Yes, that's a bug"? If you feel strongly about this I would suggest submitting a bug report in Excel via File -> Feedback. Don't hold your breath though, a short Google trip shows that Microsoft does not seem to be very responsive. – Saaru Lindestøkke Mar 5 at 16:01
  • Are you afraid to put such a conclusion into your answer? You can either extend your answer showing that the sole highlighted value has its logical place in the entire functionality or you can write your opinion that it is a bug. – miroxlav Mar 5 at 16:04
  • 1
    Answer to: what will it bring the community (you including) when someone here answers "Yes, that's a bug"? – this will have multiple benefits: 1. they will start using the functionality with care 2. someone will maybe report this at Microsoft 3. they will expect to get this fixed in some future release. If someone will claim this is not a bug and will show overlooked use case, this will be also a great contribution. Thus this is a good subjective question along with the help section you linked. Therefore I rejected your edits and edited the question for better clarity. – miroxlav Mar 5 at 16:41
  • 1
    Let us continue this discussion in chat. – miroxlav Mar 5 at 16:44

protected by Community Oct 2 at 6:05

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.