Excel Column Compare: Find Differences With Formulas

To compare values on the same rows in Excel, put this formula in C2 when the two columns are A and B, then fill it down: =IF(A2=B2,"Same","Different"). To find values present in one column but absent from another, use a membership check such as COUNTIF; the right method depends on whether row order matters.

Choose the comparison that matches your data

“Compare two columns” can mean several different tasks. You might want to flag rows where two corresponding cells disagree, identify names in one list that do not appear anywhere in another, or highlight changes without adding a results column. These tasks need different approaches. Comparing cells in the same row is not the same as checking whether a value exists somewhere in a second list.

Before choosing a method, ask:

Does position matter? If each row is a paired record, compare the cells on that row.
Does order not matter? If the columns are independent lists, check whether each value occurs anywhere in the other column.
Do you need a visible report? A formula can return labels or missing values; conditional formatting can mark cells in place.
Are duplicates meaningful? A basic membership check reports whether a value occurs at least once, not whether both lists contain the same number of copies.

Compare corresponding rows with a formula

Use a row-by-row comparison when the value in A2 is meant to correspond to the value in B2, the value in A3 to B3, and so on. With the two lists in A2:A100 and B2:B100, enter this in C2 and copy it through C100: =IF(A2=B2,"Same","Different"). The formula returns Same when the two cells match and Different when they do not. For example, if A2 contains North and B2 contains South, C2 returns Different.

For a concise visual result, the comparison can return TRUE or FALSE directly. With the same A2:A100 and B2:B100 layout, enter this in C2 and fill down: =A2=B2. TRUE means the cells compare equal; FALSE means they do not. A label is often easier to scan in a report, while TRUE/FALSE can be convenient for filtering or further calculations.

These comparisons are not case-sensitive: Excel treats “Blue” and “blue” as equal in an ordinary equality test. Blank cells in the same row also compare as equal. If either cell contains an error, the comparison can return that error instead of a label. Decide whether those behaviors make sense for the data before interpreting the results.

Find values missing from the other column

When the lists may be in a different order, check whether each value in A appears anywhere in B. With values in A2:A100 and B2:B100, put this formula in C2 and fill it down: =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)=0,"Only in A","Found in B")). It leaves a blank result for an empty A cell; otherwise it labels the value according to whether COUNTIF finds it in B. If A2 contains Cedar and Cedar does not occur in B2:B100, the result is Only in A.

The dollar signs keep the lookup range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. To check the reverse direction, place the corresponding formula beside column B and switch the searched range and lookup cell. For example, with the same list ranges, enter this in D2 and fill down: =IF(B2="","",IF(COUNTIF($A$2:$A$100,B2)=0,"Only in B","Found in A")). Checking both directions gives a fuller picture: a one-way check only finds values missing from one chosen side.

COUNTIF treats text matching without regard to letter case. Also, the characters * and ? are wildcards in its criteria, so a value containing those characters can be interpreted as a pattern rather than literal text. That is uncommon in ordinary name lists, but it matters when comparing codes or filenames that can contain wildcard characters.

Return a compact list of differences

If you have Microsoft 365 or Excel 2021 or later, FILTER can produce a compact list of values from A that are not found in B, instead of returning a label beside every row. With source values in A2:A100 and B2:B100, enter this in an empty cell such as D2: =FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)=0),"No differences"). If the only unmatched value in A is Cedar, the formula spills Cedar into D2; if there are none, it returns No differences.

The formula excludes blank source cells and tests each nonblank value in A against the full B range. The results occupy cells below the formula automatically, so keep that spill area clear. If Excel shows a #SPILL! error, another value, merged cell, or other obstruction may be blocking the output range. The formula displays unmatched entries from A only; create a second formula with A and B reversed if you need the differences from both lists.

A compact result is useful for review, but it does not preserve every detail of the original rows. If each value belongs to a customer record, transaction, or other row of related information, use a helper column to flag the source row rather than extracting just the value. That keeps the comparison traceable to its surrounding data.

Highlight differences without adding a results column

Conditional formatting can color differing cells while leaving the worksheet data unchanged. To mark row-by-row disagreements, select A2:B100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selection beginning at A2 and paired values in columns A and B, use =$A2<>$B2, choose a format, and confirm the rule. Excel adjusts the row reference for each selected row, highlighting both cells in a pair when they differ.

To highlight values in A that do not occur in B, select A2:A100 and create a formula-based rule. With those ranges, use =AND(A2<>"",COUNTIF($B$2:$B$100,A2)=0). The rule marks nonblank entries in A that have no match in B. To highlight missing values in B as well, create a separate rule for B2:B100 and reverse the ranges. Conditional formatting is a visual aid, not a separate data result: filtering, copying, or exporting the workbook may require you to preserve or inspect the rule separately.

