Compare Two Columns in Excel: Formulas and Methods

Formulas & functions · Updated November 22, 2026

To compare two columns in Excel row by row, enter =A2=B2 in a third column and fill it down; TRUE means the cells match and FALSE means they do not. To compare two lists regardless of row order, use a lookup or count formula instead, because a row-by-row check only compares cells in the same positions.

Choose the comparison that fits your data

“Compare two columns” can mean several different tasks. You might want to check whether each value in column A matches the value beside it in column B, identify differences, find items that appear in both lists, or locate duplicates within one list. The right method depends on whether row order matters and what result you need.

  • Same rows, exact match: compare A2 with B2, then repeat for each row.
  • Same rows, readable labels: return words such as “Match” and “Different” rather than TRUE and FALSE.
  • Lists in different orders: check whether each item from one column appears anywhere in the other.
  • Visual review: use Conditional Formatting to color matching or different cells without adding a result column.
  • Case-sensitive comparison: use a function designed to distinguish uppercase and lowercase letters.

Before comparing, confirm that the columns represent the same kind of information. Comparing customer IDs to names, for example, can produce a technically valid formula but a meaningless conclusion. Also decide how blank cells, extra spaces, and repeated values should be treated.

Compare cells on the same row

Suppose the first values are in A2 and B2. In C2, enter the equality formula below. With A2 containing “North” and B2 containing “North,” it returns TRUE. If B2 contains “South,” it returns FALSE. Drag the fill handle down to apply the comparison to the remaining rows; each copied formula adjusts its row references.

With the values to compare in A2 and B2, use =A2=B2.

If a TRUE/FALSE result is too technical for the people using the sheet, return a label instead. With the same row layout, enter this in C2: =IF(A2=B2,"Match","Different"). For North/North, the result is Match; for North/South, it is Different. You can replace those labels with wording that suits a reconciliation report, such as “OK” and “Check.”

For a quick one-time check, you can also select two cells and look at the formula bar, but that does not scale well to many rows. A helper column is easier to filter, sort, count, or audit later. To fill a formula through a selected range in Excel for Microsoft 365 on Windows, select the formula cell and cells below it, then press Ctrl+D. On Mac, the equivalent Fill Down shortcut is Control+D in Excel; shortcut behavior can depend on the keyboard and system settings.

Handle blank cells deliberately

A simple equality test considers two empty cells equal, so =A2=B2 returns TRUE when both cells are blank. It also returns FALSE if one cell is blank and the other contains a value. That may be exactly right, but it can make a report look as if empty rows are successful matches.

If you want to label two blanks separately, first decide whether a genuinely empty cell and a formula that displays an empty string should count as blank. The following formula treats both as blank because the comparison to an empty string also recognizes a formula result of “”. With data in A2 and B2, enter it in C2; two blank-looking cells return “Both blank,” equal nonblank values return “Match,” and other pairs return “Different.”

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

Another choice is to ignore rows where both cells are empty, for example by filtering them out before reviewing differences. If blanks mean missing information rather than a valid value, you may want to flag a blank in either column instead of calling two blanks a match. State that rule in the worksheet or report so another person does not mistake “both missing” for “verified equal.”

Compare two lists when order differs

A row-by-row formula is not appropriate when the same items may appear in different positions. For example, A2 might contain “Pencil” while “Pencil” appears in B8. The row comparison will say they differ, even though the item exists in both lists. Use a membership check to search the entire second list for each value in the first.

With the first list in A2:A100 and the second list in B2:B100, enter this in C2 and fill down. If A2 contains an item found anywhere in B2:B100, the result is TRUE; otherwise it is FALSE. The formula checks whether each A item is represented in the other list, not whether the lists have the same number of occurrences.

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

To show a useful label, use this alternative in C2 with the same ranges: =IF(COUNTIF($B$2:$B$100,A2)>0,"In both","Only in A"). If A2 is “Pencil” and B7 contains “Pencil,” the result is “In both”; if no B cell contains it, the result is “Only in A.” The dollar signs keep the lookup range fixed as you fill the formula down.

To find items in B that are absent from A, reverse the ranges: place the formula beside the B list and make the COUNTIF range A2:A100, using the corresponding B cell as the criterion. Check both directions if you need a complete reconciliation. One direction alone tells you whether A’s items appear in B; it does not identify items that exist only in B.

COUNTIF is not case-sensitive, and its criteria can interpret wildcard characters such as an asterisk or question mark. That matters if those characters are actual parts of codes or names. For ordinary names and IDs without wildcard characters, it is a convenient method; for exact character-by-character comparisons, use a case-sensitive approach or prepare the data carefully.

Highlight matches and differences without a helper column

Conditional Formatting can color the cells that need attention while leaving the worksheet’s values unchanged. On Windows Excel for Microsoft 365, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the format, confirm the rule, and make sure the rule’s “Applies to” range covers the intended rows. The corresponding Conditional Formatting controls are also available in Excel for Mac, although dialog layout can differ.

To highlight mismatches across A2:B100, select A2:B100 before creating the rule. With A2 as the top-left cell of the selection, use =A2<>B2. The formula returns TRUE for a row whose two values differ, so Excel applies the chosen format to the cells covered by that rule. For matching pairs, use =A2=B2 instead. Keep the references relative: do not add dollar signs to the row numbers, or every selected row may be evaluated against row 2.

