A bug in Excel? Conditional formatting for marking duplicates also highlights unique valueExcel conditional...

Is there a math equivalent to the conditional ternary operator?

How do we objectively assess if a dialogue sounds unnatural or cringy?

What can I do if someone tampers with my SSH public key?

PTIJ: Why can't I sing about soda on certain days?

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

Wardrobe above a wall with fuse boxes

How can neutral atoms have exactly zero electric field when there is a difference in the positions of the charges?

Find maximum of the output from reduce

Can I solder 12/2 Romex to extend wire 5 ft?

It doesn't matter the side you see it

Would the melodic leap of the opening phrase of Mozart's K545 be considered dissonant?

Misplaced tyre lever - alternatives?

Can a space-faring robot still function over a billion years?

I can't die. Who am I?

Create chunks from an array

PTIJ: What dummy is the Gemara referring to?

I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?

I encountered my boss during an on-site interview at another company. Should I bring it up when seeing him next time?

GPL code private and stolen

Split a number into equal parts given the number of parts

Reason why dimensional travelling would be restricted

Did Amazon pay $0 in taxes last year?

Why won't the strings command stop?

How to get the first element while continue streaming?



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?













34















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



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



enter image description here



The problem can be reproduced based on the image.



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



         enter image description here



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










share|improve this question




















  • 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
















34















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



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



enter image description here



The problem can be reproduced based on the image.



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



         enter image description here



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










share|improve this question




















  • 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














34












34








34


2






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



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



enter image description here



The problem can be reproduced based on the image.



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



         enter image description here



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










share|improve this question
















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



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



enter image description here



The problem can be reproduced based on the image.



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



         enter image description here



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







windows microsoft-excel office365 conditional-formatting duplicate






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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














  • 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










3 Answers
3






active

oldest

votes


















39














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





share|improve this answer



















  • 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
    9 hours ago



















19














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:



enter image description here



Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.




And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.




Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.






share|improve this answer


























  • 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



















13














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



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



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



enter image description here



And the result is:



enter image description here






share|improve this answer


























  • 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











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
});


}
});














draft saved

draft discarded


















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









39














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





share|improve this answer



















  • 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
    9 hours ago
















39














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





share|improve this answer



















  • 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
    9 hours ago














39












39








39







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





share|improve this answer













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






share|improve this answer












share|improve this answer



share|improve this answer










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
    9 hours ago














  • 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
    9 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
9 hours ago





I'm always surprised when I'm reminded that the escape character in Excel is ~.

– BruceWayne
9 hours ago













19














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:



enter image description here



Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.




And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.




Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.






share|improve this answer


























  • 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
















19














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:



enter image description here



Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.




And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.




Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.






share|improve this answer


























  • 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














19












19








19







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:



enter image description here



Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.




And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.




Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.






share|improve this answer















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:



enter image description here



Based on the above, I would say this is not a defect, but an unexpected and undocumented behavior.




And this poses a problem in real-life scenarios. You were not warned, that you need a special formulas to discover real duplicates. You were just presented by Duplicate values conditional formatting type without further explanation. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. I almost deleted unique values considering them to have duplicates somewhere in the list.




Current behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. If something should be fixed, it is at least the documentation.







share|improve this answer














share|improve this answer



share|improve this answer








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



















  • 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











13














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



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



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



enter image description here



And the result is:



enter image description here






share|improve this answer


























  • 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
















13














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



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



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



enter image description here



And the result is:



enter image description here






share|improve this answer


























  • 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














13












13








13







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



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



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



enter image description here



And the result is:



enter image description here






share|improve this answer















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



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



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



enter image description here



And the result is:



enter image description here







share|improve this answer














share|improve this answer



share|improve this answer








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



















  • 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


















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

El tren de la libertad Índice Antecedentes "Porque yo decido" Desarrollo de la...

Castillo d'Acher Características Menú de navegación

Puerta de Hutt Referencias Enlaces externos Menú de navegación15°58′00″S 5°42′00″O /...