Compare Two Columns in Excel for Differences

To compare two Excel columns row by row, put a comparison formula in a third column and fill it down. With the values to compare in A2:A100 and B2:B100, enter =IF(A2=B2,"Same","Different") in C2; it returns “Same” for equal cells and “Different” when they do not match.

What kind of difference are you looking for?

“Compare two columns” can mean two different tasks. In a row-by-row comparison, A2 is compared with B2, A3 with B3, and so on. This is useful when each row represents the same record in two versions of a file, such as an old and updated address list. It assumes the records are already in the same order.

A list comparison asks whether each value in one column appears anywhere in the other, regardless of row order. This suits two customer lists that have been sorted differently or contain additions and removals. A third task is comparing numeric amounts to find the size of each change, rather than simply whether the cells match.

Choose the comparison before writing a formula. A row-by-row formula can report many differences when the lists contain the same entries in a different order. Conversely, a “found anywhere” check can miss a change in the pairing of values from one row to another. If each row has a shared ID, compare records using that key rather than assuming that their current positions correspond.

Compare cells in the same row with a formula

With the two lists in A2:A100 and B2:B100, enter =IF(A2=B2,"Same","Different") in C2. If A2 contains North and B2 contains North, C2 displays “Same”; if B2 contains South instead, it displays “Different.” Select C2 and fill the formula down through the rows you need to check. The references adjust automatically, so the next row compares A3 with B3.

For a simpler result, with the same layout, enter =A2=B2 in C2. Excel returns TRUE when the values compare equal and FALSE otherwise. TRUE and FALSE can be useful for filtering, counting, or building other formulas; the IF version is often easier to read in a report.

Excel’s ordinary equality comparison is not case-sensitive: “North” and “NORTH” compare as equal. If capitalization itself matters, with the same layout use =IF(EXACT(A2,B2),"Same","Different") in C2. EXACT returns TRUE only when the text matches character for character, including letter case. It does not ignore extra spaces.

If you are comparing numbers and want the amount of change, with old values in A2:A100 and new values in B2:B100, enter =B2-A2 in C2. For example, if A2 is 120 and B2 is 135, the result is 15; a negative result means the new value is lower. Format the result as currency, a percentage, or another suitable number format when appropriate. Subtraction answers a different question from equality: two values can differ while the size and direction of the difference are more useful than a “Different” label.

Highlight mismatched rows with conditional formatting

Conditional formatting marks differences directly in the worksheet, which is convenient when you want to scan the original data rather than read a separate results column. Select A2:B100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range beginning at A2 and the compared columns A and B, use =A2<>B2 as the rule formula, choose a format, and confirm the rule.

The relative row references are important: when the rule is applied to the next row, Excel evaluates A3 against B3. Because the selected range includes both columns, the formatting highlights both cells in a row when they differ. If you want to highlight only the A cells, select A2:A100 instead and use the same formula. Check the “Applies to” range in the Conditional Formatting Rules Manager if the highlights appear on the wrong rows.

Conditional formatting is a visual aid, not a replacement for a comparison result. It does not change the underlying values, and the color may be hard to interpret when a sheet has many other formatting rules. For a review you need to filter or export, keep a status formula as well.

Find values missing from either list

To check whether each entry in one list exists anywhere in the other, use COUNTIF rather than comparing corresponding rows. With the values to check in A2:A100 and the lookup list in B2:B100, enter =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing")) in C2 and fill down. If A2 contains Pear and Pear does not appear in B2:B100, C2 displays “Missing”; if it appears at least once, C2 displays “Found.” The outer IF leaves the result blank when A2 is blank.

To find entries in the other direction, make a second check with the columns reversed. With B2:B100 as the list being checked and A2:A100 as the lookup list, enter =IF(B2="","",IF(COUNTIF($A$2:$A$100,B2)>0,"Found","Missing")) in D2 and fill down. A value missing from A will then be marked in D. Checking only one direction cannot reveal items that exist exclusively in the other list.

COUNTIF treats text matches as case-insensitive, and its criteria support wildcard characters such as an asterisk and a question mark. That behavior can matter if the values themselves contain those characters: a search criterion may be interpreted as a pattern rather than as literal text. For ordinary names and IDs without wildcard symbols, the formula is a straightforward way to locate unmatched entries. For case-sensitive list matching, a different approach is needed; the EXACT function can be combined with array formulas, but the right construction depends on the workbook’s Excel version and data size.

Account for duplicates and repeated values

A basic “Found” result answers whether a value appears at least once, not whether it occurs the same number of times in both lists. Suppose A contains three copies of “Blue” and B contains one. Every Blue in A will be marked Found by COUNTIF, even though the lists have different duplicate counts. This distinction matters for inventory quantities, transaction records, and lists where every occurrence represents a separate item.

