How to Compare Two Columns in Excel for Differences

Spreadsheets get messy. You inherit a workbook with two lists that should match, or you stitch together exports from different systems, and now you need to know which rows agree and which don't. The good news? Excel has roughly a dozen ways to do column compare excel work, and each one solves a slightly different flavour of the problem.

This guide walks through every reliable method — from the dead-simple =A1=B1 trick up to Power Query's left-anti join. You'll see when to reach for conditional formatting versus a formula, why EXACT behaves differently to the equals sign, and how to dodge the silent killers (trailing spaces, mixed text/number formats, leading apostrophes) that quietly break otherwise correct comparisons.

Pick whichever method fits your data. Short lists? Use the equals operator. Two huge customer exports? Power Query. Need a visual map of mismatches across a worksheet? Conditional formatting wins. We'll show you all of it — with the exact syntax, the gotchas, and a few tricks the documentation tends to skip over.

Method 1: The Equals Operator — fastest way to spot mismatches

If you only remember one trick from this article, make it this. Drop into cell C1 and type =A1=B1. Press Enter. Excel returns either TRUE (the cells match) or FALSE (they don't). Drag the formula down the length of your data and you have an instant audit column.

It's almost too simple, which is why most people skip past it. But for a quick eyeball check across a few hundred rows, nothing beats it. Filter the audit column for FALSE and the differences jump out — done in twenty seconds.

One catch worth knowing: the equals operator is not case-sensitive. =A1=B1 returns TRUE for "Apple" versus "apple". For most real-world data that's exactly what you want. When you do need case sensitivity, skip ahead to the EXACT function below.

The operator also handles numbers and text the same way, but watch out for one nasty quirk — Excel sometimes stores numbers as text (look for that little green triangle in the corner of the cell). A "5" stored as text will compare FALSE against a 5 stored as a number. If you're seeing unexpected mismatches, run the column through =VALUE() first to force-convert.

Method 2: IF Formula — replace TRUE/FALSE with readable labels

TRUE/FALSE is fine for a quick scan, but when you're handing the workbook to a colleague, "Match" / "Different" reads a lot cleaner. That's where =IF(A1=B1,"Match","Different") comes in. It does exactly what the equals operator does, just with human-friendly output.

You can take this further. Want to flag only the mismatches and leave matches blank? Use =IF(A1=B1,"","Different"). Need to surface the actual difference? Try =IF(A1=B1,"OK","Was: "&A1&", Now: "&B1) — that builds a tiny audit message inline.

For numeric columns where you care about magnitude, swap the equals check for a tolerance: =IF(ABS(A1-B1)<0.01,"Match","Off by "&ROUND(A1-B1,2)). This pattern saves grief when comparing currency values that occasionally drift by a fraction of a cent due to rounding upstream.

Nest more conditions and IF becomes a Swiss Army knife — but past three levels of nesting, you're better off using IFS (Excel 2019+) or switching to a lookup table. Anything more than that is a hint you need pivot tables in excel or Power Query instead.

Method 3: Conditional Formatting — see the differences instead of calculating them

Sometimes you don't want a third column of TRUE/FALSE. You want the data itself to light up. Select both columns, head to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and pick Unique in the dropdown (yes — unique, not duplicate). Every value that appears in one column but not the other gets a colour wash.

That built-in rule compares within the combined range, so it surfaces orphaned values regardless of which row they sit on. Brilliant for catching items that exist in one list but not the other.

For row-by-row checking — where you want cell A5 compared specifically against B5 — the built-in rule won't cut it. Use a custom formula instead. Select your range, then go to Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter =$A1<>$B1 and pick a fill colour. Every row where the two cells disagree now glows in your chosen shade.

The dollar signs matter. $A1 locks the column but lets the row float, which is exactly what you want when the rule cascades down the selection. Drop the dollar signs and the rule starts comparing against itself in weird ways.

Method 4: EXACT Function for case-sensitive comparisons

The equals operator treats "USA" and "usa" as identical. Usually that's what you want. But when you're auditing product SKUs, codes that distinguish "ABC123" from "abc123", or anywhere case actually means something, you need =EXACT(A1,B1).

EXACT returns TRUE only when every character — including the case — matches. It still ignores formatting differences (bold, colour, font) because those aren't part of the cell value, but the text itself has to be a byte-for-byte match.

Pair it with IF for cleaner output: =IF(EXACT(A1,B1),"Identical","Mismatch"). And if you want a case-sensitive count of mismatches across a whole range, wrap it in SUMPRODUCT — more on that in Method 5.

One subtle point: EXACT does not ignore leading or trailing spaces. "USA " (with trailing space) returns FALSE against "USA". For text imports from CSV or web scrapes, that bites people constantly. Run both columns through =TRIM() first, or compare with =EXACT(TRIM(A1),TRIM(B1)) in a single shot.

Method 5: Count the differences across a whole range

The audit column tells you which rows mismatch. But sometimes you just need the headline number — "47 of 1,200 rows are different." That's a job for SUMPRODUCT:

=SUMPRODUCT(--($A$1:$A$1000<>$B$1:$B$1000))

What's happening here? The expression $A$1:$A$1000<>$B$1:$B$1000 returns a thousand-element array of TRUE/FALSE values, one per row pair. The double-negative -- coerces those booleans into 1s and 0s. SUMPRODUCT then sums the array. End result: a single integer telling you how many rows disagree.

Want matches instead? Flip the comparison: =SUMPRODUCT(--($A$1:$A$1000=$B$1:$B$1000)). Need case-sensitive counting? Swap in EXACT: =SUMPRODUCT(--(NOT(EXACT($A$1:$A$1000,$B$1:$B$1000)))).

For Excel 2021 and Microsoft 365 users, you can do this even cleaner with =ROWS(FILTER(A1:A1000, A1:A1000<>B1:B1000)) which returns the count of differing rows using dynamic arrays. Same answer, slightly more readable formula.

Method 6: VLOOKUP / XLOOKUP — find values that exist only in one column

Row-by-row comparison only works when your two lists are aligned. What if Column A is a customer master list and Column B is last month's order export — same kind of data, but different order, different length, lots of overlap? Now you need a lookup function.

The classic VLOOKUP pattern is =ISNA(VLOOKUP(A1,B:B,1,FALSE)). For each value in column A, VLOOKUP scans column B for an exact match. ISNA returns TRUE when the lookup fails — meaning the value in A doesn't exist anywhere in B. Filter for TRUE and you've isolated the "missing from B" set. Reverse the columns to find what's only in B.

If you're on Excel 2021 or Microsoft 365, XLOOKUP is the modern replacement: =IF(ISNA(XLOOKUP(A1,B:B,B:B)),"Only in A","In both"). XLOOKUP handles missing values more gracefully, doesn't care which column comes first, and reads much more naturally than VLOOKUP's positional arguments. The how to use xlookup in excel reference covers the full syntax.

One performance note: full-column references (B:B) can be slow on large workbooks. If you know your data only spans 10,000 rows, use B1:B10000 instead — Excel won't waste cycles scanning empty rows.

Method 7: MATCH function — a lighter alternative to VLOOKUP

VLOOKUP and XLOOKUP return values. MATCH returns positions. When all you need is a yes/no answer to "does this value appear in the other column," MATCH is faster and arguably cleaner.

The pattern: =ISNUMBER(MATCH(A1,B:B,0)). MATCH searches column B for an exact match (that's what the trailing 0 means). It returns either the position or #N/A. ISNUMBER converts that into TRUE/FALSE — TRUE when the value exists, FALSE when it doesn't.

Combine it with IF for readability: =IF(ISNUMBER(MATCH(A1,B:B,0)),"Found","Missing"). To find what's only in A, filter the audit column for "Missing". To count values that exist in both columns: =SUMPRODUCT(--ISNUMBER(MATCH(A1:A1000,B:B,0))).

MATCH plays well with INDEX too, forming the classic excel index match combo that experienced users prefer over VLOOKUP for its flexibility. The pair gives you horizontal and vertical lookups, exact and approximate matching, and column ordering that doesn't depend on the lookup value sitting in the leftmost column.

Method 8: Power Query Merge — the heavyweight option

For very large datasets — tens of thousands of rows, or comparisons that need to repeat on fresh data weekly — Power Query is the right tool. It's slower to set up than a formula, but once configured, you click Refresh and the whole comparison re-runs.

Load both columns as separate queries (Data → From Table/Range for each). In the Power Query Editor, pick Home → Merge Queries. Match the columns you want to compare, then crucially, set the Join Kind to Left Anti. That keeps only the rows from the first query that have no match in the second — exactly what "differences" means.

Switch the join to Right Anti for values that appear only in the second query. Use Full Outer if you want everything tagged with which side it came from. The excel power query guide drills into each join type.

The killer feature: refresh-ability. Drop a fresh export into your source range, click Refresh, and Power Query re-applies every transformation in seconds. For recurring monthly reconciliations, the time saved adds up fast — you set it up once and never touch the comparison logic again.

Method 9: Inquire add-in & Spreadsheet Compare 2019+

Hidden inside Microsoft 365 / Office 2019 Professional Plus is a tool most users never discover: Spreadsheet Compare. It's not a function — it's a standalone application that diffs two entire workbooks side-by-side, cell by cell, formula by formula.

You'll find it in your Start menu under Microsoft Office Tools. Open it, point it at two workbooks, and it produces a colour-coded report flagging every difference: values, formulas, formatting, named ranges, even VBA code. For audit work — checking that this month's financial workbook matches last month's structure — it's invaluable.

The companion add-in is Inquire. Enable it via File → Options → Add-ins → COM Add-ins → Inquire. It adds a tab to your ribbon with Workbook Analysis, Worksheet Relationship diagrams, and a Compare Files button that launches Spreadsheet Compare directly on your active workbook.

Two caveats. First, neither tool is available in the consumer Home & Business editions — you need Pro Plus, Office 365 E3/E5, or equivalent enterprise SKUs. Second, both tools work at the workbook level, not the column level, so they're overkill for a quick two-column check. Keep them in your back pocket for the bigger audit jobs.

Method 10: Sort and side-by-side visual comparison

Sometimes the simplest method wins. Sort both columns alphabetically (or numerically). Then visually scan them side by side. Mismatches stick out because the sort order breaks where one list has values the other doesn't.

This works best when the columns have heavy overlap and only a handful of differences. Sort Column A ascending, sort Column B ascending, place them next to each other. Where the two lists drift out of alignment, you've found a difference.

Pair it with conditional formatting and you get an even faster visual: sort both columns, select both ranges, apply the Duplicate Values rule set to Unique. The orphans light up in red while the matching values stay neutral.

It's not glamorous, but for a quick check on a couple of hundred values it beats setting up a formula. Just remember to undo the sort or work on a copy — sorting destroys the original row association, which can wreck the workbook if other columns depend on those rows staying together.

The gotchas that quietly break comparisons

Half the support tickets around column comparison aren't really comparison problems — they're data hygiene problems. Here are the silent killers, ranked by how often they bite people.

Leading and trailing spaces. "Smith " versus "Smith" returns FALSE under every method we've covered. CSV imports, web scrapes, and copy-paste from PDFs are notorious for this. Wrap your references in TRIM: =TRIM(A1)=TRIM(B1). Or, better, clean the source data once with the excel trim function applied across the whole column.

Numbers stored as text. A 5 stored as text fails equality against a 5 stored as a number. The telltale sign is a small green triangle in the cell corner. Force-convert with =VALUE(), or select the column, click the warning indicator, and choose "Convert to Number" from the dropdown.

Hidden characters. CHAR(160) — the non-breaking space — looks identical to a regular space but compares FALSE. =CLEAN(TRIM(A1))=CLEAN(TRIM(B1)) handles both at once. CLEAN strips non-printable characters; TRIM strips regular whitespace.

Date format vs serial number. Dates entered as text strings ("12/01/2024") don't equal dates stored as Excel serial numbers (45292), even when they look identical. Convert both to dates with =DATEVALUE() before comparing, and watch your regional settings — UK and US date orderings will silently mismatch.

Finding rows that appear in only one column

This is a special case worth its own section, because it's the most common real-world question: "Give me everything in list A that's not in list B." It's a set-difference problem, and Excel solves it cleanly with a couple of formulas.

The MATCH approach: =IF(ISNA(MATCH(A1,B:B,0)),A1,""). This returns the value from A only when it's missing from B; otherwise it returns blank. Filter the column for non-blanks and you've isolated your "only in A" set.

The COUNTIF approach: =IF(COUNTIF(B:B,A1)=0,A1,""). Same logic, slightly different mechanic — COUNTIF counts how many times A1 appears in column B. Zero count means it's missing. The countifs function in excel extension handles multi-column matching when you need to compare composite keys (e.g., name + date).

For the reverse — values only in B — just flip the references: =IF(COUNTIF(A:A,B1)=0,B1,""). Run both formulas side by side and you get a complete set-difference report: orphans on the left, orphans on the right, matches handled by the blanks in between.

Modern Excel users on 365 can collapse this into one dynamic array formula: =FILTER(A1:A1000, COUNTIF(B:B,A1:A1000)=0). The result spills into adjacent cells automatically — no dragging required. It's the cleanest expression of the problem we've found.

Which method should you actually use?

Ten methods is a lot. Here's the honest decision tree, based on what kind of comparison you're doing.

Row-by-row, same length, small list (under 500 rows): equals operator or IF formula. You can have a result in twenty seconds.

Row-by-row, want visual instead of calculated: conditional formatting with a custom formula. Especially good for handoff workbooks where the recipient doesn't read formulas.

Case-sensitive comparisons: EXACT function wrapped in IF.

Find values from list A missing in list B (different lengths, unsorted): COUNTIF or MATCH inside IF. Or FILTER on Microsoft 365.

Very large data (10,000+ rows) or recurring comparisons: Power Query with a Left Anti join. Painful to set up the first time, magical after that.

Auditing entire workbooks for differences: Inquire add-in or Spreadsheet Compare standalone tool, available in Pro Plus / E3 / E5 SKUs.

Just need a count of mismatches, not the rows themselves: SUMPRODUCT with the inequality array trick.

You don't need to memorise all ten. You need to recognise which question you're really asking. "Are these rows the same?" is a different problem from "What's missing from this list?" — and the right tool depends entirely on which question is in front of you.

Bringing it all together

Column comparison shows up in nearly every Excel workflow, from monthly reconciliations to QA checks on data exports to merging customer lists from different sources. None of these methods is exotic — they're all built into the version of Excel sitting on your desktop right now. The hard part isn't the formula. It's choosing the right one.

If you walk away with one habit, make it this: before you compare anything, run both columns through TRIM and CLEAN. Half the "mysterious" mismatches in spreadsheet history boil down to a stray non-breaking space hiding at the end of a cell. Five seconds of prep saves five hours of confusion.

The methods scale with the problem. Equals operator for a quick eyeball check. Power Query when you'll do the same comparison every month. EXACT when case matters. Conditional formatting when you need the visual. Keep them all in your toolkit and you'll handle anything a colleague throws at you — including the workbooks where two columns that "should be identical" turn out to disagree on 47 rows for reasons nobody understands until you investigate.