remove Conditional formatting for specific duplicates,

Question

Hi all,&nbsp;&nbsp;I have situation where I have conditional formatting in column E for duplicates. in column E I need to have unique references but sometimes I do not have any reference for some row, and I need to write N/A (non-applicable). Now Excel highlight me that cells as duplicates, but I do not want that cell to be highlighted.&nbsp; So is there any rule that I can say that excel ignore N/A (or any other reference) as duplicates.&nbsp;&nbsp;&nbsp;thanks.&nbsp;

lorenzo · Answer

Hi&nbsp;Bilosta&nbsp;&nbsp;You cannot achieve what you expect with the built-in Cond. Format Highlight Duplicate Values rule. Instead you'll have to setup a custom rule using a formula:&nbsp;&nbsp;Cond. Format rule that applies to&nbsp;=$E$2:$E$14 for the above example:=AND($E2 &lt;&gt; "n/a", COUNTIF($E$2:$E$14,$E2) &gt; 1)If not already the case I would recommend you to format your data as TableCorresponding sample is available here

bilosta · Answer

Thanks 😄

lorenzo · Answer

You're welcome. If this solves the issue there's a Mark as response link (at the bottom of the post) that helps people who Search - Thanks

Forum Discussion

remove Conditional formatting for specific duplicates,

3 Replies

Resources