Has Wakanda ever accepted refugees?

Wardrobe above a wall with fuse boxes

Relationship between the symmetry number of a molecule as used in rotational spectroscopy and point group

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

When was drinking water recognized as crucial in marathon running?

The Ohm's law calculations of the parts do not agree with the whole

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

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

Lock enemy's y-axis when using Vector3.MoveTowards to follow the player

What is a term for a function that when called repeatedly, has the same effect as calling once?

Giving a talk in my old university, how prominently should I tell students my salary?

1970s scifi/horror novel where protagonist is used by a crablike creature to feed its larvae, goes mad, and is defeated by retraumatising him

Four buttons on a table

Where is this quote about overcoming the impossible said in "Interstellar"?

Can we carry rice to Japan?

Make me a metasequence

Did Amazon pay $0 in taxes last year?

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

Book about a time-travel war fought by computers

How to disable or uninstall iTunes under High Sierra without disabling SIP

Split a number into equal parts given the number of parts

Inconsistent behaviour between dict.values() and dict.keys() equality in Python 3.x and Python 2.7

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?

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?

32

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.

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

share|improve this question

edited yesterday

miroxlav

asked yesterday

miroxlavmiroxlav

7,81352773

• 
  
  
  

  
  8
  

  
  
  
  
  
  

  
  
  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 | 

32

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.

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

share|improve this question

edited yesterday

miroxlav

asked yesterday

miroxlavmiroxlav

7,81352773

• 
  
  
  

  
  8
  

  
  
  
  
  
  

  
  
  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

share|improve this question

edited yesterday

miroxlav

asked yesterday

miroxlavmiroxlav

7,81352773

share|improve this question

edited yesterday

miroxlav

asked yesterday

miroxlavmiroxlav

7,81352773

share|improve this question

edited yesterday

miroxlav

asked yesterday

miroxlavmiroxlav

7,81352773

asked yesterday

miroxlavmiroxlav

7,81352773

asked yesterday

miroxlavmiroxlav

7,81352773

3 Answers
3

active

oldest

votes

36

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

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

• 
  
  
  

  
  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
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  I'm always surprised when I'm reminded that the escape character in Excel is ~.
  
  – BruceWayne
  2 hours ago
  

  
  
  
  

add a comment | 

17

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.

share|improve this answer

edited 14 hours ago

answered yesterday

miroxlavmiroxlav

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
  13 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
  3 hours ago
  

  
  
  
  

add a comment | 

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

And the result is:

share|improve this answer

edited yesterday

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

• 
  
  
  

  
  

  
  
  
  
  
  

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


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

36

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

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

• 
  
  
  

  
  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
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  I'm always surprised when I'm reminded that the escape character in Excel is ~.
  
  – BruceWayne
  2 hours ago
  

  
  
  
  

add a comment | 

36

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

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

• 
  
  
  

  
  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
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  I'm always surprised when I'm reminded that the escape character in Excel is ~.
  
  – BruceWayne
  2 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

share|improve this answer

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

=COUNTIF(A:A,SUBSTITUTE(A1,"*","~*"))>1

share|improve this answer

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

answered yesterday

cybernetic.nomadcybernetic.nomad

2,135415

17

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.

share|improve this answer

edited 14 hours ago

answered yesterday

miroxlavmiroxlav

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
  13 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
  3 hours ago
  

  
  
  
  

add a comment | 

17

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.

share|improve this answer

edited 14 hours ago

answered yesterday

miroxlavmiroxlav

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
  13 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
  3 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.

share|improve this answer

edited 14 hours ago

answered yesterday

miroxlavmiroxlav

7,81352773

share|improve this answer

edited 14 hours ago

answered yesterday

miroxlavmiroxlav

7,81352773

answered yesterday

miroxlavmiroxlav

7,81352773

answered yesterday

miroxlavmiroxlav

7,81352773

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

And the result is:

share|improve this answer

edited yesterday

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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

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

And the result is:

share|improve this answer

edited yesterday

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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:

share|improve this answer

edited yesterday

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

share|improve this answer

edited yesterday

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033

answered yesterday

Saaru LindestøkkeSaaru Lindestøkke

80421033