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)

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","")

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.

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.

  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.
  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.

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.

See also

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

  1. 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 support@ablebits.com.

  2. 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.

  3. Ahmed says:

    Help
    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
    Thanks

  4. 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.

  5. 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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard