Excel Column Comparison: Formulas, Methods, and Tips

To compare two Excel columns, use a row-by-row equality test when corresponding cells should match, or check whether each value in one column appears anywhere in the other when order does not matter. With the values to compare in A2 and B2, enter =A2=B2 in C2; if both cells contain “North,” the result is TRUE.

What an Excel column comparison can mean

“Compare columns” describes several different tasks, and choosing the right one matters. A row-by-row comparison checks pairs such as A2 against B2, A3 against B3, and so on. It is useful when two exports are meant to have the same records in the same order. A list-membership comparison asks whether a value from one column occurs anywhere in the other, regardless of row position. That is better for checking customer lists or inventory codes that have been sorted differently.

You may also want to compare text with capitalization treated as significant, identify values present in only one list, count duplicates, or highlight mismatched cells without creating a separate results column. These are different versions of the problem. Before choosing a formula, check whether the lists have the same order, whether duplicates matter, and whether “abc” should be treated as the same value as “ABC.”

Compare corresponding rows with a formula

For a basic row-by-row test, place the result in a third column and fill it down. With the first list in A2:A100 and the second list in B2:B100, enter this in C2:

=IF(A2=B2,"Match","Different")

If A2 and B2 both contain “North,” C2 displays Match; if A2 contains “North” and B2 contains “South,” it displays Different. The simpler TRUE/FALSE test in the opening paragraph is useful when you want a logical result for later calculations. The IF version is easier to scan in a report.

Use the fill handle at the lower-right corner of C2 to copy the formula down the rows. Excel adjusts the row references automatically, so the next row compares A3 with B3. If blanks should not count as a match, add an explicit blank check. With the same layout, enter this in C2:

=IF(AND(A2="",B2=""),"Both blank",IF(A2=B2,"Match","Different"))

This version reports Both blank when both cells are empty, Match when their contents agree, and Different otherwise. That distinction is helpful because a simple equality test returns TRUE for two blank cells, which can make incomplete data look like a successful comparison.

Check whether values appear anywhere in another column

When order is irrelevant, test each value in the first list against the whole second list. With source values in A2:A5 and the lookup list in B2:B5, enter this in C2 and fill it down:

=IF(COUNTIF($B$2:$B$5,A2)>0,"Found","Missing")

Suppose A2 is “P-104” and B2:B5 contains P-104, P-108, P-111, and P-113. C2 returns Found, even if P-104 is not in the same row as A2. If A3 is “P-120” and that code does not occur in B2:B5, the result for that row is Missing. The dollar signs keep the lookup range fixed while the formula is filled down.

COUNTIF reports whether a value occurs at least once; it does not pair duplicate records. If the first list contains P-104 twice and the second contains it once, both source rows will say Found. For a simple count of occurrences instead, with the same ranges enter this in C2:

=COUNTIF($B$2:$B$5,A2)

The result is the number of times A2 appears in the second list. This helps distinguish “present” from “present the same number of times,” although checking duplicate totals across two full lists may require a separate count for each value.

Find values missing from either list

A one-way membership check only tells you which values from the first list are missing from the second. To find items unique to both sides, run the check in both directions: test A against B, then test B against A. The first pass identifies values that appear only in A; the reverse pass identifies values that appear only in B. This is often the clearest way to reconcile two independently maintained lists.

For example, suppose column A contains the old product codes and column B contains the current codes. A result of Missing beside an old code means it is absent from the current list; it does not prove that the code was deleted intentionally. It may have been renamed, entered with an extra space, or stored as a number in one list and text in the other. Treat the formula as a way to flag records for review, not as an explanation of why they differ.

If you want a visible, filtered report, keep the status formula in a helper column and use Excel’s filter controls to show only Missing. This preserves the original data and makes it easier to inspect the source rows before taking action.

Highlight differences without a helper column

Conditional formatting can color mismatches directly. Select the range that contains the paired values, such as A2:B100, and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 and B2:B100 holding corresponding values, use this rule formula:

=$A2<>$B2

Choose a format, such as a fill color, and confirm the rule. The dollar signs lock the column references while leaving the row number relative, so the rule checks each row in turn. Because the selected range includes both columns, Excel highlights both cells in a row when they differ. If you select only A2:A100, it highlights the A-side value instead.

For a list-membership highlight, select A2:A100 and create a rule that checks whether each selected value occurs in B2:B100. With those ranges, use:

=COUNTIF($B$2:$B$100,A2)=0

