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:

compare lists

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:

duplicate cell rules excel


  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.

  2. very helpful.

  3. Love your tutorials,
    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.

Comments are closed.