A bug in Excel? Conditional formatting for marking duplicates also highlights unique value
Clash Royale CLAN TAG#URR8PPP
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.
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!
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?
windows microsoft-excel office365 conditional-formatting duplicate
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
add a comment |
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.
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!
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?
windows microsoft-excel office365 conditional-formatting duplicate
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
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
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44
add a comment |
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.
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!
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?
windows microsoft-excel office365 conditional-formatting duplicate
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
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.
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!
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?
windows microsoft-excel office365 conditional-formatting duplicate
windows microsoft-excel office365 conditional-formatting duplicate
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
edited Mar 5 at 16:37
miroxlav
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
asked Mar 5 at 15:27
miroxlavmiroxlav
8,02652874
8,02652874
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
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44
add a comment |
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
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44
12
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
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
2
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44
add a comment |
3 Answers
3
active
oldest
votes
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
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
2
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
18
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
5
@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
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
add a comment |
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:
- Find and remove duplicates
- Filter for unique values or remove duplicate values
- Filter for or remove duplicate values
- Highlight patterns and trends with conditional formatting
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:
- standard search box
- functions like SEARCH(), SEARCHB(), COUNTIF(), ...
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:
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.
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
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
3
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.
– zarose
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
add a comment |
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
And the result is:
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
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
|
show 1 more comment
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "3"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411496%2fa-bug-in-excel-conditional-formatting-for-marking-duplicates-also-highlights-un%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
2
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
18
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
5
@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
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
add a comment |
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
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
2
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
18
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
5
@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
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
add a comment |
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
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
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
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
answered Mar 5 at 15:49
cybernetic.nomadcybernetic.nomad
2,621617
2,621617
2
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
18
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
5
@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
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
add a comment |
2
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
18
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
5
@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
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
2
2
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
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
18
18
I'm always surprised when I'm reminded that the escape character in Excel is
~.– BruceWayne
Mar 6 at 20:35
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
@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
5
5
@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
@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
5
5
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
And regex uses `` (edit: and, it appears comments in SU do too)
– cybernetic.nomad
Mar 7 at 17:36
add a comment |
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:
- Find and remove duplicates
- Filter for unique values or remove duplicate values
- Filter for or remove duplicate values
- Highlight patterns and trends with conditional formatting
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:
- standard search box
- functions like SEARCH(), SEARCHB(), COUNTIF(), ...
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:
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.
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
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
3
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.
– zarose
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
add a comment |
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:
- Find and remove duplicates
- Filter for unique values or remove duplicate values
- Filter for or remove duplicate values
- Highlight patterns and trends with conditional formatting
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:
- standard search box
- functions like SEARCH(), SEARCHB(), COUNTIF(), ...
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:
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.
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
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
3
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.
– zarose
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
add a comment |
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:
- Find and remove duplicates
- Filter for unique values or remove duplicate values
- Filter for or remove duplicate values
- Highlight patterns and trends with conditional formatting
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:
- standard search box
- functions like SEARCH(), SEARCHB(), COUNTIF(), ...
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:
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.
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
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:
- Find and remove duplicates
- Filter for unique values or remove duplicate values
- Filter for or remove duplicate values
- Highlight patterns and trends with conditional formatting
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:
- standard search box
- functions like SEARCH(), SEARCHB(), COUNTIF(), ...
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:
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.
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
edited Mar 6 at 9:04
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
answered Mar 5 at 22:26
miroxlavmiroxlav
8,02652874
8,02652874
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
3
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.
– zarose
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
add a comment |
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
3
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.
– zarose
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
2
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
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
3
3
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
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
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.
– zarose
Mar 7 at 17:57
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.
– zarose
Mar 7 at 17:57
1
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
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
add a comment |
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
And the result is:
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
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
|
show 1 more comment
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
And the result is:
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
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
|
show 1 more comment
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
And the result is:
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
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
And the result is:
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
edited Mar 5 at 21:52
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
answered Mar 5 at 15:50
Saaru LindestøkkeSaaru Lindestøkke
82021133
82021133
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
|
show 1 more comment
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
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
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
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
@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
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
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
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
1
Let us continue this discussion in chat.
– miroxlav
Mar 5 at 16:44
Let us continue this discussion in chat.
– miroxlav
Mar 5 at 16:44
|
show 1 more comment
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411496%2fa-bug-in-excel-conditional-formatting-for-marking-duplicates-also-highlights-un%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
Mar 5 at 15:44