How to compare two columns in Excel
To compare two columns in Excel row by row, type =A2=B2 in a third column and fill it down: TRUE means the two cells match. If the lists are in a different order, use COUNTIF or XLOOKUP to check whether each value appears anywhere in the other list. This guide shows both methods and how to highlight the differences.
1. Compare row by row
When each row should hold the same value in both columns, a comparison formula is enough. It returns TRUE or FALSE. Note that Excel ignores upper and lower case here, so "Ana" and "ANA" count as equal.
Example: Do the two cells match?
| A | B | C | |
|---|---|---|---|
| 1 | Entered | Expected | Match? |
| 2 | Ana | ANA | TRUE |
The comparison ignores case, so Ana and ANA are equal.
2. Match upper and lower case exactly
When case matters, such as with product codes or passwords, wrap the two cells in EXACT. It returns TRUE only when the text is identical, character for character.
Example: An exact, case-sensitive comparison
| A | B | C | |
|---|---|---|---|
| 1 | Entered | Expected | Exact? |
| 2 | Ana | ANA | FALSE |
Ana and ANA differ in case, so EXACT returns FALSE.
3. Find values that appear in both lists
When the lists are in different orders, a row-by-row test fails. Instead, count how often each value of the first list appears in the second one with COUNTIF. A count above zero means the value is in both lists. Lock the second range with dollar signs so it stays put when you fill the formula down.
Example: In both lists or only in the first?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | List A | List B | Check | |
| 2 | Ben | Dee | In both | |
| 3 | Ana | Ben | ||
| 4 | Cam | Eve | ||
| 5 | Dee |
Ben appears once in List B, so the count is 1 and the row says In both.
4. Look up a value from the other list
To bring something over from the second list, such as a price or a status, use XLOOKUP (or VLOOKUP in older versions). It also tells you which values are missing, because a missing value returns #N/A. Wrap the formula in IFERROR to show a word such as "Missing" instead.
5. Highlight the differences
Select both columns and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then change Duplicate to Unique. Every value that appears in only one place gets a color. For row-by-row differences, select the two columns, press Ctrl+G, click Special and choose Row differences. Excel selects the cells that differ from the first column in each row.
Common mistakes
Hidden spaces. "Ana" and "Ana " with a trailing space look the same but do not match.
Fix: Clean both columns with TRIM before you compare them.
Numbers stored as text. The number 100 and the text "100" are not equal.
Fix: Convert the text to numbers, or check with ISNUMBER which cells are text.
An unlocked range. =COUNTIF(B2:B4,A2) filled down slides the range and starts missing rows.
Fix: Lock it as $B$2:$B$4 before you fill down.
Frequently asked questions
How do I compare two columns and return the differences?
In Microsoft 365, FILTER returns the values of one list that are missing from the other. In older versions, use COUNTIF in a helper column, then filter it.
Can I compare two sheets this way?
Yes. Refer to the other sheet in the formula, for example Sheet2!B2:B100, and the same methods work.
Why does my comparison say FALSE for values that look the same?
Usually a hidden space or a number stored as text. Use TRIM and check the cell format.
Related functions
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- IFIF checks a condition and returns one value if it's true and another if it's false.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
More on comparing columns
The guide gives the row-by-row test, the COUNTIF check and the highlight. This piece adds how to compare lists on two sheets, how to get the values that are in one list but not the other, and how to make a comparison tolerant of messy data.
Lists on two different sheets
The methods are the same when the lists sit on separate sheets. Add the sheet name before the range, followed by an exclamation mark. If the sheet name contains a space, put it in single quotes.
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"In both","Only here")Get the values that are only in one list
Use a helper column to mark each row, and filter it for "Only in A" to list the missing values. In Microsoft 365, one formula returns them directly, and the result updates when the data changes.
=FILTER(A2:A5,COUNTIF(B2:B4,A2:A5)=0)Swap the two ranges to get the reverse list. The result spills into the cells below, so keep them empty.
Make the comparison tolerant
Real lists have extra spaces, inconsistent capitals and numbers stored as text. Clean each column in a helper column first, and compare the cleaned columns. TRIM removes extra spaces, and the other lists in your workbook can be compared the same way. Do the cleaning once in a helper column, then point every comparison at the cleaned column, so a fix in one place carries through. If the lists are long, also sort them first, because it makes the differences much easier to read by eye.
For a lookup that brings a value from one list to the other, see [the XLOOKUP lesson](/learn/xlookup) and [VLOOKUP vs XLOOKUP](/guides/vlookup-vs-xlookup).