Comparing Two Google Sheets for Differences

Guide · Updated June 2026

Method 1: ISNA + MATCH formula

In a blank column next to List A, use:

=IF(ISNA(MATCH(A2, B:B, 0)), "Not in B", "Match")

This flags every A2 value that has no match anywhere in column B. Copy the formula down the column, then use Data → Create a filterto isolate the “Not in B” rows. Swap the ranges to check the reverse direction.

Method 2: Conditional formatting

Select both ranges, open Format → Conditional formatting, and use a custom formula like =COUNTIF($B$:$B, A1)=0 to highlight cells in A with no match in B. Good for a quick visual pass over a small sheet; harder to read once a list runs into the thousands of rows.

A faster way: paste both columns into a comparison tool

Select column A, copy, and paste into List 1; do the same for column B into List 2. You immediately get everything only in A, only in B, what’s shared, and the full combined set — no helper columns, no filters to remember to remove afterward. Toggle case sensitive if your sheet uses codes where case matters, and natural sort if you want numeric IDs ordered correctly instead of alphabetically.

Which is better?

If the comparison needs to live inside the spreadsheet — recalculating as the sheet updates — formulas are the right call. For a one-off “what changed between these two exports” question, pasting into a dedicated comparison tool is faster and easier to read.

Worked example

Two sheets tracking the same order IDs, one week apart:

Copy each column into a list panel and compare. row-104 and row-106 match (intersection); row-105 and row-107 dropped out of Sheet 2 (only in List 1); row-108 and row-109 are new to Sheet 2 (only in List 2). No ISNA/MATCH formula to write or clean up afterward.