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

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:

Teaching professional business subjects to the students of FIA, ACCA, CIMA, CA etc. He also found ACCA LIVE which is Pakistan's first portal to provide online classes and distance learning solutions to FIA/ACCA students. At PakAccountants.com he is busy making study material for different qualifications. Beside writing articles he answers questions asked using ASK TUTOR!

1. Sandhya Nagaraj

Thank you a Lot, for teaching us a daily techniques on Excel, which could increase our speed in working without much wasting of time.

learn one new trick every few days, growing from barely adequate to empowered user over time.

thank You keep it up!

another thought is to allow the expansion of main trick to more sub-tricks.
This will bring more views and more learning especially for non power users.

as an example, at the end of this trick, show how to make a new list by choosing the color sort and copy to new row or something like that for unique list.
Power users would just skip if known, other would continue to learn beyond main trick learn.

4. Oloyede O. Lawal

This is fantastic and very simple.