Analistable

How to compare two columns in Excel

By the Analistable team · Updated · 5 min read

To compare two columns row by row, use =A2=B2 (TRUE when they match, ignoring case) or =EXACT(A2,B2) for a case-sensitive check. To see whether each value in column A appears anywhere in column B, use =COUNTIF(B:B, A2)>0 or =ISNUMBER(XMATCH(A2, B:B)). To select every row where the columns differ, select both columns and press Ctrl+\.

Part of our guide: How to compare two spreadsheets for differences and matches

Row by row or anywhere in the column?

Pick the comparison
QuestionFormula
Is A2 the same as B2?=A2=B2
Same, including upper/lower case?=EXACT(A2, B2)
Does A2 appear anywhere in column B?=COUNTIF(B:B, A2)>0
Which values in A are missing from B?=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0)

1. Row by row with a formula

In C2, enter =IF(A2=B2, "Match", "Different") and fill down. = ignores case, so “ACME” equals “acme”. For codes where case matters, use =IF(EXACT(A2,B2), "Match", "Different").

Row-by-row comparison
A (Old SKU)B (New SKU)=A2=B2=EXACT(A2,B2)
AB-100AB-100TRUETRUE
ab-101AB-101TRUEFALSE
AB-102AB-103FALSEFALSE

2. Select differences with Ctrl+\

  1. Select both columns, starting from the column you want to compare against (the active cell's column is the reference).
  2. Press Ctrl+\ (or Home → Find & Select → Go To Special → Row differences).
  3. Excel selects every cell in the second column that differs from the first in the same row. Apply a fill colour to mark them.

3. Highlight with conditional formatting

Select A2:B100, choose Home → Conditional Formatting → New Rule → Use a formula, enter =$A2<>$B2 and choose a fill. Whole rows that differ are highlighted. To highlight values that appear in both columns regardless of row, use Highlight Cells Rules → Duplicate Values on the two columns together.

4. Find values missing from the other column

When the columns are separate lists (this month's customers vs last month's), the order doesn't matter. Next to column A:

=IF(COUNTIF($B$2:$B$100, A2)=0, "Not in B", "")

In Microsoft 365, list the missing values in one formula: =FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0, "None"). More patterns, including partial matches, are in finding matches between two lists.

5. Compare columns in different sheets or files

The same formulas work with sheet references: =COUNTIF(Sheet2!A:A, A2)>0. For whole sheets, see comparing two Excel sheets for differences.

If matches you expect aren't found, check for trailing spaces (=LEN(A2) vs =LEN(TRIM(A2))) and numbers stored as text.

Compare columns in Google Sheets

The same formulas work in Google Sheets: =A2=B2, =EXACT(A2,B2) and =COUNTIF(B:B, A2)>0. Sheets has no Ctrl+\ shortcut, so use a conditional formatting rule with a custom formula such as =$A2<>$B2.

Compare numbers with a tolerance

Two columns of amounts calculated in different systems often differ by a fraction of a penny, so =A2=B2 returns FALSE even though the values are the same for practical purposes. Compare the absolute difference against a tolerance instead:

=IF(ABS(A2-B2)<=0.01, "Match", "Diff "&TEXT(B2-A2, "0.00"))
Invoice totals from two systems
A (Ledger)B (Bank)=A2=B2Tolerance check
250.00250.00TRUEMatch
99.999100.00FALSEMatch
410.00401.00FALSEDiff -9.00

The second row is a rounding difference, not an error. The third is a real one — a typical transposed-digit typo, where the difference is divisible by 9.

Compare text that is almost the same

  • Ignore spaces: =TRIM(A2)=TRIM(B2). TRIM removes leading, trailing and repeated inner spaces, but not non-breaking spaces from web pages; remove those with SUBSTITUTE(A2, CHAR(160), " ").
  • Ignore punctuation in codes: =SUBSTITUTE(A2, "-", "")=SUBSTITUTE(B2, "-", "") treats AB-100 and AB100 as equal.
  • Compare the first part only: =LEFT(A2, 6)=LEFT(B2, 6) checks just a prefix such as an account code.
  • Number vs text: =A2=B2 returns FALSE for 100 and "100". Use =A2&""=B2&"" to compare both as text.

Count matches and differences

Before scrolling through a long column, get the totals:

Summary formulas
QuestionFormula
How many rows match?=SUMPRODUCT(--(A2:A100=B2:B100))
How many rows differ?=SUMPRODUCT(--(A2:A100<>B2:B100))
How many A values are missing from B?=SUMPRODUCT(--(COUNTIF(B2:B100, A2:A100)=0))

SUMPRODUCT works in every Excel version without Ctrl+Shift+Enter. Blank rows at the bottom of both ranges count as matches, so trim the ranges to the data or subtract the blank count.

Excel for Mac and older versions

On a Mac, Row differences is in Go To Special: press Control+G (or choose Edit → Find → Go To), click Special… and choose Row differences. FILTER and XMATCH need Microsoft 365 or Excel 2021; in Excel 2016 and 2019, use the COUNTIF and MATCH versions and filter the helper column with AutoFilter instead.

Common mistakes

  • Selecting the columns in the wrong order for Ctrl+\ — the first selected column is the reference, so differences are highlighted in the other one.
  • Using $A$2 in a conditional formatting rule, which compares every row with row 2.
  • Whole-column references in thousands of COUNTIF formulas, which slow the workbook. Use fixed ranges such as $B$2:$B$5000.
  • Comparing two lists row by row when they are not in the same order. Sorting both first only helps if they contain exactly the same items.

Repeat the check every month

If you compare the same two columns after every export — say, last month's SKU list against this month's — turn the data into tables (Insert → Table) before adding the formulas. Formulas written against a table, such as =COUNTIF(Old[SKU], [@SKU])=0, fill down automatically when you paste in more rows, and the conditional formatting rules extend with the table. Next month, paste the new export over the old table's data and the flags update. For larger lists or a comparison that combines several files, a Power Query anti join is sturdier because it does not depend on pasting into the right place.

Frequently asked questions

How do I compare two columns in Excel for matches?
Use =A2=B2 for row-by-row matches, or =COUNTIF(B:B, A2)>0 to check whether each value in A appears anywhere in B.
How do I highlight differences between two columns?
Select both columns and press Ctrl+\ to select row differences, or use a conditional formatting rule =$A2<>$B2.
Is the comparison case-sensitive?
No, = and COUNTIF ignore case. Use EXACT for a case-sensitive comparison.
How do I compare two columns of numbers that differ by rounding?
Use =ABS(A2-B2)<=0.01 (or another tolerance) instead of =A2=B2, so tiny rounding differences count as matches.
How do I compare two columns and return a value from a third?
Use XLOOKUP: =XLOOKUP(A2, B:B, C:C, "Not found") returns the value in column C for the row where column B matches A2.

Related guides