Issue with conditional formatting in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1515836%22%20slang%3D%22en-US%22%3EIssue%20with%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515836%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20conditional%20formatting%20in%20excel%20to%20highlight%20cells%20that%20are%20duplicate%2C%20it%20works%20and%20creates%20a%20rule%2C%20but%20after%20a%20while%20it%20stops%20working%20while%20the%20rule%20is%20still%20active.%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%E2%80%99t%20figure%20out%20why%20this%20happens.%20I%20searched%20online%20and%20cold%20not%20get%20an%20answer.%26nbsp%3BAnyone%20has%20a%20clue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%3A%20Running%20Office%20365%20on%20a%20MacBook.%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1515836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515848%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725384%22%20target%3D%22_blank%22%3E%40zenpanda%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20be%20a%20bit%20more%20precise%20and%20specific%20in%20your%20descriptions%3F%20I'll%20give%20you%20a%20few%20questions%20to%20start%20you%20off%3B%20there%20may%20be%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EWhat's%20%22a%20while%22%20as%20in%20%22after%20a%20while%20it%20stops%20working%22%3F%3C%2FLI%3E%3CLI%3EWhat's%20the%20nature%20of%20the%20data%20in%20the%20cells%20that%20may%20or%20may%20not%20be%20duplicate%3F%20Is%20it%20text%3F%20Numeric%3F%3C%2FLI%3E%3CLI%3EHow%20does%20that%20data%20get%20into%20the%20cells%20in%20the%20first%20place%3F%3C%2FLI%3E%3CLI%3EWhat's%20the%20rule%3F%20How%20does%20it%20appear%20in%20the%20conditional%20formatting%20dialog%20box%3F%3C%2FLI%3E%3CLI%3EIs%20this%20a%20matter%20of%20a%20lot%20of%20cells%20in%20a%20long%20column%2C%20some%20of%20which%20are%20(or%20appear%20to%20be)%20duplicates%3F%3C%2FLI%3E%3CLI%3EDoes%20the%20%22failure%20to%20work%22%20happen%20in%20all%20kinds%20of%20different%20conditions%20(i.e.%2C%20are%20there%20some%20duplicates%20that%20still%20are%20highlighted%2C%20but%20not%20all)%3F%3C%2FLI%3E%3C%2FUL%3E%3CP%3EEach%20of%20these%20questions%20gets%20at%20things%20that%20are%20possible%20causes.%20That's%20why%20it's%20important%20to%20get%20a%20more%20complete%20description%2C%20just%20as%20a%20doctor%20would%20want%20a%20more%20complete%20descripton%20of%20the%20pain%20you%20are%20experiencing%2C%20when%20it%20happens%2C%20where%2C%20the%20intensity%2C%20etc.%2C%20before%20attempting%20a%20diagnosis.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515859%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725384%22%20target%3D%22_blank%22%3E%40zenpanda%3C%2FA%3E%26nbsp%3B%20I'm%20kinda%20surprise%20that%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%20didn't%20ask%20his%20favorite%20question%20...%20can%20you%20please%20attach%20an%20example%20workbook%20for%20us%20to%20see.%26nbsp%3B%20That%20said%2C%20my%20guess%20(because%20it%20happens%20to%20me%20all%20the%20time)%20is%20that%20you%20insert%20and%20delete%20rows%2Fcolumns%20and%20that%20often%20causes%20issues%20with%20conditional%20formatting%20(the%20ranges%20get%20broken%20up%20or%20the%20rule%20gets%20split%20into%20multiple%20copies).%26nbsp%3B%20But%20as%20mathetes%20said%2C%20we%20could%20really%20use%20more%20info%20to%20help%20you%20track%20it%20down.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1515925%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1515925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BYeah%20you're%20right%2C%20I%20will%20submit%20the%20info%20and%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bsaid%20will%20upload%20an%20example%20workbook.%20I%20just%20created%20a%20new%20test%20workbook%20and%20wonder%20if%20I%20figured%20it%20out%20by%20accident.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20applied%20the%20rule%20before%20adding%20anything%20into%20the%20column%20and%20so%20far%20it%20works%20as%20it%20should.%20will%20use%20it%20and%20open%20and%20close%20a%20few%20times%20throughout%20today%20day%20and%20see%20if%20it%20sticks%20this%20time.%20If%20not%2C%20I'll%20upload%20the%20book%20and%20will%20clarify%20all%20the%20points%20you%20mentioned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFingers%20crossed%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20and%20thanks%20for%20the%20replies!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516271%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725384%22%20target%3D%22_blank%22%3E%40zenpanda%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EI%20applied%20the%20rule%20before%20adding%20anything%20into%20the%20column%20and%20so%20far%20it%20works%20as%20it%20should.%20will%20use%20it%20and%20open%20and%20close%20a%20few%20times%20throughout%20today%20day%20and%20see%20if%20it%20sticks%20this%20time.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20going%20to%20suggest%20that%20you%20reverse%20your%20thinking%20a%20bit%20here.%20You%20have%20been%20describing%20this%20from%20the%20beginning%20as%20a%20failure%20of%20Excel%20to%20consistently%20apply%20a%20conditional%20formatting%20rule%20for%20duplicate%20cells.%20Your%20most%20recent%20post%20(excerpted%20above)%20still%20talks%20of%20whether%20%22%3CFONT%20color%3D%22%23FF0000%22%3E%3CU%3E%3CSTRONG%3Eit%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FFONT%3E%20sticks%20this%20time.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reality%20is%20that%20likelihood%20of%20Excel%20failing%20in%20this%20case%20is%20far%2C%20FAR%20lower%2C%20than%20is%20one%20of%20the%20other%20causes%20that%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%20and%20I%20have%20alluded%20to.%3C%2FP%3E%3CUL%3E%3CLI%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20suggested%20the%20possibility%20of%20your%20inadvertently%20breaking%20up%20the%20range%20to%20which%20the%20rule%20applied%2C%20doing%20so%20by%20inserting%20a%20row%2C%20or%20some%20other%20procedure.%3C%2FLI%3E%3CLI%3Eall%20of%20my%20questions%20(OK%2C%20most%20of%20them)%20were%20designed%20to%20uncover%20the%20possibility%20that%20what%20you%20thought%20was%20a%20duplicate%20or%20exact%20match%20wasn't%20in%20fact.%20Computers%20are%20very%20precise%3B%20we%20humans%20are%20less%20precise.%26nbsp%3B%3C%2FLI%3E%3CLI%3EFor%20example%2C%20if%20you%20had%20text%20entries%20you%20might%20see%20%3CU%3EThis%3C%2FU%3E%20as%20the%20same%20as%20%3CU%3EThis%26nbsp%3B%3C%2FU%3E%20but%20the%20computer%20would%20not%20see%20those%20two%20entries%20as%20duplicates.%20The%20latter%20has%20a%20trailing%20space%2C%20easily%20overlooked%20by%20our%20eyes%20and%20minds%2C%20which%20are%20focusing%20on%20the%20word%20itself.%20So%20if%20the%20entries%20into%20the%20cells%20are%20being%20done%20by%20typing%2C%20i.e.%2C%20by%20humans%20who%20easily%20can%20insert%20a%20space%20without%20even%20realizing%20it%2C%20that%20could%20be%20what's%20happening.%3C%2FLI%3E%3CLI%3ESimilarly%2C%20depending%20on%20how%20the%20cells%20are%20formatted%20for%20numeric%20displays%2C%20or%20dates%2C%20it's%20entirely%20possible%20for%20entries%20to%20look%20like%20duplicates%20to%20the%20human%20eye%2Fmind%2C%20but%20in%20fact%20not%20be%20duplicate%20in%20the%20computer's%20way%20of%20seeing.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20when%20this%20happens%2C%20%3CEM%3E%3CSTRONG%3Edon't%20think%3C%2FSTRONG%3E%3C%2FEM%3E%20%22why%20has%20Excel%20failed%20to%20see%20duplicates%3F%22%20%3CEM%3E%3CSTRONG%3EInstead%2C%20s%3C%2FSTRONG%3E%3C%2FEM%3E%3CEM%3E%3CSTRONG%3Etart%20your%20investigation%20with%20the%20mindset%3C%2FSTRONG%3E%26nbsp%3B%3C%2FEM%3E%20%22Excel%20is%20telling%20me%20that%20these%20cells%20are%20not%20duplicates%2C%20even%20though%20they%20look%20to%20me%20as%20if%20they%20are%3B%20so%20let%20me%20see%20if%20I%20can%20figure%20out%20how%20they're%20not%20duplicates%20after%20all.%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

 

