A bug in Excel? Conditional formatting for marking duplicates also highlights unique valueExcel conditional...
Canadian citizen, on US no-fly list. What can I do in order to be allowed on flights which go through US airspace?
What is this waxed root vegetable?
What is better: yes / no radio, or simple checkbox?
Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?
What is a term for a function that when called repeatedly, has the same effect as calling once?
What should one use the left pedal for on an upright?
Hacker Rank: Array left rotation
Why is s'abonner reflexive?
What is the wife of a henpecked husband called?
Second-rate spelling
Is there a German word for “analytics”?
How would we write a misogynistic character without offending people?
Are there any other Chaos worshipping races?
Are small insurances worth it
Inverse of the covariance matrix of a multivariate normal distribution
Is there a ternary operator in math?
The need of reserving one's ability in job interviews
What type of postprocessing gives the effect of people standing out
What happened to QGIS 2.x
Sometimes a banana is just a banana
Real life puzzle: Unknown alphabet or shorthand
What could trigger powerful quakes on icy world?
Filling in Area Under Curve Causes Alignment Issues
Can we carry rice to Japan?
A bug in Excel? Conditional formatting for marking duplicates also highlights unique value
Excel conditional formatting based on a formulaExcel conditional formatting of rowConditional formatting Excel 2007/2010: Highlight the first cell in the row that contains duplicate values?Excel 2007 - Conditional Formatting: Compare 3 columns with text - find unique valuesConditional Formatting in excel based on another columnExcel conditional formatting only applies to first cellApplying Conditional Formatting in ExcelConditional Formatting for when cells contain part of a valueExcel - Highlight Unique Values Across a Row, Apply to Multiple RowsExcel 2010 conditional Formatting for multiple conditions
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.
If you reproduced the behavior, try to delete one of PT_ values. The other will lose the highlighting but the *736 will keep it!
Now this really looks rather like a bug than a feature. How can be this understood?
windows microsoft-excel office365 conditional-formatting duplicate
asked 54 mins ago
miroxlavmiroxlav
7,55852569
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.
If you reproduced the behavior, try to delete one of PT_ values. The other will lose the highlighting but the *736 will keep it!
Now this really looks rather like a bug than a feature. How can be this understood?
windows microsoft-excel office365 conditional-formatting duplicate
asked 54 mins ago
miroxlavmiroxlav
7,55852569
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
43 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago
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.
If you reproduced the behavior, try to delete one of PT_ values. The other will lose the highlighting but the *736 will keep it!
Now this really looks rather like a bug than a feature. How can be this understood?
windows microsoft-excel office365 conditional-formatting duplicate
asked 54 mins ago
miroxlavmiroxlav
7,55852569
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.
If you reproduced the behavior, try to delete one of PT_ values. The other will lose the highlighting but the *736 will keep it!
Now this really looks rather like a bug than a feature. How can be this understood?
windows microsoft-excel office365 conditional-formatting duplicate
windows microsoft-excel office365 conditional-formatting duplicate
asked 54 mins ago
miroxlavmiroxlav
7,55852569
asked 54 mins ago
miroxlavmiroxlav
7,55852569
edited 16 mins ago
miroxlav
asked 54 mins ago
miroxlavmiroxlav
7,55852569
asked 54 mins ago
miroxlavmiroxlav
7,55852569
asked 54 mins ago
miroxlavmiroxlav
7,55852569
7,55852569
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
43 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago
add a comment |
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
43 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago
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
43 mins ago
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
43 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago
add a comment |
2 Answers
2
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 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
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:
And the result is:
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
@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
20 mins ago
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
17 mins ago
add a 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
2 Answers
2
active
oldest
votes
2 Answers
2
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 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
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 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
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 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
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 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
answered 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
answered 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
answered 32 mins ago
cybernetic.nomadcybernetic.nomad
1,815312
1,815312
add a comment |
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:
And the result is:
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
@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
20 mins ago
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
17 mins ago
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:
And the result is:
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
@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
20 mins ago
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
17 mins ago
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:
And the result is:
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
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:
And the result is:
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
answered 32 mins ago
Saaru LindestøkkeSaaru Lindestøkke
69421032
69421032
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
@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
20 mins ago
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
17 mins ago
add a comment |
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
@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
20 mins ago
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
17 mins ago
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
I don't see the value in that. What will it bring you (and future user with the same problem) knowing whether or not this is a "bug" or a "feature"? Your original question was perfectly fine, my answer provides an answer for that.
– Saaru Lindestøkke
27 mins ago
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
25 mins ago
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
25 mins ago
@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
20 mins ago
@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
20 mins ago
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
17 mins ago
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
17 mins ago
add a 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
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
43 mins ago
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
37 mins ago