How to compare columns in Google Sheets

Google Sheets is Google’s powerful, easy-to-learn, cloud-based spreadsheet app. Sheets competes with Microsoft Excel in the spreadsheet market, it doesn’t have the same breadth and depth of features.

Google Sheets, Google Docs and Google Slides are part of the web-based office suite offered by Google for free. These applications integrate with Google Drive, Google’s file storage and synchronization service.

One of those routine spreadsheet tasks is comparing information in different columns. Sheets is more than capable of making this kind of comparison.

In this how-to article, I’ll show you how you can compare data between columns in Google Sheets and also describe an approach to do the same with Microsoft Excel. If you’re an Excel user considering the switch and are worried about having comparable features, this article should ease your worries. You can do it with Sheets!

Google Sheets Comparison Columns

Compare two columns in Google Sheets

A simple method to compare columns in Sheets is to use a formula. Let’s say we have two columns of data, column A and column B. If we want to compare the columns and note the 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 No TRUE, and the third argument is the result to be returned if the test is TRUE. This is pretty easy to understand, but hard to read into the formula, so let’s go over it.

1. Open your Sheet to the page you want to compare.
   
2. Using the data in columns A and B, highlight cell C1.
   
3. Paste =if(A1=B1,"","Mismatch") in cell C1. The logic is as follows: if A1 and B1 are equal (i.e. A1=B1), the formula returns an empty string, and if they are not equal (A1 is not No equal to B1), the formula returns “Do not match”.
   
4. Left click in the lower right corner of cell C1 and drag down. This copies the formula in C1 to all cells in column C.
   

Now, for each row where A and B are not identical, column C will contain the word “Do not match.” If you see a blank cell, then the formula did not return anything indicating that the columns match.

Comparison of data from multiple columns

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

In Sheets and other worksheets, you can use an array formula to calculate a column or row of subtotal values ​​by placing it in a row of cells, or you can calculate a single value in a single cell.

Let’s say we have two sets of data. Each data set has an index value; It can be a part number or a serial number. There are also a couple of columns of data associated with each index value: product colors, perhaps, or quantity available. This is what one of those data sets might look like.

So we have Jane’s data. But then Bob sends his figures for the same data set and we suspect there might be some differences between the two data sets. (In this example, he could easily identify the differences visually, but assume a spreadsheet with thousands of entries.) See the figures of Jane and Bob next to each other below.

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

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

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

Now we can see that SKU A10305 has a difference and we can determine who has the correct information and who has an error.

Using power tools to compare columns

Another approach is to use a comparison tool in one of the Google Sheets add-on packages. A tool is known as ‘Electric tools,a very useful collection of add-ons to extend the functionality of Google Sheets. It is one of many tools that greatly expand the basic functionality and eliminate much of the work of creating spreadsheets.

While they used to be free indefinitely, Power Tools now requires a subscription after the free trial period ends. I’d say Power Tools is worth the price for regular spreadsheet users: $29.95 a year or $89.95 for a lifetime subscription.

Power Tools has many powerful features, but here we will only look at its column comparison method.

1. Once Power Tools is added to Google Sheets, go to the Accessories Press menu
   
2. Select Electric tools
   
3. Then select Begin
   
4. Click the “Deduplicate and Compare” menu option and then select “Compare Two Sheets.”
   
5. Enter the ranges of the columns you want to compare. Note that you can compare multiple columns at once and even compare between different sheets.
   
6. Select whether you want to search for unique values ​​or duplicate values.
   
7. Select how you want Power Tools to display comparison results. You can choose to have duplicate or unique cells colored, move or copy data to new columns, and other options.
   

A quick way to compare differences between text or spreadsheets

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

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

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

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

Compare two columns in Microsoft Excel

I switch between Google Sheets and Microsoft Excel depending on what I’m trying to do. While Sheets is very good, it doesn’t have as many features as Excel and therefore 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 on 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,$B1)=0,"No match in A","") in 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 stating “No match in A”. Of course, you can modify this to say whatever you want. You can also change the column letters or the order in which you compare the two accordingly.