To compare two Excel lists in one pass, paste the cells as one item per line into the Compare Two Lists tool, choose your case policy, and the tool emits five views — In both, Only in A, Only in B, Union, and Either not both — in that fixed order, with the same arrays feeding the on-screen panels and the copied text. Each non-blank trimmed line is treated as one set member, CRLF and CR line breaks become LF before reading, and the operation follows standard set meanings documented independently by Python's built-in set types reference. The whole comparison runs locally in the browser, with no upload, no log, and no AI involvement, which matters when the lists contain customer emails, allowlists, or inventory identifiers you do not want leaving your machine.
Excel has built-in ways to compare lists — conditional formatting, VLOOKUP, INDEX-MATCH, XLOOKUP — but each one stops at a narrow question. One marks matches with a fill color. Another returns a single lookup value per formula. None of them produces a copy-ready report that lists the union, the common items, the A-only items, the B-only items, and the symmetric difference at the same time. The next sections walk through what each approach gives, what it misses, and the concrete steps for turning two Excel columns into a five-section local report.
What Excel's Built-In Compare Methods Actually Return
Excel offers several ways to compare two lists, and the choice between them usually depends on which single question you need answered.
- Conditional formatting with duplicate rules highlights cells that appear on both lists using a fill color, but it does not produce a copyable list of matches.
- The formula =IF(A2=B2,"Match","Difference") labels each row as a match or a difference when the rows line up, which is useful only when both lists sit in the same row order.
- XLOOKUP returns one result for each lookup formula, so it works as a per-row match check against a key column but not as a list-wide membership view.
- INDEX-MATCH and VLOOKUP are older alternatives that behave the same way for membership checks on a single key column.
- Spreadsheet Compare is a Windows-only utility that highlights changes between two workbook versions rather than membership between two flat lists.
None of those methods answers every question at once: which items exist in both lists, which live only in A, which live only in B, what the union looks like, and what the symmetric difference looks like. For each of those five views you usually need a separate formula column, a separate PivotTable, or a separate Power Query merge. The Compare Two Lists tool produces all five in one pass with one consistent case policy and one copyable report.
Turn Your Excel Columns Into Two Clean Line-Based Lists
Before any comparison runs, each input has to be a list of items, one item per line. Cells in Excel are already arranged that way inside a single column, which makes the tool a natural fit.
- Select the first column of items in Excel, including the header if you have one.
- Press Ctrl+C, then open a plain-text editor such as Notepad and press Ctrl+V.
- Save that file as list-a.txt or paste it straight into the List A textarea.
- Repeat the same steps with the second column into List B.
- If your lists are short, copy each column directly into the corresponding textarea — one cell per line is the rule.
The tool normalizes CRLF and CR line endings to LF before it reads anything, so Windows- or Mac-pasted data lands in the same place. Each line is trimmed of surrounding whitespace, blank lines are discarded, and duplicate keys keep their first displayed spelling and order. If a list contains more than one million UTF-16 code units or more than 50,000 lines, the tool returns an explicit error rather than a partial result, which gives you a clean signal to split the input first.
Generate a Five-Section Report From Two Excel Lists
- Open the Compare Two Lists tool in your browser.
- Paste the first Excel column into List A, one item per line.
- Paste the second Excel column into List B, one item per line.
- Choose whether letter case changes membership — leave it off to treat Apple and apple as the same item, turn it on to treat them as different members.
- Click Compare and inspect the five labeled sections: In both (the intersection), Only in A, Only in B, Union, and Either not both (the symmetric difference).
- Click Copy results to put all five sections onto the clipboard in a fixed order, then paste the report into Excel, a text file, or a ticket.
- Verify the literal-line policy against the destination system — especially if the downstream sheet strips leading zeros, treats text as numbers, or folds case on import.
The same arrays feed the on-screen panels and the copied report, so what you see is exactly what gets written to the clipboard. An empty section shows an em dash so the absence stays visible after pasting. Clipboard success is reported only after the system confirms the copy, which means a manual fallback appears the moment the browser denies clipboard access instead of pretending to succeed.
How the Five Views Map Onto Your Spreadsheet Data
The five sections use standard set-operation names. Union contains every member in either or both lists, ordered by first appearance in A then new members from B. In both contains members common to both lists. A-only and B-only are directional differences — items present in one list but not the other. Either not both is the symmetric difference: members that exist in exactly one of the two lists.
| Section | Set name | Meaning |
|---|---|---|
| In both | Intersection | Items present in List A and List B |
| Only in A | A-only difference | Items in A but not in B |
| Only in B | B-only difference | Items in B but not in A |
| Union | Union | Every item in A, plus new items from B |
| Either not both | Symmetric difference | Items in exactly one of the two lists |
Consider the worked example the tool ships with. Let List A be apple, banana, pear and List B be banana, peach. Under either case policy the common result is banana, A-only is apple and pear, B-only is peach, union is apple, banana, pear, peach, and the symmetric difference is apple, pear, peach. The arithmetic here is the membership check itself, no derived figure is computed from the lists, so every value on the page stays verifiable from the inputs.
Case Policy, Duplicates, and the Literal-Line Rule
Three rules govern every output and they apply consistently across all five sections.
- One physical line is one literal item. Whitespace inside the line is preserved; only the surrounding whitespace is trimmed. The tool does not parse CSV, split on commas, normalize Unicode, remove punctuation, or compare numbers numerically. A row that reads SKU-001 in Excel stays SKU-001 in the report.
- Case sensitivity is a single global choice. With case-insensitive comparison the default, Apple and apple share one membership key and the first displayed spelling wins. With case-sensitive comparison turned on, the two lines count as distinct members and can land in A-only or B-only.
- Duplicates keep their first spelling and order. If apple appears first in List A and APPLE appears later in the same list, case-insensitive comparison collapses them to a single key and the report uses apple.
These rules matter most when the destination system has different normalization rules. Excel, for example, lets a numeric cell display as 0007 while the underlying value is the integer 7. Because the tool only sees the literal text you paste, it has no way to recover the formatting the cell had in the spreadsheet. Review the case policy and the surrounding whitespace before treating any result as authoritative.
Limits, Errors, and What This Tool Won't Do
The tool's contracts are narrow on purpose so the outputs stay predictable.
- One million UTF-16 code units and 50,000 lines per input. Exceeding either limit returns an explicit error with no partial result.
- Empty normalized lists on both sides also return an error rather than five empty sections, which prevents silent confusion.
- Editing either textarea clears the prior result, so a stale report never lingers after a paste.
- No upload, no storage, no AI involvement. Parsing, set operations, rendering, and clipboard writing all run locally. No data leaves the tab.
- No fuzzy matching. The tool does not recognize aliases, equivalent domains, Unicode confusables, numeric ranges, or business identities. It compares exact normalized line keys only.
- No sorting. Output follows the first occurrence order from List A and then new List B members, which is useful for stable diffs and risky if you expected alphabetical order.
For spreadsheet-side guidance on related list work, the Compare Two Lists for Differences From Excel guide walks through a second workflow with overlapping lists, and the Five Views at Once guide focuses on the same five outputs from a different angle. The Compare Two Lists tool itself is the only place where the report is generated.