3

How To Compare Columns in Google Sheets

Posted by Jamie on December 28, 2018

If you work with cloud-based documents, then you already know that Google Sheets is a powerful and easy-to-learn cloud-based spreadsheet that competes with Microsoft Excel in the spreadsheet market. Among the advantages of Sheets are that it is quick to use and of course, free. It’s true that for power users, Excel undoubtedly has more features and greater flexibility, but you would be surprised at how many things you can actually do just as easily in Sheets. People who aren’t spreadsheet fanatics will probably find that Sheets is a somewhat friendlier tool to use. Regardless of one’s skill level, however, one of the most basic tasks in using spreadsheets is comparing two columns of data. In this article I’ll discuss how to do this in Google Sheets, and as a free bonus I’ll also describe one approach to doing it with Microsoft Excel.

How to compare two columns in Google Sheets2

Compare two columns in Google Sheets

One simple approach to comparing columns in Sheets is to use a formula. Let’s say that we have two columns of data, column A and column B. If we want to compare the columns and make a note of any differences, we can use a formula. The IF formula is a powerful tool in Sheets (as well as Excel). In the IF statement, there are three arguments. The first argument is the test to be performed, the second argument is the result to return if the test is not true, and the third argument is the result to return if the test is true. This is fairly easy to understand, but hard to read in the formula, so let’s step through it.

  1. Open your Sheet on the page that you want to compare.
  2. With data in columns A and B, highlight cell C1.
  3. Paste ‘=if(A1=B1,“”,“Mismatch”)’ into cell C1. So if A1 and B1 are the same, the formula returns an empty string, and if they are not the same, it returns “Mismatch”.
  4. Left-click on the bottom right corner of cell C1 and drag downwards. This copies the formula in C1 into all the cells of column C.

Now for each row in which A and B are not identical, column C will contain the word “Mismatch”.

Another approach is to use a comparison tool in one of the add-on packs for Google Sheets. One tool is known as ‘Power Tools’. It is one of many such tools that greatly extend the basic functionality and take a lot of the work out of creating spreadsheets. Power Tools has many powerful functions, but we’ll just take a look at its method of column comparing here.

  1. Once Power Tools is added to your Google Sheets, go to Add-Ons -> Power Tools -> Start.
  2. Click the ‘Data’ menu option then select ‘Compare two sheets’
  3. Enter the ranges of the columns you want to compare. Note that you can compare multiple columns at a time, and even compare across different sheets!
  4. Select whether you want to find unique values, or duplicate values.
  5. Select how you want Power Tools to indicate the results of the comparison. You can choose to have it color in the duplicate or unique cells, to move or copy data to new columns, and other choices.

Quick way to compare differences between text or spreadsheets

If you don’t want the hassle of writing formulas or using an add-on and just want to quickly compare values or text between two documents, there is a free online tool that does the heavy lifting for you. It is called Diffchecker and seems to work well. It is even recommended for use on the Google Docs forum.

  1. Navigate to Diffchecker.
  2. Paste one set of text or values into the left pane and the other column or text into the right.
  3. Select Find Difference!
  4. The site will compare the two panes and highlight any differences.

Diffchecker is useful if you are trying to differentiate between columns and only need the results.

So how about if you use Excel, can you compare columns using that tool? Well of course you can!

Compare two columns in Microsoft Excel

I flip between Google Sheets and Microsoft Excel depending on what I am trying to do. While Sheets is very good, it doesn’t have quite as many features as Excel and so falls short in some key areas.

Method 1 to compare columns for duplicates in Excel:

  1. Highlight the two columns you want to check.
  2. Select Conditional Formatting from the Home ribbon.
  3. Select Highlight Cell Rules and Duplicate Values.
  4. Select a format to display and select OK.

Method 2 to compare columns for differences in Excel:

  1. Highlight cell 1 in column C.
  2. Paste ‘=IF(COUNTIF($A:$A, $B2)=0, “No match in A”, “”)’ into the formula bar.
  3. You should see ‘No match in A’ in column C wherever the two columns differ.

You should see those cells with differences as there should be a label in the corresponding row telling you ‘No match in A’. You can of course modify this to say anything you like. You can also change the column letters, or the order in which you compare the two accordingly.

These methods will let you compare columns for duplicates and unique values in both Google Sheets and Excel.

Got any tips for comparison tools for either of these software tools? Share with us below.

3 thoughts on “How To Compare Columns in Google Sheets”

Gaurav Swaroop says:
Simpler way –
=ARRAYFORMULA(COUNTIF(IF(C12:C336<D12:D336,"Yes","No"),"Yes"))
Reply
Q says:
Your formula makes no sense, honestly. First of all, you can’t tell if what you wrote on step 4 is part of the formula from step 3.
Also, if I look in step 3 I see some quotes left unclosed while we also have some double quotes that haven’t been “opened” before.
Reply
m says:
Formula raises error
Reply
a says:
I got an error when copy/pasting because the quotation marks were special characters. Retype them in. Also make sure you remove the linebreak and “4.” from the middle of the formula, I dont know why the author of this article formatted it like this.. Should just be:
=query(index(if({1,2,0},A:B,isna(match(A:A,B:B,0)))),”select Col2 where Col3=TRUE”,1)
Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.