Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

remove Conditional formatting for specific duplicates,

Copper Contributor

Hi all, 


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.  So is there any rule that I can say that excel ignore N/A (or any other reference) as duplicates.






4 Replies

Hi @Bilosta 


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:




Cond. Format rule that applies to =$E$2:$E$14 for the above example:

=AND($E2 <> "n/a", COUNTIF($E$2:$E$14,$E2) > 1)

If not already the case I would recommend you to format your data as Table

Corresponding sample is available here

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

Infopulsepro @To remove conditional formatting for specific duplicates in Excel, you can follow these steps:

  1. Select the range of cells that contains the duplicates with conditional formatting.

  2. Go to the "Home" tab on the Excel ribbon.

  3. In the "Styles" group, click on "Conditional Formatting."

  4. Choose "Manage Rules" from the dropdown menu.

  5. In the "Conditional Formatting Rules Manager" dialog box, you will see a list of all the applied conditional formatting rules for the selected range.

  6. Select the rule that you want to remove for specific duplicates.

  7. Click on the "Edit Rule" button.

  8. In the "Edit Formatting Rule" dialog box, make sure the rule criteria are set to identify the duplicates correctly.