How to compare two columns in Excel
By the Analistable team · Updated · 2 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?
| Question | Formula |
|---|---|
| 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").
| A (Old SKU) | B (New SKU) | =A2=B2 | =EXACT(A2,B2) |
|---|---|---|---|
| AB-100 | AB-100 | TRUE | TRUE |
| ab-101 | AB-101 | TRUE | FALSE |
| AB-102 | AB-103 | FALSE | FALSE |
2. Select differences with Ctrl+\
- Select both columns, starting from the column you want to compare against (the active cell's column is the reference).
- Press Ctrl+\ (or Home → Find & Select → Go To Special → Row differences).
- 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.
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.