Compare two lists for duplicates or unique values in no time using Excel

 Last time we learnt a technique to compare two lists for similar values using conditional formatting. You can learn more about it at this page.

However the formula we employed had its limitations as it was checking for the values only in the same row. Data may be duplicated in a different row in another list. Though there are ways to find that too but require a little complex formula.

But there is one extremely easy way to accomplish it and that too using conditional formatting but applying a different method i.e. cell rules to highlight duplicate/unique values

Following is the result of applying the method we are about to learn today:

As you can see that though duplicate values were in different rows but still they are found and highlighted. That’s powerful! So lets see learn how to do it!

Step 1: Have two lists and select both of them using mouse or keyboard.

Step 2: Go to home tab > styles group > conditional formatting drop-down button > highlight cell rules > duplicate values.

Step 3: A dialogue box will appear from which you can make the selection to highlight the duplicate values or unique values in two lists.

Following animation will guide you through the whole procedure: