How to compare two columns in Excel

To compare two columns in Excel row by row, put this formula in an empty column next to them and fill it down:

=IF(B2=C2, "Same", "Different")

Every row now says whether the value in column B matches the value in column C. The example in this guide is a price list: SKU in column A, the January price in B and the February price in C. Two prices changed.

This works when the two columns describe the same thing on the same row. If you want to know which values of one column appear anywhere in the other (two lists of emails, for example), that is a different question: see how to compare two lists in Excel.

1. Same row, same value: one formula

=B2=C2 on its own returns TRUE or FALSE. Wrapping it in IF gives words that are easier to filter on. A few useful variants:

• Only mark the differences: =IF(B2<>C2, "Different", ""). Then filter column D on “Different”.
• Letter case matters: = treats acme and ACME as equal. =IF(EXACT(B2, C2), "Same", "Different") does not.
• Numbers that may differ by rounding: =IF(ABS(B2-C2)<0.005, "Same", "Different") treats 12.50 and 12.499 as the same price.
• How much it changed: =C2-B2 for the difference, or =C2/B2-1 formatted as a percentage.

Count the result with =COUNTIF(D2:D7, "Different") so you know how many rows to check before you scroll.

2. Highlight the cells that differ

If you would rather see the differences than add a column, colour them with conditional formatting:

1. Select the cells to colour, here B2:C7.
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Enter =$B2<>$C2, click Format, pick a fill and press OK.

The $ before the column letters keeps the rule on columns B and C, and the missing $ before the row number lets it move down row by row. The colour updates when a value changes.

A one-off check without a rule: select B2:C7 starting in B2 and press Ctrl+\, or go to Home → Find & Select → Go To Special → Row differences. Excel selects the cells in each row that differ from the cell in column B. Fill them with a colour while they are selected.

3. Two sheets, rows in a different order

Row by row stops working as soon as the rows are not in the same order. That is usual for two exports: the February sheet below has the same products sorted differently, one new product (A-106) and one that is gone (A-105).

Instead of comparing by position, look up each SKU on the other sheet and bring its price next to the January price. In C2 on the January sheet:

=XLOOKUP(A2, February!A:A, February!B:B, "missing")

Then compare as before, and say when a SKU is not there at all:

=IF(C2="missing", "Not in February", IF(B2=C2, "Same", "Different"))

XLOOKUP is in Microsoft 365 and Excel 2021 or later. In older versions use =IFERROR(VLOOKUP(A2, February!A:B, 2, FALSE), "missing"). Both return the first match, so if a SKU appears twice on the February sheet you only see one of its prices.

The lookup only goes one way: it finds what is missing in February, not what is new there. To find A-106, do the same on the February sheet and look up its SKUs in January. If the sheets have more than one column to compare, the next sections are quicker.

4. Why equal-looking values come out “Different”

When the formula says “Different” but you see the same value, the cells usually hold something other than what they show:

• A number stored as text (often with a small green triangle) is never equal to a number. Convert it with Data → Text to Columns → Finish, or compare both as numbers: =VALUE(B2)=VALUE(C2).
• A space at the end, or a non-breaking space copied from a web page. Compare TRIM(SUBSTITUTE(B2, CHAR(160), " ")) instead of B2. When XLOOKUP returns #N/A for a value you can see, the causes are the same: why VLOOKUP returns #N/A.
• A date in one column and text that looks like a date in the other, common after importing a CSV. Turn the text into a date with Text to Columns (choose the date order in step 3).
• Letter case goes the other way: = says acme and ACME are the same. Use EXACT if they must not be.

5. Many columns or many rows: let a tool pair the rows

The lookup in section 3 is one formula per column, and with tens of thousands of rows whole-column lookups get slow. If both sheets come from an export, there is a shorter way:

1. Save each sheet as CSV (File → Save As → CSV UTF-8).
2. Drop the old file and the new file into Calamo's free compare tool. It pairs the rows by the ID column, here sku, so the order does not matter.
3. You get added rows, removed rows and every changed cell with its old and new value, for all columns at once.

The files are read in your browser and not uploaded. To try it with the example from this guide, download prices-january.csv and prices-february.csv.

Comparing whole sheets with many columns? See how to compare two Excel sheets for differences and how to find added, removed and changed rows.

Questions

Michal PijákData engineer and the maker of Calamo. These guides come from comparing exports at work: every formula and command is checked against the linked documentation. Found a mistake? hello@calamo.io