How to compare two columns in Google Sheets

Quick answer

First decide what "compare" means for your data. If row 2 of column A should equal row 2 of column B, compare cell by cell. If the columns are two lists in any order, such as emails or product codes, look up each value in the other column with COUNTIF. The examples below assume the lists are in columns A and B, with headers in row 1.

Compare two columns row by row for differences

To check whether each pair of cells is the same, enter this in C2 and fill it down:

=A2=B2

It returns TRUE for a match and FALSE for a difference. The = comparison ignores letter case, so "Apple" and "apple" count as equal. For a case-sensitive check, use =EXACT(A2, B2).

For readable labels, wrap either one in IF: =IF(A2=B2, "Match", "Different") or =IF(EXACT(A2, B2), "Match", "Different"). To label the whole column with one formula instead of filling down, use:

=ARRAYFORMULA(IF(A2:A&B2:B="", "", IF(A2:A=B2:B, "Match", "Different")))

Find missing values between two columns

When the lists are in different orders, check whether each value in column A appears anywhere in column B. Both of these return TRUE when the value is missing:

=COUNTIF(B:B, A2)=0
=ISNA(MATCH(A2, B:B, 0))

Fill either one down beside column A. To go the other way, put =COUNTIF(A:A, B2)=0 beside column B and you get the values in B that are missing from A.

To list every missing value at once, put this in an empty column, such as D2:

=FILTER(A2:A, A2:A<>"", COUNTIF(B2:B, A2:A)=0)

The A2:A<>"" condition skips blank rows. The results spill downward, so keep the cells below empty. If nothing is missing, FILTER returns an #N/A error. For a cleaner result, use =IFERROR(FILTER(A2:A, A2:A<>"", COUNTIF(B2:B, A2:A)=0), "Nothing missing").

Compare two columns for matches

To list the values that appear in both columns, change the condition to greater than zero:

=FILTER(A2:A, A2:A<>"", COUNTIF(B2:B, A2:A)>0)

If a value appears twice in column A, it appears twice in the results. Wrap the formula in UNIQUE() to list each match once.

Highlight differences and matching data with conditional formatting

A helper column is easy to check, but color is quicker to scan. To highlight values in column A that are missing from column B:

1. Select A2:A1000, or however far your list goes.
2. Choose Format → Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Enter =AND(A2<>"", COUNTIF($B:$B, A2)=0), pick a fill color and click Done.

The formula is written for the first cell of the range, A2, and Google Sheets adjusts it for every row below. The $ signs keep the lookup fixed on column B. If your range starts in row 1, write A1 instead, or every highlight lands one row off.

Other rules use the same steps with a different formula:

• Highlight matching data (values in both columns, often called duplicates between columns): =AND(A2<>"", COUNTIF($B:$B, A2)>0)
• Values in B missing from A: select B2:B1000 and use =AND(B2<>"", COUNTIF($A:$A, B2)=0)
• Differences in the same row: select A2:B1000 and use =$A2<>$B2. Both cells of each mismatched row are colored. Use =NOT(EXACT($A2, $B2)) if case matters.

Compare two lists on different tabs

Regular formulas can point straight at another tab. If the second list is in column A of a tab called Sheet2, this lists the values missing from it:

=FILTER(A2:A, A2:A<>"", COUNTIF(Sheet2!A2:A, A2:A)=0)

Conditional formatting is stricter: a custom formula cannot reference another tab directly. Wrap the range in INDIRECT instead:

=AND(A2<>"", COUNTIF(INDIRECT("Sheet2!A:A"), A2)=0)

If the tab name has spaces, add single quotes inside the text, as in INDIRECT("'Price List'!A:A"). The text does not update when a tab is renamed, so edit the rule if you rename it. To compare whole rows across tabs rather than one column, see how to compare two Google Sheets tabs.

Why values that look the same don't match

• Extra spaces: a trailing space makes Acme and Acme different. Select both columns and use Data → Data cleanup → Trim whitespace, or compare TRIM(A2) with TRIM(B2).
• Letter case: =, COUNTIF and MATCH all ignore case. If "ABC" and "abc" should count as different, use EXACT. For a case-sensitive lookup, =SUMPRODUCT(--EXACT(A2, $B$2:$B$1000))=0 returns TRUE when column B has no exact match.
• Numbers stored as text: an ID typed as a number in one column and imported as text in the other may not match. Format both columns the same way, or compare TO_TEXT() of each value.
• Repeated values: COUNTIF tells you a value exists, not how often. If a list should be unique, check it first with =COUNTIF(A:A, A2)>1. See finding duplicate rows between tabs.

When to use SheetDiff instead of formulas

The formulas above are the right tool when both columns sit on one tab. SheetDiff, a Google Sheets add-on, is for bigger jobs:

• The two lists are on different tabs and you want both directions, missing from each side, in one report.
• One list is a CSV or Excel export, or lives in another Google Sheet. See comparing a CSV file with a Google Sheet.
• Each row has several columns, and you also want to know which values changed, not just whether an ID is present.

1. Choose Extensions → SheetDiff → Start.
2. Pick the two tabs, or upload a CSV or Excel file, or paste the link to another Google Sheet.
3. Choose how rows are matched: by a key column such as an ID or email, by whole row, or by row order.
4. Click Compare Sheets.

The report goes to a new tab: rows only in the first sheet, rows only in the second, changed rows with old → new values, and a count of matching rows. Matching ignores letter case and extra spaces unless you tick Case sensitive comparison. Your original tabs are not modified.

SheetDiff compares two different tabs, so it cannot compare two columns on the same tab. If yours are, use the formulas above, or copy one column to a new tab first. To compare just one column of each tab, open Advanced Options and fill in the custom range for each sheet, such as A1:A500. The first row of each range is read as the header, so include it and give both columns the same header.

Coloring the differences inside your own tabs is a Pro feature. The free plan gives you the full counts and the report, as described below.

Is COUNTIF case-sensitive in Google Sheets?

No. COUNTIF, MATCH and the = operator all treat Apple and apple as the same value. Use EXACT when letter case matters.

Why does my conditional formatting highlight the wrong rows?

The custom formula has to refer to the first row of the range the rule applies to. A rule on A2:A1000 should use A2, not A1. Also check that the lookup column is anchored with dollar signs, as in $B:$B.

Can SheetDiff compare two columns on the same tab?

No. SheetDiff compares two different tabs, or a tab with a CSV file, an Excel file or another Google Sheet. For two columns on one tab, use the COUNTIF formulas or conditional formatting, or copy one column to its own tab first.

Is SheetDiff free?

Yes. The free plan compares lists of any size, 5 times a day. Every count is shown in full, and each list shows its first 25 rows. Pro is $10 a year and lists every difference, removes the daily limit, and can highlight differences in your own tabs.