Maybe you have sufficient reasons to compare two sheets in Google Sheets and identify differences. Whatever the reason, you can easily do it.

For example, if you make a duplicate copy of a sheet by right-clicking the tab name and selecting “Duplicate,” and working on the duplicate, you might want to check the differences between both sheets at a later date.

No matter how many columns there are in the sheets, you can find the differences hassle-free with a formula.

We will use a third sheet in the same file to return the differing values in the corresponding cells.

For example, if Sheet1 cell A3 contains ‘Gravel’ and the corresponding cell in Sheet2 contains ‘Gravel 10-20’, cell A3 in Sheet3 will display ‘Gravel | Gravel 10-20’, showing the values from both sheets.

Data in Sheet1 and Sheet2 Side by Side:

Differences Identified in the Third Sheet:

This way, you can not only find the differences but also identify the cell in which the differences appear. Here is how to do that.

Compare Two Sheets and Extract Differences in a Third Sheet

We can use either a non-array formula in all cells in the third sheet or an array formula only in cell A1 in the third sheet.

If you have a very large dataset in the sheets to compare, I suggest using the array formula section by section, such as for the first 100 rows and 26 columns, then the next 100 rows and 26 columns, and so on. Let’s start with the non-array formula first.

Step-by-Step Instructions

In cell A1 of the third sheet, enter the following formula:

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

In this formula, replace “Sheet1” with the first sheet name and “Sheet2” with the second sheet name. If there is a space character in the sheet name, you should enter it within apostrophes, such as 'January sales' for the sheet with the tab name “January sales.”

Now copy and paste the entered formula from cell A1 to the rest of the sheet.

That’s all. This formula will show the differences in corresponding cells.

How Does This Formula Compare Two Sheets and Find the Differences?

The above formula compares the contents of cell A1 between Sheet1 and Sheet2. It’s a logical test that utilizes the JOIN function to combine values:

The IF function tests the values in Sheet1!A1 and Sheet2!A1 for differences and returns the result of the JOIN function if they are not equal, otherwise returns a blank.

Syntax: IF(logical_expression, value_if_true, value_if_false)

• logical_expression: Sheet1!A1 <> Sheet2!A1 checks if the value in Sheet1 cell A1 is different from the value in Sheet2 cell A1.
• value_if_true: JOIN(" | ", Sheet1!A1, Sheet2!A1) concatenates the values from Sheet1 cell A1 and Sheet2 cell A1 with a pipe symbol ( | ) as the delimiter if they are different.
• value_if_false: If the values are the same, the formula returns an empty string.

Array Formula to Compare Two Sheets in a Third Sheet

If you prefer an array formula, then here you go!

Assume you have 1000 rows and 26 columns in both sheets, i.e., the range A1:Z1000.

You can use the following formula in cell A1 of the third sheet, provided the third sheet is blank:

=MAP(Sheet1!A1:Z1000, Sheet2!A1:Z1000, lambda(x, y, IF(x<> y, JOIN(" | ", x, y),)))

This formula will compare the values cell by cell and return wherever a mismatch occurs.

One thing to note is that you shouldn’t feed a very large range to this formula as it may break or affect the performance of the sheet due to the use of LAMBDA.

Another thing is that the sheet ranges in both sheets used in the formula should match in size.

Formula Break-Down

LAMBDA(x, y, IF(x <> y, JOIN(" | ", x, y), "")) – A custom LAMBDA function that tests the elements x and y, and if they do not match, joins them with a pipe delimiter between them.

Syntax: LAMBDA([name, …], formula_expression)

Where:

• name1: x (the first name used as an identifier to be used inside the formula_expression)
• name2: y (the second name used as an identifier to be used inside the formula_expression)
• formula_expression: IF(x <> y, JOIN(" | ", x, y), "")

We have used the custom LAMBDA function within MAP to map those elements in two arrays and return an equally sized array result.

Syntax: MAP(array1, [array2, …], lambda)

Where:

• array1: Sheet1!A1:Z1000
• array2: Sheet2!A1:Z1000
• lambda: LAMBDA(x, y, IF(x <> y, JOIN(" | ", x, y), ""))

Resources

• Compare Two Tables and Remove Duplicates in Google Sheets
• Google Sheets: Compare Two Lists and Extract Differences
• Compare Two Sets of Multi-Column Data for Differences in Google Sheets
• Compare Two Google Sheets Cell by Cell and Highlight