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 https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370#:~:text=1%20Select%20any%20cell%20in%20the%20table%20from,displayed%20in%20the%20default%20table%20format.%20See%20MoreCorresponding sample is available https://1drv.ms/x/s!AsuqEFbTJNgcgaZujOun6v-L-twScw?e=e25BEz

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