Compare Two or More Lists in Excel with Conditional Formatting


I’ve recently posted two easy ways to compare two lists in Excel. One uses ISNA and MATCH and one IF and COUNTIF. I like both of these methods because they allow you to:

  1. Learn how to use and modify four powerful Excel functions
  2. Quickly apply AutoFilter to view just the missing values
  3. Easily manage and compare massive lists of data

For shorter lists, however, there is a much easier, non-formula way to compare two lists in Excel 2007 and later.

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Highlight both lists.

    Highlight the entire range of data.
    Highlight the entire range of data.
  3. Navigate to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values…
  4. In the Duplicate Values dialog box, select “Duplicate” from the drop-down list to highlight values (in our example, names) found on both lists and click OK.

    Apply conditional formatting to highlight duplicate values.
    Apply conditional formatting to highlight duplicate values.

It doesn’t get much easier than that. If you’d rather Excel highlight the values missing from either list, select “Unique” from the drop-down list on the Duplicate Values dialog box (step 4 above).

Apply conditional formatting to highlight unique values.
Apply conditional formatting to highlight unique values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with Conditional Formatting


One response to “Compare Two or More Lists in Excel with Conditional Formatting”

  1. Hello sir, I’m Trying to enter Data in Sheet in form of numbers
    for example:-

    Roll-No:- 801
    Maximum Marks
    20
    20
    20
    20
    20
    20
    20
    Sig. of Class Incharge

    Roll-No:- 802
    Maximum Marks
    20
    20
    20
    20
    20
    20
    20
    Sig. of Class Incharge

    but the problem is when i try to drag or paste the same cells in another line…….Rollno has changed 814 instead of 802. My all cells are refrence cells of sheet1.

    Now which formula is best during drag or paste the rows.If i paste the row after 14 Rows ..roll no show 815 instead of 802.
    please guide me what should i do?
    if u want i can send you the copy of Sheet.
    Thanks & Regard
    Gandhi

Leave a Reply to Prince Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.