When you need the overlap
Sometimes the question is not what is missing but what two lists share: which customers are on both mailing lists, which products appear in two suppliers' price files, which attendees registered for both events. In a spreadsheet that usually means a COUNTIF or XLOOKUP formula and a filter. Compare two lists gives you the shared values directly, along with what is unique to each side.
Paste one column into each box
Copy one column of values from each source, without the heading, and paste them into List A and List B. Each line is one value. Blank lines are ignored. The order of lines does not matter.
Example input
List A — newsletter
[email protected]
[email protected]
[email protected]
List B — event sign-ups
[email protected]
[email protected]
[email protected]Decide whether case and spaces matter
Trim whitespace is on by default and ignores spaces at the start and end of a value, which exports often add by accident. Ignore letter case makes [email protected] and [email protected] match. Email addresses are a typical case where you want that; product codes where case is meaningful are not.
With Ignore letter case on, In both lists shows the spelling from List A. Spaces and punctuation inside a value always count, so [email protected] and bob @example.com do not match.
Example result
Ignore letter case off
In both lists: [email protected]
Ignore letter case on
In both lists: [email protected], [email protected]Read the other results too
Only in A and Only in B show the values unique to each list, and All unique values gives the combined list with each value once. With Ignore letter case on, the example gives [email protected] in Only in A and [email protected] in Only in B. To focus on what is missing from one list, follow the missing-items guide.
Each result has Copy and Download buttons, so you can paste the shared values straight back into a spreadsheet.
How duplicates are handled
The comparison works on unique values. If a value appears three times in List A and once in List B, it appears once in In both lists. The note above the results says how many repeated values were excluded. If quantities matter, for example stock counts, use a pivot table instead, because this tool answers whether a value is present, not how many times.
Want to tidy a list before comparing? Text cleanup removes blank lines and duplicates and trims spaces.
Common questions
Can I compare whole rows? No. It compares single values. Copy the key column, such as an email or ID, from each sheet.
Is my data uploaded? No. The lists are compared in your browser.
Which list should be A? For shared values it does not matter, except that A's spelling is shown when case is ignored. Swap lists switches them.