How to Compare Two Columns in Excel
Comparing two columns is one of the most common Excel chores — checking which customers from this year's list weren't in last year's, which SKUs in inventory are missing from a supplier's catalogue, which emails are on the new mailing list but not the old one. Almost every analyst has hit it, and almost every analyst has done it the slow way at least once.
The slow Excel way: VLOOKUP / XLOOKUP / MATCH
The classic recipe: drop a VLOOKUP or XLOOKUP in a helper column to flag matches, then filter the column for #N/A to find missing items, then repeat the entire process in the other direction. Each pass:
- Write the lookup formula in a new column.
- Drag-fill it down the entire data range.
- Wait for Excel to recalculate (slow on 10k+ rows).
- Apply a filter, manually copy out the non-matching rows.
- Repeat the whole thing for List B against List A.
For one-shot comparison it's 5 minutes of fiddling. For a workflow you do every week, it's an hour a month that adds up. And VLOOKUP on a 50,000-row list is genuinely slow — Excel recalculates the whole sheet on every change.
The real silent killer though is false mismatches from invisible characters. If either list was pasted from a web page, a PDF, an SAP export, or even Word, the values often contain non-breaking spaces (U+00A0) or zero-width characters that look identical to plain text. The VLOOKUP returns #N/A for what should be an obvious match, you spend twenty minutes wondering whether your data is wrong, and the real answer is just one rogue character.
What this tool does instead
Paste your two lists, click Compare, get three answers in well under a second:
- Unique to A— items in List A that don't appear in List B (the classic “new customers” case).
- Unique to B— items in List B that don't appear in List A (the “churned customers” case).
- Matches— items present in both (the “retained” case).
The comparison uses a hash-set algorithm (O(n) lookup) instead of the nested-loop O(n²) that VLOOKUP effectively does — comparing two 50,000-item lists takes milliseconds in your browser, while the same operation in Excel can chew on it for tens of seconds.
And the Clean on Pastefeature handles the invisible-character problem by default: every line gets trimmed and stripped of non-breaking spaces, zero-width chars, and control codes as it comes in. The vast majority of “why don't these obvious matches match” cases are silently solved before you even see the result.
Pro Tip
If your lists have merged cells or ghost rows in the source file, clean them first with our Spreadsheet Cleaner. Merged cells leave most data rows blank when you copy a column, so half your “Unique to A” will just be empty space. Run the file through the cleaner first to get a single value per row, then paste those values here.
Case sensitivity — when to flip the toggle
By default the comparison is case-insensitive — North, NORTH, and north are treated as the same value. This is the right default for names, locations, statuses, and most categorical data where casing variations are unintentional.
Turn case-sensitivity onwhen you're comparing data where case is meaningful: API keys, file names on Linux servers, hashed values, codes where abc123 is a different thing from ABC123.
What about duplicates within a single list?
Each list is deduplicated internally before comparison — if List A contains North three times, it counts as one entry. This matches what you usually want when comparing membership: “is this region in both lists?”, not “how many times does this region appear in each list?”
If you need duplicate counts within a single list, use our Duplicate Remover — it shows duplicate counts per group and removes them in one click.