How to Compare Two Columns in Excel: Matches and Differences

"Compare two columns" can mean two quite different jobs, and picking the right method depends on which one you've got. Are you checking row by row, like whether the latest stock count matches the previous one on the same line? Or are you checking two separate lists, like which people on your webinar list are already customers, regardless of order?

I'll show you a quick formula for each, a case-sensitive check for when capital letters matter, and a colour-coded method if you'd rather see the answer than read it. None of them change your original data.

Which way of comparing columns in Excel do you need?

Your question	Method	Shows
Do these two cells on the same row match?	=A2=B2	TRUE or FALSE
Do they match, including capitals?	EXACT	TRUE or FALSE
Does this value appear anywhere in the other list?	IF with MATCH	The matching value or a blank
Which values appear in both lists?	Duplicate Values highlight	Coloured cells

Comparing two Excel columns row by row

For lists that line up row for row, a simple equals sign does the job.

1. Click the first empty cell beside your data, say C2.
2. Type =A2=B2 and press Enter.
3. Copy the formula down the column by double-clicking the small square at the bottom-right of C2.

Each row shows TRUE where the two cells match and FALSE where they don't. Filter column C to FALSE and you've got your list of differences.

If you'd rather see words than TRUE and FALSE, wrap it in IF: =IF(A2=B2,"Match","Different").

A case-sensitive check with EXACT

The equals sign treats "SMITH" and "smith" as the same. When capitals matter, for codes or passwords lists, use EXACT. Microsoft says it compares two text strings and returns TRUE only if they're exactly the same, and that it's case-sensitive but ignores formatting differences such as bold.

The formula is =EXACT(A2,B2). Copy it down the same way.

Finding values from one Excel column in another list

When the two lists are in different orders, row-by-row checks fall apart. Microsoft's help gives a formula that looks for each value in column A anywhere in column C, and shows it in column B when it's found.

1. Put your first list in column A and the list you're checking against in column C. Leave column B empty.
2. In B1, type =IF(ISERROR(MATCH(A1,C:C,0)),"",A1).
3. Select B1 down to the last row of your list.
4. On the Home tab, in the Editing group, select Fill, then Down.

Column B now shows every value from column A that also appears in column C, and stays blank where there's no match. Microsoft's version points at a fixed range in column C using an absolute reference. I've used the whole column, C:C, which works the same way and won't miss new rows you add later.

What the parts of that formula do

• MATCH(A1,C:C,0) looks for A1 in column C. The 0 means it must be an exact match.
• ISERROR is TRUE when MATCH can't find it.
• IF shows a blank when it's not found, and the value itself when it's found.

Want the opposite, the values missing from the other list? Swap the last two parts: =IF(ISERROR(MATCH(A1,C:C,0)),A1,"").

Highlighting matches between two Excel columns

If you'd rather see matches in colour, conditional formatting does it without any formulas.

1. Select both columns. Hold Ctrl to select columns that aren't next to each other.
2. Go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
3. Pick a colour and select OK.

Any value that appears in both columns lights up. Be aware it also colours repeats within a single column, so a name listed twice in column A gets coloured even if column C doesn't have it. For a stricter test, a formula rule using COUNTIF is more precise, and my guide on using COUNTIF in Excel shows how that function counts matches.

Making sure your Excel comparison is accurate

Most "wrong" comparisons come down to the data, not the formula:

• Extra spaces: "Acme Ltd" and "Acme Ltd " look the same but won't match.
• Numbers stored as text: an order code saved as text won't match the same code saved as a number.
• Different formats: a date typed as a real date in one list and as text in the other won't match.
• Hidden characters: data pasted from websites or other apps can carry invisible characters.

Clean a handful of cells before you compare a long list, and the results will be far more trustworthy.

Comparing two Excel columns on separate sheets

The lists don't have to sit on the same sheet. In the MATCH formula, click the other sheet's tab when you get to the range part, then select the column there. Excel writes the sheet name into the formula for you, followed by an exclamation mark. Everything else works the same, and it keeps your working list tidy while the reference list stays on its own tab.

Comparing columns for small business tasks

• Check which newsletter subscribers are already customers before sending an offer.
• Match a supplier's delivery note against your order list.
• Spot stock codes that appear in the previous stock count but not the latest one.
• Find which event sign-ups turned up, by comparing the booking list with the check-in list.

If what you need is to join two columns into one, rather than check them against each other, merging two columns in Excel is the job you're after. And if the comparison turns up repeats you want to delete, Microsoft's Remove Duplicates tool on the Data tab will do it, but it deletes permanently, so copy your data first.

Frequently asked questions

How do I compare two columns in Excel for matches?

For rows that line up, type =A2=B2 and copy it down to see TRUE or FALSE. For lists in different orders, use Microsoft's IF and MATCH formula to show which values appear in both.

How do I compare two columns in Excel and highlight differences?

Use =A2=B2 in a helper column and filter for FALSE, or select both columns and use Conditional Formatting, Duplicate Values to colour the values they share.

Is comparing columns in Excel case-sensitive?

The equals sign isn't. Use EXACT, which Microsoft says is case-sensitive and returns TRUE only when the text is exactly the same.

Why does Excel say two matching cells are different?

Usually extra spaces, numbers stored as text, or hidden characters from pasted data. Clean the data and compare again.

Can I compare two columns without a formula?

Yes. Select both columns and use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values to colour values that appear in both.