A bug in Excel? Conditional formatting for marking duplicates also highlights unique valueExcel conditional formatting based on a formulaExcel 2010 conditional formatting: selectively highlighting duplicatesConditional 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 for when cells contain part of a valueConditional formatting so that each row highlights duplicated valuesExcel - Highlight Unique Values Across a Row, Apply to Multiple RowsExcel 2010 Conditional Formatting Accross multiple tablesHighlighting duplicate rows based on 2 columnsExcel conditional formatting: formula blanks treated inconsistently?
Are there other characters in the Star Wars universe who had damaged bodies and needed to wear an outfit like Darth Vader?
Why is it "take a leak?"
Reason why dimensional travelling would be restricted
Why won't the strings command stop?
Should I use HTTPS on a domain that will only be used for redirection?
A peculiar integral identity
Why did the Cray-1 have 8 parity bits per word?
Can an earth elemental drown/bury its opponent underground using earth glide?
PTIJ: Aharon, King of Egypt
What is a term for a function that when called repeatedly, has the same effect as calling once?
How to kill a localhost:8080
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
Is divide-by-zero a security vulnerability?
Book about a time-travel war fought by computers
Was it really inappropriate to write a pull request for the company I interviewed with?
Correct physics behind the colors on CD (compact disc)?
Wardrobe above a wall with fuse boxes
I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?
Why is my Contribution Detail Report (native CiviCRM Core report) not accurate?
Can the Shape Water Cantrip be used to manipulate blood?
If nine coins are tossed, what is the probability that the number of heads is even?
Deal the cards to the players
What can I do if someone tampers with my SSH public key?
Meaning of word ягоза
A bug in Excel? Conditional formatting for marking duplicates also highlights unique value
Excel conditional formatting based on a formulaExcel 2010 conditional formatting: selectively highlighting duplicatesConditional 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 for when cells contain part of a valueConditional formatting so that each row highlights duplicated valuesExcel - Highlight Unique Values Across a Row, Apply to Multiple RowsExcel 2010 Conditional Formatting Accross multiple tablesHighlighting duplicate rows based on 2 columnsExcel conditional formatting: formula blanks treated inconsistently?
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
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
9
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
yesterday
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday
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
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
edited yesterday
miroxlav
asked yesterday
miroxlavmiroxlav
7,81352773
7,81352773
9
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
yesterday
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday
add a comment |
9
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
yesterday
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday
9
9
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
yesterday
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
yesterday
2
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday
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
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
yesterday
7
I'm always surprised when I'm reminded that the escape character in Excel is~
.
– BruceWayne
8 hours ago
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.
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
19 hours ago
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
9 hours 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:=SUMPRODUCT(--(("~"&A2)=("~"&$A$2:$A$4)))>1
And the result is:
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
Let us continue this discussion in chat.
– miroxlav
yesterday
|
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
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
yesterday
7
I'm always surprised when I'm reminded that the escape character in Excel is~
.
– BruceWayne
8 hours ago
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
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
yesterday
7
I'm always surprised when I'm reminded that the escape character in Excel is~
.
– BruceWayne
8 hours ago
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
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 yesterday
cybernetic.nomadcybernetic.nomad
2,145415
2,145415
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
yesterday
7
I'm always surprised when I'm reminded that the escape character in Excel is~
.
– BruceWayne
8 hours ago
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
yesterday
7
I'm always surprised when I'm reminded that the escape character in Excel is~
.
– BruceWayne
8 hours ago
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
yesterday
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
yesterday
7
7
I'm always surprised when I'm reminded that the escape character in Excel is
~
.– BruceWayne
8 hours ago
I'm always surprised when I'm reminded that the escape character in Excel is
~
.– BruceWayne
8 hours ago
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.
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
19 hours ago
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
9 hours ago
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.
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
19 hours ago
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
9 hours ago
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.
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.
edited 20 hours ago
answered yesterday
miroxlavmiroxlav
7,81352773
7,81352773
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
19 hours ago
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
9 hours ago
add a comment |
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
19 hours ago
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
9 hours ago
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
19 hours ago
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
19 hours ago
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
9 hours ago
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
9 hours 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:=SUMPRODUCT(--(("~"&A2)=("~"&$A$2:$A$4)))>1
And the result is:
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
Let us continue this discussion in chat.
– miroxlav
yesterday
|
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:
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
Let us continue this discussion in chat.
– miroxlav
yesterday
|
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:
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:
edited yesterday
answered yesterday
Saaru LindestøkkeSaaru Lindestøkke
80921133
80921133
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
Let us continue this discussion in chat.
– miroxlav
yesterday
|
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
Let us continue this discussion in chat.
– miroxlav
yesterday
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
yesterday
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
yesterday
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
yesterday
@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
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
1
1
Let us continue this discussion in chat.
– miroxlav
yesterday
Let us continue this discussion in chat.
– miroxlav
yesterday
|
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
9
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
yesterday
2
@SaaruLindestøkke – Can you find this documented? To me it looks like a defect. Question updated...
– miroxlav
yesterday