How to Compare Two Lists in Microsoft Excel

Comparing two lists in Excel is a task you’ll run into often—whether you’re tracking inventory, checking for duplicates, or syncing data from two sources. Luckily, Excel gives you multiple ways to spot differences, find matches, and clean up your data.

Here’s how to do it—fast and clean.

1. Use Conditional Formatting to Highlight Differences

This is the quickest way to visually compare two columns.

Step-by-step:

1. Select the first list (say, Column A).
2. Go to the Home tab → click Conditional Formatting → New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter this formula: =ISERROR(MATCH(A1, B:B, 0)) This highlights values in A that aren’t in B.
5. Set a format (like a red fill), then click OK.

🔁 Want to check B against A? Just flip the formula to:

=ISERROR(MATCH(B1, A:A, 0))

2. Use the MATCH Function for a More Precise Comparison

If you need more than just highlighting—like knowing which values are missing—use MATCH in a helper column.

Example:

1. In Column C, next to your first list, use: =IF(ISNUMBER(MATCH(A1, B:B, 0)), "Match", "No Match")
2. Drag it down.

This gives you a side-by-side verdict for every entry.

3. Use COUNTIF for Duplicate or Unique Values

Another way to flag entries that appear (or don’t) in the other list:

=IF(COUNTIF(B:B, A1) > 0, "Exists", "Missing")

This does the same job as MATCH but is sometimes easier to read, especially for beginners.

4. Combine Lists and Remove Duplicates

Need a quick way to find unique entries across both lists?

Try this:

1. Copy both lists into a single column.
2. Select the new combined column.
3. Go to Data → click Remove Duplicates.

Now you’ve got a clean list of unique items.

5. Use Excel’s New UNIQUE and FILTER Functions (Office 365 or Excel 2021+)

If you’re on a modern version of Excel, life just got easier.

To get values in A not in B:

=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)))

To get values that exist in both:

=FILTER(A:A, ISNUMBER(MATCH(A:A, B:B, 0)))

These are dynamic formulas that automatically resize as your data changes.

Final Tips

• Always check for extra spaces or formatting differences—Excel sees “Item1” and “Item1 ” as different.
• Use TRIM() or CLEAN() to normalize data if needed.
• Save your work before applying bulk formatting or formulas, just in case.

Wrapping Up

Whether you’re doing a quick visual check or building a detailed comparison, Excel gives you the tools to do it. The best method depends on your goal—spotting differences, confirming matches, or generating a clean list. With the tips above, you’ll save time, reduce errors, and keep your data in check.

Need a downloadable Excel template with these examples? Drop a comment and I’ll share one!