Steve_Brady
asked on
Conditional Formatting (highlighting duplicates) oddity in Excel
Hello,
I've noticed something in Excel (2007) which seems very odd when using:
Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values >
and certain characters are present. I'm first interested in knowing if the same thing happens in all installations of Excel or just mine -- and if it does, I'm very curious to know why.
Trying to adequately explain what happens (even with an uploaded file or screenshots) would, I'm afraid, be problematic but you can create the same thing in only a minute or two:
Open a blank spreadsheet and in some single column, enter the following values in any four cells:
1st cell: >3d
2nd cell: >3d
3rd cell: a
4th cell: b
Now select that entire column and go to
Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values >
1st drop down menu > Duplicate
2nd drop down menu > Light Red Fill with... (any choice is OK)
In my Excel, the two cells with ">3d" are highlighted as expected but each of the following maneuvers and its result seems very odd to me:
1) If I delete either of the highlighted entries, the remaining cell is still highlighted -- even though it no longer has a duplicate. (Thus the test has produced a false positive.)
2) If both ">3d" entries are present and I delete either "a" or the "b" the shading goes away, (a false negative).
3) If I remove any of the three characters in ">3d" (i.e. ">" or "3" or "d"), the highlighting goes away for that entry.
4) I can replace the ">" with "<" and get the same result.
5) I can replace "3" with any number and/or "d" with any text and get the same result.
6) If I go back to: Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values > 1st drop down menu > and select "Unique" in place of "Duplicate," the unexpected or odd behavior persists.
7) If I go to Data tab > Remove Duplicates, it eliminates one of the duplicates as it should.
Again, I'm curious to know if Excel does this for all users and if so, why.
Thanks
I've noticed something in Excel (2007) which seems very odd when using:
Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values >
and certain characters are present. I'm first interested in knowing if the same thing happens in all installations of Excel or just mine -- and if it does, I'm very curious to know why.
Trying to adequately explain what happens (even with an uploaded file or screenshots) would, I'm afraid, be problematic but you can create the same thing in only a minute or two:
Open a blank spreadsheet and in some single column, enter the following values in any four cells:
1st cell: >3d
2nd cell: >3d
3rd cell: a
4th cell: b
Now select that entire column and go to
Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values >
1st drop down menu > Duplicate
2nd drop down menu > Light Red Fill with... (any choice is OK)
In my Excel, the two cells with ">3d" are highlighted as expected but each of the following maneuvers and its result seems very odd to me:
1) If I delete either of the highlighted entries, the remaining cell is still highlighted -- even though it no longer has a duplicate. (Thus the test has produced a false positive.)
2) If both ">3d" entries are present and I delete either "a" or the "b" the shading goes away, (a false negative).
3) If I remove any of the three characters in ">3d" (i.e. ">" or "3" or "d"), the highlighting goes away for that entry.
4) I can replace the ">" with "<" and get the same result.
5) I can replace "3" with any number and/or "d" with any text and get the same result.
6) If I go back to: Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values > 1st drop down menu > and select "Unique" in place of "Duplicate," the unexpected or odd behavior persists.
7) If I go to Data tab > Remove Duplicates, it eliminates one of the duplicates as it should.
Again, I'm curious to know if Excel does this for all users and if so, why.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>Perhaps there is no reason...
Yikes!! I thought there is a reason for everything and everything happens for a reason"
JK...
Well, thanks for the responses. Do you know the best way to communicate something like this to Microsoft?
Yikes!! I thought there is a reason for everything and everything happens for a reason"
JK...
Well, thanks for the responses. Do you know the best way to communicate something like this to Microsoft?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Steve,
The link below talks about reporting a bug - in Conditional Formatting, as it happens.
http://answers.microsoft.c om/en-us/o ffice/foru m/office_2 007-excel/ how-do-you -report-a- product-bu g-to-micro soft/0ef12 a13-78eb-4 0c1-b946-f 963cbe26b6 e
Regards,
Brian.
The link below talks about reporting a bug - in Conditional Formatting, as it happens.
http://answers.microsoft.c
Regards,
Brian.
I can file it as a bug on Connect for you.
ASKER
redmondb:
>>reporting bugs involves a "deposit" which is repaid if MS confirm it.
You have got to be kidding me! You discover a bug in their software and before you are allowed to do them a favor by telling them about it, they require that you pay a deposit? And then, it is only refunded if the bug turns out to be real? Again, "You have got to be kidding me!" I would think they would give you a trip to Seattle to tour Bill's house or at least a small trinket to hang from your review mirror!
>>reporting bugs involves a "deposit" which is repaid if MS confirm it.
You have got to be kidding me! You discover a bug in their software and before you are allowed to do them a favor by telling them about it, they require that you pay a deposit? And then, it is only refunded if the bug turns out to be real? Again, "You have got to be kidding me!" I would think they would give you a trip to Seattle to tour Bill's house or at least a small trinket to hang from your review mirror!
ASKER
rorya:
I can file it as a bug on Connect for you.
I am not familiar with Connect but I assume it is some type of MS bug-reporting system. If so, then by all means, please report it.
Thanks
I can file it as a bug on Connect for you.
I am not familiar with Connect but I assume it is some type of MS bug-reporting system. If so, then by all means, please report it.
Thanks
ASKER
Multiple good answers.
Thanks, Steve.
(Small clarification - as rorya is a Microsoft MVP his offer should be considered a great answer!)
Regards,
Brian.
(Small clarification - as rorya is a Microsoft MVP his offer should be considered a great answer!)
Regards,
Brian.
Perhaps there is no reason...