3 Ways to Compare Two Columns in Excel

Guide · Updated June 2026

Say you have two columns — maybe last week’s inventory list and this week’s — and need to know what changed. Here are three ways to compare them, roughly in order of how much Excel knowledge they require.

Method 1: Conditional formatting

Select both columns, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Choose “Unique” instead of “Duplicate” to highlight items that don’t match. This works well for a quick visual scan but doesn’t separate “only in column A” from “only in column B” — both get flagged the same color.

Method 2: A COUNTIF/MATCH formula

In a helper column next to List A, use:

=IF(COUNTIF(B:B, A2)=0, "Missing from B", "Match")

Fill down, then filter for “Missing from B.” Repeat in reverse (COUNTIF against A) to find what’s missing from A. This gives you a real difference set, but it’s two separate formulas, and things like extra spaces or mismatched casing will silently break the match unless you wrap both sides in TRIM() and LOWER().

Method 3: Paste both columns into a comparison tool

Copy column A into List 1 and column B into List 2in a browser-based list comparison tool. You get all four differences at once — only in A, only in B, shared, and combined — with case-sensitivity and whitespace handling as toggles instead of nested formulas, and nothing leaves your browser.

Which method to use

• One-off visual check on a small range → conditional formatting.
• Need the difference as data you can filter or export → formulas, if you’re comfortable maintaining them.
• Want a clean result you can copy out immediately, with duplicates and case handled for you → paste into a comparison tool.

Worked example

Say column A holds last week’s product SKUs and column B holds this week’s:

Paste column A into List 1 and column B into List 2. The result: Only in List 1 is SKU-1002 and SKU-1004 (removed or discontinued this week). Only in List 2 is SKU-1009 and SKU-1010 (newly added). Intersectionis SKU-1001 and SKU-1003 (unchanged). No COUNTIF, no helper column — the same five results a formula would eventually produce, in one paste.

For the Google Sheets equivalent, see How to Compare Two Lists in Google Sheets.