Prepare the columns before trusting the result

A formula can be correct and still expose problems in the source data rather than meaningful differences. Look for extra spaces, inconsistent punctuation, different number formats, and values stored as text in one column but as numbers in the other. For example, a code that looks like 0042 may be stored as text in one cell and as the number 42 in another; leading zeros may be significant identifiers, so do not convert values until you know what they mean.

For suspected extra spaces, inspect a few mismatches directly and consider using TRIM in a separate cleanup column. With a value in A2, this formula removes ordinary leading and trailing spaces and reduces repeated internal spaces: =TRIM(A2). TRIM does not remove every possible invisible character, such as a nonbreaking space copied from some web pages. Cleaning should be done cautiously: a transformed comparison can hide real differences if spaces or punctuation are meaningful.

Dates can also be misleading. A cell displaying 1/2/2026 may contain a date serial, while another cell may contain the text string “1/2/2026”; identical-looking displays do not guarantee identical underlying values. Check the formula bar, number formats, and the source of the data. When a mismatch is unexpected, compare the underlying values and data types before rewriting the comparison formula.

Common comparison mistakes and edge cases

Comparing rows when order does not matter: A2 may be Cedar and B2 may be Maple even though Cedar appears elsewhere in B. Use a membership test for unordered lists.
Checking only one direction: A-to-B identifies items missing from B, but does not reveal items found only in B. Run the reverse check too.
Ignoring duplicates: COUNTIF-based membership answers “is there at least one match?” It does not show that A contains three copies while B contains one. If counts matter, count each value in both columns and compare those counts.
Assuming a match is case-sensitive: Standard equality and COUNTIF comparisons do not distinguish uppercase from lowercase. Use a case-sensitive method if codes or identifiers require that distinction.
Including blanks unintentionally: A blank in A can be treated as matching a blank in B. The example membership formula explicitly returns an empty result for a blank in A; adjust that behavior if blank entries need to be reported.

For a case-sensitive membership check, Excel’s EXACT function can be combined with SUMPRODUCT. With A2 as the value to search for and B2:B100 as the comparison list, enter this formula in C2: =IF(SUMPRODUCT(--EXACT(B$2:B$100,A2))=0,"Only in A","Found in B"). EXACT distinguishes “Blue” from “blue”; the double unary converts its TRUE/FALSE results to numbers for SUMPRODUCT. Add blank handling if the source column contains empty cells, and test the formula on representative values before using it for an important audit.

Which method should you use?

For paired records, a row-by-row IF formula or conditional formatting is usually clearest because it shows exactly which corresponding rows disagree. For two independently sorted lists, COUNTIF-based membership checks are straightforward and work in older Excel versions as well as current Microsoft 365. Use FILTER when you want a short, automatically updating list of unmatched values and your Excel version supports dynamic arrays.

Think about how the result will be used. A formula column is easy to filter and retain in a workbook. Conditional formatting is quick for a visual scan but can be less obvious to someone who opens the sheet later. A FILTER result is concise, though it omits row context and requires room to spill. If duplicates or quantities matter, compare occurrence counts rather than relying on a simple found/not-found test.

Frequently Asked Questions

How do I compare two Excel columns and highlight differences?
Select the paired range, such as A2:B100, and create a formula-based conditional formatting rule using =$A2<>$B2. The formula compares each row’s two cells and highlights rows where they differ. For unordered lists, use a COUNTIF-based rule instead.

How do I find names in one column that are not in another?
Put a COUNTIF membership formula beside the first list, searching the full range of the second list. Label results with no match, then repeat the check in the opposite direction if you need to find values unique to both lists.

Can Excel compare two columns when the order is different?
Yes. Use COUNTIF or another lookup-based membership test to search the entire second column for each value in the first. A direct comparison such as A2=B2 only checks cells on the same row and can report differences when the same values are merely arranged differently.

Why does Excel say two values are different when they look the same?
Common causes include leading or trailing spaces, invisible characters, text-versus-number differences, and dates stored in different ways. Inspect the formula bar and source data, then clean a copy of the values if appropriate. Do not remove characters that are meaningful in your identifiers.

Does COUNTIF compare uppercase and lowercase as different?
No. COUNTIF is not case-sensitive, so uppercase and lowercase versions generally count as a match. For case-sensitive checks, use EXACT as part of a formula such as the SUMPRODUCT example above.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.