Guides › How to Compare Two Columns in Excel or Google Sheets and Find Differences
How to Compare Two Columns in Excel or Google Sheets and Find Differences
You have two columns — customers from this month and last month, SKUs in two systems, two mailing lists — and you need to know what is in one but not the other. Here is how to do it with formulas, and when a dedicated tool is quicker.
The result you want
When you compare two lists there are three useful answers: items only in A, items only in B, and items in both (the overlap). Everything below is a way to get one or more of those.
Method 1: COUNTIF (works in every version)
With list A in column A and list B in column B, put this in C2 and fill down:
=IF(COUNTIF($B:$B, A2)=0, "Only in A", "In both")COUNTIF counts how many times the value in A2 appears in column B. Zero means it is missing from B. To check the other direction, swap the columns in a second formula in column D.
Method 2: VLOOKUP or XLOOKUP
=IF(ISNA(VLOOKUP(A2, $B:$B, 1, FALSE)), "Only in A", "In both")In Excel 365 and Google Sheets you can write the shorter =IF(ISNUMBER(MATCH(A2, $B:$B, 0)), "In both", "Only in A"). All of these do the same job; COUNTIF is the easiest to remember.
Method 3: list the differences with FILTER
In Excel 365 and Google Sheets, one formula can return the whole list of items that are only in A:
=FILTER(A2:A500, COUNTIF(B2:B500, A2:A500)=0)Swap the two ranges to get the items only in B. This produces a clean list you can copy, rather than a flag next to every row.
Method 4: highlight with conditional formatting
Select column A, choose Conditional Formatting then New Rule then Use a formula, and enter =COUNTIF($B:$B, A1)=0. Pick a fill colour. Every value in A that is missing from B lights up. In Google Sheets the same rule is under Format then Conditional formatting with a “custom formula”.
Why a comparison shows false differences
When two lists that look identical show mismatches, the cause is almost always one of these:
- Extra spaces. “Apple” and “Apple ” are different values. Wrap lookups in
TRIM()or clean the data first. - Capital letters.
COUNTIFignores case, but exact-match functions such asEXACTand some tools do not. Decide whether “apple” and “Apple” should match. - Numbers stored as text. The number 7 and the text “7” do not match. Convert both columns to the same type.
- Leading zeros. “007” and “7” look different even though they are often meant to be the same ID.
- Invisible characters. Non-breaking spaces from web pages can look like normal spaces but are not.
The no-formula method
Formulas get awkward with big lists, several comparisons or data from different files. Copy each column and paste them into Compare Lists. It shows only in A, only in B, in both and the combined list at once, with options to ignore case, extra spaces, leading zeros and punctuation, so the false mismatches above disappear. You can copy any result straight back into your spreadsheet, or send it into one of the lists to compare against something else.
Comparing rows, not just single values
If your data is a table, such as an email plus a name, compare on the column that identifies a row. In Compare Lists, paste the whole rows and enter the column number to compare on; the full row is kept in the results. In a spreadsheet, build the formulas on that one identifying column.