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?
| 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.
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"))| A (Ledger) | B (Bank) | =A2=B2 | Tolerance check |
|---|---|---|---|
| 250.00 | 250.00 | TRUE | Match |
| 99.999 | 100.00 | FALSE | Match |
| 410.00 | 401.00 | FALSE | Diff -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 withSUBSTITUTE(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=B2returns 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:
| Question | Formula |
|---|---|
| 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$2in 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.