To compare how often a value occurs, with both lists in A2:A100 and B2:B100, enter =COUNTIF($A$2:$A$100,A2)=COUNTIF($B$2:$B$100,A2) in C2 and fill down. If Blue occurs three times in each range, the result for a Blue row is TRUE; if it occurs three times in A and once in B, it is FALSE. This checks the total count for that value in the two ranges, so repeated rows can show the same TRUE or FALSE result. Blank rows may also be counted as a value; avoid interpreting blank-row results as meaningful matches, or exclude blanks from the data being compared.

When duplicates are meaningful, do not remove them simply to make the lists look alike. Removing duplicates changes the question from “Do the lists contain the same occurrences?” to “Do they contain the same distinct values?” If you only care about distinct values, work on a copy or use a separate comparison area so the original records remain intact.

Check common causes of false differences

Two cells that look identical can contain different underlying values. Before treating every mismatch as a real change, check these common causes:

Leading or trailing spaces: “Maple” and “Maple ” look similar but are different text strings. Imported data may also contain nonbreaking spaces, which are not always removed by ordinary trimming.
Numbers stored as text: A cell containing the text “123” may not behave like the numeric value 123 in all comparisons or calculations. Check the cell alignment, number format, and whether Excel recognizes the value as a number.
Dates and times: A date may include a time component that is hidden by the displayed format. Two cells can show the same date while their underlying time values differ.
Rounding and floating-point values: Displayed numbers may be rounded even though the stored values have additional decimal places. If a tolerance is appropriate, define it explicitly rather than relying on what the cells appear to show.
Different row order: A valid item on another row will still be flagged by a row-by-row check. Confirm that the records are aligned or compare by a unique identifier.

For ordinary leading and trailing spaces, with text in A2 and B2, a cleaned comparison can use =TRIM(A2)=TRIM(B2). TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. It does not remove every unusual whitespace character, so imported web data may need additional cleaning. Keep the original values if the exact source text is important.

For numbers that should count as equal within a tolerance, decide on the acceptable difference first. For example, with numeric values in A2 and B2 and a permitted absolute difference of 0.01, use =ABS(A2-B2)<=0.01. This returns TRUE when the difference is at most one hundredth. The tolerance should come from the data’s meaning—such as a rounding rule—not from a guess that could conceal a real discrepancy.

Choose the right method for the job

Use a method that matches the structure and purpose of the data:

Same records, same row order: use an IF comparison or a TRUE/FALSE equality formula. Add conditional formatting when visual review is helpful.
Same items, different order: use a COUNTIF-based membership check in both directions to identify items missing from either list.
Duplicates matter: compare occurrence counts, or use a unique record key and inspect the corresponding records. A simple membership test is not enough.
Numeric change matters: subtract old values from new values, and decide whether to show the signed change, absolute change, or percentage change.
Case matters: use EXACT for row-by-row text comparisons instead of ordinary equals, which ignores capitalization.

If the workbook has a shared identifier, such as a product code or account number, use that key to align records before comparing other fields. Sorting both lists by the same key can help, but sorting changes row order and can be risky if only one part of a record is sorted. Sort whole tables, not isolated columns, and retain a backup when the source data must remain unchanged. In Excel for Microsoft 365 on Windows, the formulas and ribbon steps above work in the desktop app; Excel for Mac has equivalent formula and conditional-formatting features, although some ribbon layout details can differ. Excel for the web also supports common comparison formulas and conditional formatting, with some interface differences.

Frequently Asked Questions

How do I compare two columns in Excel and return a match or no match?
With values in A2 and B2, enter =IF(A2=B2,"Match","No match") in C2 and fill it down. The formula compares each row with its counterpart and is not case-sensitive.

How can I compare two columns when the values are in a different order?
Use a membership check such as COUNTIF to test whether each value in the first column appears anywhere in the second, then repeat the check in the opposite direction. This identifies items found in only one list, but a basic membership check does not compare duplicate counts.

Can Excel highlight differences between two columns automatically?
Yes. Select the range to format, create a conditional formatting rule using a formula, and compare the two cells in each row—for example, A2 against B2 when the selection starts at A2. Excel updates the relative row references across the selected range.

Why does Excel say two cells are different when they look the same?
Common reasons include hidden spaces, a number stored as text, a hidden time in a date value, or decimal places that are not displayed. Inspect the underlying values and clean or normalize the data only when doing so matches the comparison you intend.

Does Excel treat uppercase and lowercase text as different?
Not in an ordinary comparison such as A2=B2; it treats text comparisons as case-insensitive. Use EXACT for a case-sensitive row-by-row comparison.

Can I compare columns without changing the original data?
Yes. Put the result formula in an unused column or apply conditional formatting, which changes appearance rather than cell contents. For sorting or cleaning, work on a copy if the original order and values need to be preserved.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.