Compare Two Columns
The =A2=B2 test everyone starts with is looser than it looks — it ignores capitalisation, so "Smith" and "SMITH" come back TRUE. In the other direction it is far stricter than it looks about type: a reference typed as text never equals the same reference stored as a number, which is why a reconciliation can return FALSE on every single row while the two columns look identical on screen. Before deciding what the comparison is telling you, settle those two questions — does case matter here, and are both columns really the same kind of value.
The formula
=A2=B2 matches, ignoring capitals; strict about type
=EXACT(A2,B2) matches, capitals included
=TRIM(A2)=TRIM(B2) rules out a stray leading or trailing space
=VALUE(A2)=B2 rules out one side being a number stored as text
=COUNTIF(Sheet2!$A:$A,A2) the same question asked against another sheet
select A2:B100, Ctrl+\ selects every cell in B that differs from A in its rowA worked example
A2:A400 holds invoice references from the accounting system and B2:B400 holds the same references pulled out of the bank export. On screen they are identical.
=A2=B2 filled down returns FALSE on all 399 rows. =TRIM(A2)=TRIM(B2) returns TRUE on all 399.
The bank export carries a trailing space on every value. Nothing was ever mismatched, and a comparison that reported 399 exceptions would have sent someone through the whole list by hand. Left-alignment on one column and right-alignment on the other points at the other version of the same trap, where one side is text and the other is a number.
Which one do I need?
Frequently asked questions
Why does my comparison say every single row is different?
Three causes account for nearly all of it. One column is text and the other numbers, so "1001" never equals 1001 — the giveaway is that one column sits left-aligned and the other right. Or a trailing space rides along on one side, which =TRIM(A2)=TRIM(B2) proves in a single cell. Or the values are dates, one held as a real date and the other as text that merely displays like one.
How do I make the comparison case-sensitive?
Use =EXACT(A2,B2) instead of =A2=B2. The = operator, COUNTIF, MATCH and VLOOKUP are all case-blind, so every one of them will report "Smith" and "SMITH" as the same value. EXACT is the only comparison in Excel that reads capitals, and it can be nested inside other functions — =SUMPRODUCT(--EXACT(A2,$B$2:$B$500))=0, for instance, asks the list-membership question case-sensitively.
What does Ctrl+backslash do?
It is Go To Special > Row differences on a shortcut. Select two adjacent columns — A2:B100, say — and press Ctrl+\ , and Excel selects each cell in the second column whose value differs from the first column in the same row, ready to be coloured in one click. It compares against whichever column the active cell sits in, so start the selection from the column you regard as correct.
Can I compare columns that live on different sheets?
Yes — only the range changes. =COUNTIF(Sheet2!$A:$A,A2)=0 asks whether the value in A2 is missing from column A of Sheet2, and the same pattern works with MATCH or XLOOKUP. Quote the sheet name whenever it has a space in it, as in 'Bank Export'!$A:$A. Referring to a closed workbook this way works with some functions and fails with others, so keep both files open.
New guides and tools, once a month
DE + EN · double opt-in · no spam