Highlight Unmatched Cells
Excel Tips and Tricks

Highlight Unmatched Cells using Conditional Formatting in Excel

In this article you will learn how to highlight the Unmatched Cells values from another range using Conditional formatting.

For Example, we have Sales and Product Master worksheets. In the Sales worksheet we need to highlight the Products which are not available in Product Master.

Sales Data and Product Master Worksheets
Sales Data and Product Master Worksheets

Below are the steps to put the Conditional Formatting to highlight Product Name in Sales data-

  • Select the Range (“A2:A19”)
  • Go to Home >> Conditional Formatting >> New Rule or Press Alt+D+O+N
  • Select “Use a formula to determine which cells to format”
  • Put any one of below given formula in the box.
  • =ISERROR(VLOOKUP(A2,’Product Master’!B:B,1,0))”

Or

  • =ISERROR(MATCH(A2,’Product Master’!B:B,0))

Or

  • “=COUNTIF(‘Product Master’!$B$2:$B$15,A2)=0
  • Click on Format button and select some background color.
  • Click on OK.

 

Conditional Formatting Window
Conditional Formatting Window

 

Now Product Name which are not available in Product Master will be highlighted in Sales worksheet.

Highlighted Unmatched Cells
Highlighted Unmatched Cells

 

Click here to download the practice workbook.

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com