Home>Blogs>Excel Tips and Tricks>Highlight Unmatched Cells using Conditional Formatting in Excel
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))”


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


  • “=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.


Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!