That highlights values in A that are absent from B. Create a second rule over B2:B100, reversing the ranges, if you also want to highlight values found only in B. Conditional formatting changes appearance, not the underlying values, and it does not automatically explain the cause of a mismatch.

Make the comparison case-sensitive or clean the data

Excel’s ordinary equality comparison and COUNTIF are generally case-insensitive for text. That means “ACME” and “Acme” are treated as equivalent for these checks. If capitalization is meaningful, use the EXACT function. With the two values in A2 and B2, enter this in C2:

=IF(EXACT(A2,B2),"Exact match","Different")

For A2 containing “ACME” and B2 containing “Acme,” the result is Different. EXACT compares text case as well as characters, so it is appropriate for identifiers where capitalization is part of the value. It is usually unnecessary for ordinary names or labels if capitalization is inconsistent by design.

Hidden spaces are another common cause of unexpected mismatches. With an imported value in A2, enter this in B2 to remove leading and trailing ordinary spaces and certain nonprinting characters:

=TRIM(CLEAN(A2))

If A2 contains a leading space followed by “West” and a trailing space, B2 returns “West.” CLEAN removes certain nonprinting characters, while TRIM removes extra ordinary spaces around text and reduces repeated internal spaces to single spaces. CLEAN does not remove every possible Unicode character, including some nonbreaking spaces from web data. If a cleaned comparison still differs, inspect the raw values or use Excel’s Find and Replace or a more targeted cleanup step rather than assuming the strings are identical.

Common problems and how to interpret results

Numbers stored as text: A cell containing the text “104” may behave differently from a numeric 104 in some comparisons and calculations. Check the cells’ number formats and data types, and convert values consistently before relying on the comparison.
Dates that only look alike: Excel stores dates as serial numbers and displays them using formats. Two cells may display the same date but contain different underlying date-time values, such as midnight versus noon. If times should be ignored, decide how to remove or compare the time component consistently.
Unequal list lengths: A row-by-row test can compare a value with a blank when one list ends earlier. A membership check can still find matches, but it will not by itself tell you that the second list has extra entries. Check both directions and review the used ranges.
Duplicate values: A membership result of Found means at least one occurrence exists. It does not confirm that both columns contain the same number of copies. Count occurrences when duplicates are meaningful.
Errors in source cells: An error such as #N/A can affect a direct comparison or a formula that uses the cell. Resolve the source error or decide explicitly how errors should be treated before interpreting the output.

Also check that the formula was filled through the intended rows and that a conditional-formatting rule applies to the correct range. A correct formula with an incorrect range can produce a misleading report just as easily as a wrong formula.

Choosing the right method for the job

Use row-by-row equality when row order represents a meaningful pairing, such as matching monthly totals for the same department. Use COUNTIF-based membership checks when comparing lists that may be sorted differently. Choose EXACT when capitalization matters, and conditional formatting when a visual scan is more useful than a separate status column. For a large reconciliation that must be repeated, Power Query can reduce manual work: on Windows Excel for Microsoft 365, use Data > Get Data > From Table/Range to load each list, then use a merge to match records by a key. Power Query is a data-preparation tool, so review the merge type and key columns carefully before treating its output as a final audit.

For occasional checks, formulas are transparent and easy to audit. For repeated reports, keep a copy of the original data, document which comparison rule you chose, and avoid overwriting source columns with cleaned or sorted values. Excel for the web and Mac provide many of the same formula and conditional-formatting capabilities, but some ribbon labels and Power Query options can vary by platform and release. If a command is missing, check the Excel version and platform rather than assuming the workbook or formula is wrong.

Frequently Asked Questions

How do I compare two columns in Excel and show only the differences?

For values that should match row by row, create a helper column with an IF comparison and filter it for Different. Alternatively, apply conditional formatting to the paired ranges to highlight mismatching rows. If the lists are not in the same order, use a membership check in both directions instead.

Does Excel treat uppercase and lowercase text as different?

Ordinary equality comparisons and COUNTIF generally ignore letter case. Use EXACT when “ABC” and “abc” must count as different values.

How can I compare columns when their order is different?

Check each value in the first column against the full range of the second, rather than comparing cells on the same row. Repeat the check in the opposite direction to identify values missing from either list.

Why do two cells look the same but compare as different?

Common causes include leading or trailing spaces, nonprinting characters, text-versus-number differences, and date-time values with different times. Inspect the underlying values and clean or standardize both columns using the same rules before comparing again.

Can I compare columns without changing the original data?

Yes. Put a formula in a new helper column or use conditional formatting, which changes the display rather than the cell contents. For safety, keep source data intact until you have reviewed the results.

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.