3

How To Compare Columns in Google Sheets

Posted by Robert Hayes on April 30, 2019

Google Sheets is Google’s powerful and easy-to-learn cloud-based spreadsheet. While Sheets competes with Microsoft Excel in the spreadsheet market, it doesn’t have the same breadth or depth of features. However, it is a very useful program and probably capable of doing 90% of the tasks that even a power user would need, while being able to do 100% of the tasks that ordinary users have. One of those routine tasks is the comparison of information in different columns. Sheets is more than capable of performing this kind of analysis. In this article I’ll discuss how you can compare data between columns in Sheets, and 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”.

Comparing multi-column data

Comparing data between two columns is fine and useful…but what if you have multiple columns of data and need to do comparisons? Well, Sheets can handle that as well, using a function called ARRAYFORMULA. This is a fairly advanced formula and I’m not going to get deep into the weeds on how it works, but it will allow us to do some multi-column data comparisons.

Let’s say we have two sets of data. Each data set has an index value – maybe a part number or a serial number. There are also a couple of columns of data associated with each index value – product colors, maybe, or the quantity on hand. Here’s what one of those data sets might look like.

So we have Jane’s data. But then Bob sends in his figures for the same set of information, and we suspect that there might be some differences. (In this example, you could easily visually identify the differences, but assume a spreadsheet with thousands of entries.) Here’s Jane and Bob’s figures side by side.

If we want to check to see whether the price per unit figures reported by Jane and Bob are the same, we can use ARRAYFORMULA to do it. We want to report any differences and print them out starting in cell I3, so in I3 we type this formula:

=ArrayFormula(SORT(if(countifs(E3:E&G3:G,A3:A&C3:C)=0,A3:D,)))

This results in a multi-column comparison that looks like this:

Now we can see that SKU A10305 has a difference and we can figure out who has the right information and who’s got an error.

Using Power Tools to compare columns

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.

Want more spreadsheet help? We’ve got your back.

Put some visual pop in your spreadsheets by adding images to your Google Sheets.

Need to get it all on one page? Here’s how to squeeze that giant spreadsheet down into one printable page.

Use CAGR? We’ve got a tutorial on adding CAGR formulas to Google Sheets.

Duplicates are an annoyance in spreadsheets – here’s how to get rid of duplicates in Sheets.

Using spreadsheets to do your billing? Here’s a way to automatically generate invoice numbers in Excel.

Hate fiddling with cell formatting? Learn how to automatically adjust row heights in Excel.

Need to tighten up your sheet layout? We’ll show you how to eliminate empty rows and columns in your Google Sheets.

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.