If you want to color only the B cells that differ from A, select B2:B100 and create the rule with =B2<>A2. The formula is evaluated relative to the top cell of the selected range. Conditional Formatting is convenient for a visual scan, but it is not a substitute for a result column when you need to filter mismatches, count them, or provide a durable audit trail.

Make the comparison case-sensitive or normalize text

Excel’s ordinary equality comparison and COUNTIF checks do not distinguish uppercase from lowercase letters in the way a case-sensitive text comparison does. If “abc” and “ABC” must count as different, use EXACT. With the values in A2 and B2, enter =EXACT(A2,B2) in C2. It returns TRUE only when the text matches with the same capitalization; “abc” versus “ABC” returns FALSE.

Differences that look invisible can also cause unexpected results. A cell may contain a leading or trailing space, repeated spaces, a nonbreaking space copied from a webpage, or a line break. For example, “Maple” and “Maple ” may look alike on the sheet but are different text strings. Inspect suspicious cells in the formula bar, widen the column, or clean the source data before deciding the values are genuinely different.

TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces; CLEAN removes many nonprinting control characters. A nonbreaking space is a separate character, so SUBSTITUTE with CHAR(160) can replace it first. With source text in A2, this formula returns a cleaned version in another cell: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). Apply the same cleanup to both columns before comparing, and keep the original values if they are needed for traceability. Cleaning can change data, so it should not be done blindly to identifiers where every character matters.

Check data types, dates, IDs, and duplicates

Two cells can display similarly while storing different kinds of values. A numeric value and text that looks like a number may behave differently in comparisons, lookups, and sorting. Dates are stored as serial numbers and merely displayed with date formatting, so the underlying values—not just how they look—matter. If imported data produces unexpected results, check whether both columns use consistent types and formats rather than assuming the displayed text tells the whole story.

Leading zeros are especially important for product codes, postal codes, and account identifiers. “0074” may be a four-character ID, not the number 74. Converting both columns to numbers would remove information and could make distinct IDs appear identical. If leading zeros are meaningful, store the codes consistently as text; if the values are quantities, convert them to numbers consistently. Do not remove punctuation or zeros unless the business rules say they are irrelevant.

Repeated entries raise a separate issue from simple membership. If “Pencil” appears three times in A and once in B, the COUNTIF membership test says each A occurrence is present in B. It does not establish that the lists contain equal quantities of each item. For a count-sensitive reconciliation, compare the number of occurrences of each item in each list. With the lists in A2:A100 and B2:B100, enter the following beside A2 and fill down; it returns TRUE when the item’s occurrence counts agree in both ranges.

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

This count comparison can repeat the same result on duplicate rows, which is expected: it compares the total frequency of that value, not individual occurrences. If duplicates should be removed or investigated, use Excel’s duplicate-highlighting options or a PivotTable to summarize counts, while preserving a copy of the original data.

Common problems and a reliable workflow

Most comparison errors come from applying a correct formula to the wrong comparison problem or to inconsistent source data. Before sharing a result, check the range boundaries, confirm that the formula was filled through the last data row, and make sure the lookup range is anchored when copying formulas. Also watch for headers included in the data range and extra blank rows that produce distracting results.

  1. Define the goal: decide whether order matters, whether capitalization matters, and how blanks and duplicates should count.
  2. Check the source: verify that both columns contain the same kind of information and use consistent data types.
  3. Choose the method: use equality for corresponding rows, a membership formula for differently ordered lists, or Conditional Formatting for a visual review.
  4. Test known examples: try one pair that should match, one that should differ, a blank pair, and a duplicate if duplicates are possible.
  5. Review both directions: for two lists, identify items only in A and items only in B, rather than checking just one side.
  6. Keep the evidence: retain the source values and, when the comparison matters, a helper column showing the result and the rule used.

A common misconception is that “all values from A appear in B” proves the lists are identical. It does not: B may contain additional values, or the same values may appear a different number of times. For a true list reconciliation, check missing items in both directions and decide whether repeated occurrences must also match.

Frequently Asked Questions

How do I compare two columns in Excel and return yes or no?

With the values in A2 and B2, enter =IF(A2=B2,"Yes","No") in C2 and fill down. It returns Yes when the two cells compare equal and No otherwise. Use a different formula if you need case-sensitive matching or special handling for blank cells.

How can I find values that are in one column but not the other?

With the first list in A2:A100 and the second in B2:B100, use =IF(COUNTIF($B$2:$B$100,A2)>0,"In both","Only in A") beside the first list. Reverse the ranges to find items that occur only in B. This basic check is not case-sensitive and does not compare duplicate counts.

Can Excel highlight cells that do not match?

Yes. Select the paired data range, create a Conditional Formatting rule using a formula, and for a selection starting at A2 use =A2<>B2. Choose a fill or font format and confirm that the rule applies to the intended rows. Relative row references let the rule evaluate each row separately.

Why does Excel say two values are different when they look the same?

Hidden spaces, nonprinting characters, different data types, or meaningful leading zeros can explain the difference. Check the formula bar and the source format, and consider cleaning ordinary spaces and control characters in a separate helper column. Avoid converting IDs or removing characters unless you know they are not significant.

Does Excel treat uppercase and lowercase letters as different?

Ordinary equality checks such as =A2=B2 are not case-sensitive. Use =EXACT(A2,B2) when capitalization must match; it returns FALSE for “abc” and “ABC.”

Related

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.