Video: How to compare two columns in Excel

Learn three sure ways to compare two columns in Excel: use a formula to mark all matches, Conditional Formatting to highlight them, or Duplicate Remover add-in for more options.

There are quite a few ways you can find matches in your tables, each well suited to a particular task. We suggest trying one of the reliable methods described in this video any time you need to compare columns, whether they are in the same or in different sheets.

How to compare two columns in Excel: video transcript

Microsoft Excel offers a variety of options for comparing two columns of data. In this video we will look at the easiest ways: using formulas, conditional formatting, or a special add-in.

Here I have a table with the requested books and a list of ordered books. I'd like to see those of the requested books that were already ordered.

Excel formulas for comparing two columns

First let's consider a native way: compare two columns in Excel using formulas. There are several functions that can help us.

The combination of IF and COUNTIF functions is one of the most convenient options. Let's start with COUNTIF and see what it does:
Enter =COUNTIF, open parenthesis and point to the range where it will search. Enter a comma, and add the first value to check; in our case it is the first requested book:

=COUNTIF('Ordered books'!D:D,C2)

Compare the columns with COUNTIF

You can read the formula easily: search for this requested book in the list of ordered books, and give me a count of the found matches. Once we enter the formula, we need to apply it to every cell in our column C, so we can point to the cell with the formula and double-click on the black plus sign. Once you fill this formula down, you will get each cell in column C checked in the range you specified.

Now we can use this as a condition to mark the ordered books: we add the IF function, open parenthesis, use our COUNTIF formula, and add ">0" condition to it. Now we say that if the number of matches is over zero, we want to see "Ordered" in the cell. Otherwise we can leave the cell blank, so we enter quotes without anything inside and close parenthesis:

=IF(COUNTIF('Ordered books'!D:D,C2)>0,"Ordered","")

Compare the columns using IF and COUNTIF together

Fill the formula down, and you can quickly see the results of comparing two columns for duplicates.

Use Conditional Formatting in Excel to compare two columns

If you would like to visualize the differences or the similarities between two lists, conditional formatting is a handy way to compare 2 columns in Excel. You will need to select the column where you want to highlight the records, then go to Conditional Formatting, click New rule, and pick the option to "Use a formula to determine which cells to format". You can use the same formula in your rule:

=COUNTIF('Ordered books'!$D:$D,C1)>0

Click Format to choose the color, and click Ok.
Color the differences between your lists

Well, there is also a pre-supplied way to find duplicates using conditional formatting that works when you have both lists in the same sheet. You just need to hold the CTRL button on your keyboard, select the columns you want to compare, and then run Conditional Formatting - Highlight Cells Rules - Duplicate Values... Select a format and click Ok to see the results.

Try a special add-in for Excel to compare two lists

If your comparison involves more than one column, you won't find a better and more flexible tool than Duplicate Remover add-in. Once you install the add-in, you will find the Compare Two Tables icon under Ablebits Data tab in Excel.
The easiest way to compare your tables

  1. The add-in will automatically pick your table; you can correct its address right in this field or use the icon for selecting range. As you can't cancel changes made by add-ins in Excel, we always recommend creating a backup copy of the sheet.
  2. Click next and pick the second table for comparison: in our case it is the list of ordered books.
  3. Follow to step 3 where you can choose the type of values you want to find: duplicates or uniques and click Next.
  4. On step 4 you can choose the columns to compare. So if I want to make sure all these fields match and the records have the same author and the same book title, I will choose all these columns as matching. This step makes this add-in a life-saver when you want to find duplicates by more than one column.
    Decide how many columns you'd like to compare
  5. Finally, you can choose what to do with the found rows: you can delete, copy, or move them, add a column that will mark their status, or highlight them with any color. Let's delete the duplicates now.
    Choose how to deal with the duplicates

Click Finish to compare two Excel sheets and see a list of books that are waiting to be ordered.

You can get Duplicate remover with a discount if you use the coupon code Preview.

I hope that when you need to compare 2 columns in Excel, the solutions we looked at will help you. And if you have any questions, please post them as comments. We'll do our best to assist you.

You may also be interested in

10 comments to "Video: How to compare two columns in Excel"

  1. Gerwyn Taylor says:

    A very basic question but excel is fighting back - I want A3 to return a Yes if A1 and A2 both reading Yes or if either reads No then A3 reads No.

  2. puneeth says:

    i want to know how to convert date into words(english) in excel 2003 or 2007
    for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.

  3. Ahmed says:

    If A1 = num1 , B1= num2
    C1= (A1+B1) total (num1+ num2) Change Amount in A1 from num 1 to num3 need to show us new total in
    C1 (increasing )automatically

  4. anas says:

    Hi.. I need your support here, plz.. I have two columns and once I select 'Ahmed' automatically show number '10' on the other side. So, Which function I have to use. Thanks a lot.

  5. sizakele khuzwayo says:

    Can you please help with a formula.

    I have created a website from an existing website and i want to compare their URLs.

    is there a formula to do this in excel.

    • Hello Sizakele,

      If you have two columns of URLs in Excel, you just need to remove the part of the link that differs, i.e. the old and the new name of the site.
      You can use the standard Find and Replace dialog box or the Substitute function if you prefer. Then you'll be able to compare the links using the methods described in the video.

      If your task is different, please describe it in more detail. If possible, please also send a sample worksheet to

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)