# Compare Two Sheets in Google Sheets (Find Differences)

To compare two sheets in Google Sheets you have two different jobs. One is a cell-by-cell comparison of two tabs with the same layout. The other is matching rows by a key, to find rows in Sheet1 that are not in Sheet2. This page covers both with formulas, plus a highlighting rule that works across tabs.

## The sample data

Sheet1 A1:B5 (Item, Qty): Apple 5; Banana 3; Cherry 7; Date 2.

Sheet2 A1:B5 (Item, Qty): Apple 5; Banana 4; Cherry 7; Dates 2.

Two things differ: Banana has quantity 3 on one sheet and 4 on the other, and "Date" is spelled "Dates" on Sheet2. Add a third tab called Diff for the results.

## Method 1: cell-by-cell differences on a third sheet

In Diff!A1, one formula fills the whole grid:

```plaintext
=ARRAYFORMULA(IF(Sheet1!A1:B5=Sheet2!A1:B5,"",Sheet1!A1:B5&" -> "&Sheet2!A1:B5))
```

Result: every cell is blank except Diff!B3 ("3 -> 4") and Diff!A5 ("Date -> Dates"). A blank cell means the two sheets agree. Both ranges must be the same size, so use the same corner and the same number of rows and columns.

The = comparison ignores case, so "apple" equals "Apple". For a case-sensitive version use EXACT:

```plaintext
=ARRAYFORMULA(IF(EXACT(Sheet1!A1:B5,Sheet2!A1:B5),"",Sheet1!A1:B5&" -> "&Sheet2!A1:B5))
```

To count the differences:

```plaintext
=SUMPRODUCT(--(Sheet1!A1:B5<>Sheet2!A1:B5))
```

Result: 2.

This only works when row 3 on one sheet is meant to be row 3 on the other. If the rows are in a different order, or one sheet has an extra row, every row after it will differ. In that case, match by key instead.

## Method 2: match rows by key with COUNTIF or MATCH

In Sheet1!C2, fill down:

```plaintext
=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing in Sheet2","Found"))
```

Result for C2:C5: Found, Found, Found, Missing in Sheet2. "Date" is flagged because Sheet2 only has "Dates". COUNTIF matches the whole cell, not part of it.

The same with MATCH:

```plaintext
=IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$100,0)),"Missing in Sheet2","Found"))
```

The result is identical. MATCH is handy when you also want the position.

To go one step further and compare the quantity of the matched row, in D2:

```plaintext
=IF(A2="","",IFERROR(IF(INDEX(Sheet2!$B$2:$B$100,MATCH(A2,Sheet2!$A$2:$A$100,0))=B2,"Same","Different"),"Missing in Sheet2"))
```

Result for D2:D5: Same, Different, Same, Missing in Sheet2.

To find rows in Sheet2 that are missing from Sheet1, put the mirror formula on Sheet2 with the sheet names swapped.

## Method 3: FILTER the rows missing from the other sheet

This lists the rows themselves, with no helper column. In Diff!A1:

```plaintext
=FILTER(Sheet1!A2:B100,Sheet1!A2:A100<>"",COUNTIF(Sheet2!A2:A100,Sheet1!A2:A100)=0)
```

Result: one row, Date 2. For the other direction, swap the sheets:

```plaintext
=FILTER(Sheet2!A2:B100,Sheet2!A2:A100<>"",COUNTIF(Sheet1!A2:A100,Sheet2!A2:A100)=0)
```

Result: Dates 2. If nothing is missing, FILTER shows an error saying no matches, so wrap it in IFERROR(...,"Nothing missing") if you want a clean message.

To list rows where the whole row (Item and Qty) has no exact twin, use COUNTIFS on both columns:

```plaintext
=FILTER(Sheet1!A2:B100,Sheet1!A2:A100<>"",COUNTIFS(Sheet2!A2:A100,Sheet1!A2:A100,Sheet2!B2:B100,Sheet1!B2:B100)=0)
```

Result: Banana 3 and Date 2. Banana's row has no twin because its quantity differs.

## Method 4: highlight differences with conditional formatting and INDIRECT

A conditional formatting rule cannot point straight at another sheet. INDIRECT turns text into a reference and gets around that. Select Sheet1!A2:A100, open Format > Conditional formatting, choose "Custom formula is", and use:

```plaintext
=AND($A2<>"",COUNTIF(INDIRECT("Sheet2!$A$2:$A$100"),$A2)=0)
```

Result: A5 (Date) is highlighted. If the tab name has spaces, use INDIRECT("'Old List'!$A$2:$A$100").

For a cell-by-cell highlight, select Sheet1!A2:B100 and use:

```plaintext
=A2<>INDIRECT("Sheet2!"&ADDRESS(ROW(),COLUMN(),4))
```

Result: B3 (3 against 4) and A5 (Date against Dates) are highlighted. ADDRESS with the 4 builds a plain reference such as "B3", so each cell is compared with the same cell on Sheet2.

## Why a comparison looks wrong

- Extra spaces. "Banana" and "Banana " differ. Use Data > Data cleanup > Trim whitespace on both sheets first.
- Numbers stored as text. "5" typed as text does not equal the number 5.
- Different sized ranges in Method 1 cause an error.
- Sorted differently. Use Methods 2 and 3, which look rows up by key, rather than Method 1.
- Spelling differences. "Date" and "Dates" are simply different strings, and none of the formulas above treat them as the same item.

## When an add-on or script pack helps

Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.

Methods 2 and 3 only match keys that are exactly the same. If the two sheets come from different systems, with "Date" on one and "Dates", "Dte" or "date " on the other, you will see a lot of false "missing" rows. Fuzzy Match Template for Google Sheets (US$9, no add-on install): <https://snakback.gumroad.com/l/fuzzy-formulas>. It adds FUZZYLOOKUP(values, lookup, return_range, \[threshold\]), a fuzzy XLOOKUP, and FUZZYMATCH and FUZZYSCORE, so near-matches can be paired instead of flagged as missing. You paste the functions into your sheet once, so it works where a Workspace admin blocks add-ons. There is a 30-day refund if it does not work for you. If both sheets use the same clean IDs, the free formulas above are enough.

Fuzzy Match, our free add-on, is coming soon to the Google Workspace Marketplace.

Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.

---

*Originally published at* [*https://www.snakbacks.com/compare-two-sheets-google-sheets*](https://www.snakbacks.com/compare-two-sheets-google-sheets)*. Written with AI assistance; every formula on this page was tested in a real Google Sheet.*