When I use conditional formatting in excel to highlight cells that are duplicate, it works and creates a rule, but after a while it stops working while the rule is still active. 

 

I can’t figure out why this happens. I searched online and cold not get an answer. Anyone has a clue?

 

BTW: Running Office 365 on a MacBook. 

Thanks

4 Replies

@Deleted 

 

Could you be a bit more precise and specific in your descriptions? I'll give you a few questions to start you off; there may be more.

 

  • What's "a while" as in "after a while it stops working"?
  • What's the nature of the data in the cells that may or may not be duplicate? Is it text? Numeric?
  • How does that data get into the cells in the first place?
  • What's the rule? How does it appear in the conditional formatting dialog box?
  • Is this a matter of a lot of cells in a long column, some of which are (or appear to be) duplicates?
  • Does the "failure to work" happen in all kinds of different conditions (i.e., are there some duplicates that still are highlighted, but not all)?

Each of these questions gets at things that are possible causes. That's why it's important to get a more complete description, just as a doctor would want a more complete descripton of the pain you are experiencing, when it happens, where, the intensity, etc., before attempting a diagnosis.

@Deleted  I'm kinda surprise that @mathetes didn't ask his favorite question ... can you please attach an example workbook for us to see.  That said, my guess (because it happens to me all the time) is that you insert and delete rows/columns and that often causes issues with conditional formatting (the ranges get broken up or the rule gets split into multiple copies).  But as mathetes said, we could really use more info to help you track it down. 

