How to compare data in two Google sheets or columns

Example 1. Google Sheets – compare two cells

For this first example, you will need a helper column in order to enter the formula into the first row of the data to compare:

=A2=C2

If cells match, you'll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:

Example 2. Google Sheets – compare two lists for matches and differences

• A neater solution would be to use the IF function. You'll be able to set the exact status for identical and different cells:

  =IF(A2=C2,"Match","Differ")

  Tip. If your data is written in different cases and you'd like to consider such words as different, here's the formula for you:

  =IF(EXACT(A2,C2),"Match","Differ")

  Where EXACT considers the case and looks for the complete identicals.

• To identify only rows with duplicate cells, use this formula:

  =IF(A2=C2,"Match","")

• To mark only rows with unique records between cells in two columns, take this one:

  =IF(A2=C2,"","Differ")

Example 3. Compare two columns in Google Sheets

• There's a way to avoid copying the formula over each row. You can forge an array IF formula in the first cell of your helper column:

  =ArrayFormula(IF(A2:A=C2:C,"","Differ"))

  This IF pairs each cell of column A with the same row in column C. If records are different, the row will be identified accordingly. What is nice about this array formula is that it automatically marks each and every row at once:

• In case you'd rather name the rows with identical cells, fill the second argument of the formula instead of the third one:

  =ArrayFormula(IF(A2:A=C2:C,"Match",""))

Example 4. Compare two Google Sheets for differences

Oftentimes you need to compare two columns in Google Sheets that belong inside a huge table. Or they can be entirely different sheets like reports, price lists, working shifts per month, etc. Then, I believe, you can't afford to create a helper column or it can be quite difficult to manage.

If this sounds familiar, don't worry, you can still mark the differences on another sheet.

Here are two tables with products and their prices. I want to locate all cells with different contents between these tables:

Start with creating a new sheet and enter the next formula into A1:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

As a result, you will see only those cells that differ in contents. The formula will also pull records from both tables and separate them with a character you enter into the formula:

Compare columns or sheets add-on

This first one will compare two Google sheets and columns for duplicates or uniques in 3 steps. Make it mark the found records with a status column (that can be filtered, by the way) or color, copy or move them to another location, or even clear cells and delete entire rows with dupes whatsoever.

I used the add-on to find the rows from Sheet1 that are absent from Sheet2 based on Fruit and MSRP columns:

Then I saved my settings into one scenario. Now I can quickly run them without going through all steps again whenever records in my tables change. I just need to start that scenario from the Google Sheets menu:

For your better convenience, we've described all the tool's options on its help page and in this video:

Feel free to try it for yourself and notice how much time it saves you. :)

Compare sheets cell by cell

The other one will compare your Google Sheets for differences. Whether you have two or more tables, it will check them all cell by cell and create one thorough report with differences from all sheets grouped accordingly.

Here's an example of the same two tables. The add-on creates one report with not only different cells (marked with yellow) but also unique rows (marked with red and blue):

To look at the report and all its parts closely, feel free to read this tutorial or watch this demo video:

Or, even better, try both add-ons for yourself and notice how much time they save you. :)

Example 1

Imagine you have two lists of products (columns A and C in my case, but they can simply be on different sheets). You need to find those presented in the first list but not in the second one. This formula will do the trick:

=ISERROR(VLOOKUP(A2,$C:$C,1,0))

How does the formula work:

• VLOOKUP searches for the product from A2 in the second list. If it's there, the function returns the product name. Or else you will get an #N/A error meaning the value wasn't found in column C.
• ISERROR checks what VLOOKUP returns and shows you TRUE if it's the value and FALSE if it's the error.

Thus, cells with FALSE are what you're looking for. Copy the formula to other cells to check each product from the first list:

Example 2

Another smart way would be to count all appearances of the product from A2 in column C:

=IF(COUNTIF($C:$C, $A2)=0, "Not found", "")

If there's absolutely nothing to count, the IF function will mark cells with Not found. Other cells will remain empty:

Example 3

Where there's VLOOKUP, there's MATCH. You know that, right? ;) Here's the formula to match products rather than count:

=IF(ISERROR(MATCH($A2,$C:$C,0)),"Not found","")

Example 1

Your task may be a bit fancier: you may need to pull all missing information for the records common for both tables, for example, update prices. If so, you'll need to wrap MATCH in INDEX:

=INDEX($E:$E,MATCH($A2,$D:$D,0))

The formula compares fruits in column A with fruits in column D. For everything found, it pulls the prices from column E to column B.

Example 2

As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.

Yet, there are a few more instruments for the job. We described them all in our blog as well:

1. These will do for the basics: lookup, match and update records.
2. These will not just update cells but add related columns & non-matching rows.

Merge sheets using the add-on

If you're tired of formulas, you can use our Merge Sheets add-on to quickly match and merge two Google sheets. Alongside its basic purpose to pull the missing data, it can also update existing values and even add non-matching rows. You can see all changes in colour or in a status column that can be filtered.

The 2.0 version of Merge Sheets will merge not just 2 tables (one main with one lookup) but multiple sheets in a row (one main with several lookups). The data from the lookup sheets will be added to your main one by one: as you added them in the add-on. Lots of additional options will make your merge as comprehensive as you need.

Example 1. Compare two columns in Google Sheets and highlight differences (uniques)

To highlight unique values in each list, you must create two conditional formatting rules for each column.

Color column A: =COUNTIF($C$2:$C$9,$A2)=0
Color column C: =COUNTIF($A$2:$A$10,$C2)=0

Here are the uniques I've got:

Example 2. Find and highlight duplicates in two columns in Google Sheets

You can colour common values after slight modifications in both formulas from the previous example. Just make the formula count everything greater than zero.

Color dupes between columns in A only: =COUNTIF($C$2:$C$9,$A2)>0
Color dupes between columns in C only: =COUNTIF($A$2:$A$10,$C2)>0

Add-on to compare & highlight duplicates or uniques

Compare columns or sheets is intuitive enough to help you match two columns within one table, two different tables on one sheet, or even two separate sheets, and highlight those uniques or dupes that may sneak into your data.

Here's how I highlighted duplicates between two tables based on Fruit and MSRP columns using the tool:

I can also save these settings into a reusable scenario. If the records update, I will call for this scenario in just a click and the add-on will immediately start processing all the data. Thus, I avoid tweaking all those settings over the add-on steps repeatedly. You will see how scenarios work in the example above and in this tutorial.

Add-on to compare Google sheets and highlight differences

Compare sheets cell by cell doesn't fall behind. It sees all differences between two columns or sheets. In fact, it compares as many sheets as you need, even from different files. Usually, one of these tables acts as your main one, and you compare it with others. The add-on highlights differences on those other sheets so you could spot them instantly:

This help page and the demo video below will give you a better idea of how it compares multiple Google sheets for differences:

Make sure to install the add-on from the Google store to follow the instructions along.