Solved

Conditional Formatting (highlighting duplicates) oddity in Excel

Posted on 2011-03-17
10
709 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Steve_Brady
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 35158431
It would appear to be a bug. It occurs in my installation of 2007 and 2010.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35158459
Again, I'm curious to know if Excel does this for all users
Yes, it does it for me (only tried 1-5)

why
Perhaps there is no reason...
0
 

Author Comment

by:Steve_Brady
ID: 35159311
>>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?
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 200 total points
ID: 35159402
Steve,

I don't have it to hand, but IIRC the mechanism for reporting bugs involves a "deposit" which is repaid if MS confirm it. Want me to dig out the link?

As to why, I assume that part of this is that the ">" is being interpreted by Excel. For example, just using ">B", "B", "B" correctly highlights the two B's, but changing the ">" to ">=" (or changing the two B's to C's) highlights the first cell as well.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35159482
Steve,

The link below talks about reporting a bug - in Conditional Formatting, as it happens.

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-you-report-a-product-bug-to-microsoft/0ef12a13-78eb-40c1-b946-f963cbe26b6e

Regards,
Brian.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35159691
I can file it as a bug on Connect for you.
0
 

Author Comment

by:Steve_Brady
ID: 35161360
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!
0
 

Author Comment

by:Steve_Brady
ID: 35161382
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
0
 

Author Closing Comment

by:Steve_Brady
ID: 35161396
Multiple good answers.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35161527
Thanks, Steve.

(Small clarification - as rorya is a Microsoft MVP his offer should be considered a great answer!)

Regards,
Brian.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
Hlookup formula help 14 19
Help with Adding text from a form to a worksheet 5 37
Highlighting cells in Excel 9 16
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now