@mathetes Yeah you're right, I will submit the info and as @mtarler said will upload an example workbook. I just created a new test workbook and wonder if I figured it out by accident. 

 

I applied the rule before adding anything into the column and so far it works as it should. will use it and open and close a few times throughout today day and see if it sticks this time. If not, I'll upload the book and will clarify all the points you mentioned.

 

Fingers crossed and thanks for the replies!

 

@Deleted 

 

You wrote: I applied the rule before adding anything into the column and so far it works as it should. will use it and open and close a few times throughout today day and see if it sticks this time.

 

I'm going to suggest that you reverse your thinking a bit here. You have been describing this from the beginning as a failure of Excel to consistently apply a conditional formatting rule for duplicate cells. Your most recent post (excerpted above) still talks of whether "it sticks this time."

 

The reality is that likelihood of Excel failing in this case is far, FAR lower, than is one of the other causes that  @mtarler and I have alluded to.

  •  @mtarler  suggested the possibility of your inadvertently breaking up the range to which the rule applied, doing so by inserting a row, or some other procedure.
  • all of my questions (OK, most of them) were designed to uncover the possibility that what you thought was a duplicate or exact match wasn't in fact. Computers are very precise; we humans are less precise. 
  • For example, if you had text entries you might see This as the same as This  but the computer would not see those two entries as duplicates. The latter has a trailing space, easily overlooked by our eyes and minds, which are focusing on the word itself. So if the entries into the cells are being done by typing, i.e., by humans who easily can insert a space without even realizing it, that could be what's happening.
  • Similarly, depending on how the cells are formatted for numeric displays, or dates, it's entirely possible for entries to look like duplicates to the human eye/mind, but in fact not be duplicate in the computer's way of seeing.

 

So when this happens, don't think "why has Excel failed to see duplicates?" Instead, start your investigation with the mindset  "Excel is telling me that these cells are not duplicates, even though they look to me as if they are; so let me see if I can figure out how they're not duplicates after all."