How to Compare Two Columns in Excel: Formulas and Tips

Formulas & functions · Updated November 18, 2026

To compare values on the same row, put the lists in columns A and B, then enter a formula in C2 and fill it down. With the values to compare in A2 and B2, use =IF(A2=B2,"Match","Different"). Excel labels each row according to whether its two cells are equal. If you need to check whether a value appears anywhere in the other column, use COUNTIF instead. These methods help identify matching entries, differences, and missing values without checking every row by eye.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in many earlier Excel versions.
  • The two columns you want to compare, with a clear idea of whether you mean same-row comparison or whether a value exists anywhere in the other list.
  • An empty column for results, if you want a formula-based comparison. Save a copy first if the workbook contains important data or you plan to sort it.

Examples below use headers in row 1 and data starting in row 2. Adjust the column letters and final row number to match your sheet. The formulas use commas between arguments, as in a typical English-language Excel installation; some regional settings require semicolons instead.

Step-by-Step

  1. Choose what “compare” means for your task. If row 2 in the first column should match row 2 in the second, compare corresponding rows. This is useful for checking two versions of a table when their rows are already aligned. If the order differs, or you are checking whether names, IDs, or products from one list appear anywhere in another, use a membership check instead. Selecting the right method matters: comparing A2 with B2 will report a difference when the same value is present elsewhere in column B.
  2. Prepare the columns and a results column. Put the values in A and B, with a heading in row 1 if you have one. Use C for the results and add a heading such as “Comparison.” Check that the two columns contain the kind of data you expect: for example, both should use actual dates, or both should use IDs stored in a consistent way. If one list has extra rows, that is fine for a search across lists, but row-by-row comparisons need a clear decision about how to handle those unmatched rows.
  3. Compare the same-row values with a formula. With the two values on each row in A2 and B2, enter =IF(A2=B2,"Match","Different") in C2. Press Enter. For example, if A2 contains “North” and B2 contains “North,” C2 returns “Match”; if B2 contains “South,” it returns “Different.” Excel’s ordinary equality comparison is not case-sensitive, so “North” and “NORTH” count as a match. Two empty cells also count as equal with this basic formula.
  4. Copy the formula down the data. Select C2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last row, or double-click the fill handle when the neighboring data column is filled continuously. Excel adjusts the references for each row, so the next result checks A3 against B3, then A4 against B4, and so on. Review the last few rows to make sure the fill reached the end of both lists.
  5. Make differences easier to scan with conditional formatting. Select the range you want highlighted, such as A2:B100. On the Home tab, choose Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the first data row at row 2 and the selected range starting at A2, enter =$A2<>$B2 as the rule formula, choose a formatting style, and select OK. The dollar signs keep the comparison in columns A and B while the row number adjusts. This highlights both cells in each row where the values differ. Change 100 to your actual last row.
  6. Check whether each value appears anywhere in the other list. Use this when the lists may be in a different order. With the value to look up in A2 and the comparison list in D2:D100, enter =IF(COUNTIF($D$2:$D$100,A2)>0,"Found","Missing") in B2, then fill down. If A2 contains “P-104” and that exact entry appears anywhere in D2:D100, the result is “Found”; otherwise it is “Missing.” The dollar signs lock the search range as you copy the formula. COUNTIF is not case-sensitive and treats wildcard characters such as * and ? as wildcards, which can affect results when those characters are part of your data.
  7. Use a case-sensitive check when capitalization matters. With the values to compare on the same row in A2 and B2, enter =IF(EXACT(A2,B2),"Match","Different") in C2 and fill down. EXACT returns TRUE only when the text is identical, including letter case, so “North” and “NORTH” return “Different.” It also compares text precisely; unlike the standard equality check, it is designed for case-sensitive text matching. Use it for codes or labels only when capitalization genuinely distinguishes one value from another.
  8. Filter and verify the results. Select the header row and data, then use Data > Filter to add filter arrows if they are not already present. Filter the results column to show only “Different” or “Missing” and inspect those rows. Check a few matches as well, especially if values include leading zeros, spaces, dates, or imported data. When you finish, keep the comparison column for review or copy it and use Home > Paste > Paste Values if you want to replace the formulas with fixed result text.

For a quick visual check without adding a result column, conditional formatting can be enough. For a repeatable audit, a result formula is usually clearer because it tells you which rows matched and can be filtered. You can use both approaches together: the formula records the outcome, while formatting draws attention to the exceptions.

Common Mistakes

  • Using the wrong comparison method: A row-by-row formula assumes corresponding records are on the same rows. If one list is sorted differently, use a COUNTIF membership check or sort and align the records first.
  • Comparing ranges of different lengths without deciding how to treat extras: A formula filled only through row 100 will not report values that exist only in row 101 of the other list. Extend the check through the longest list, and consider checking each list against the other if you need to find missing entries in both directions.
  • Overlooking spaces or inconsistent text: “Item 7” and “Item 7 ” may appear identical on screen but do not compare as the same text. Imported data can also contain nonbreaking spaces that ordinary trimming does not remove.
  • Assuming COUNTIF checks capitalization: It does not. Use EXACT for a case-sensitive, same-row comparison. COUNTIF also interprets * and ? as wildcard characters, so a search term containing those characters may match more than a literal text comparison would.
  • Including headers in the comparison: If the headers are in row 1, start formulas and formatting at row 2. Comparing headings can create an extra result that distracts from the data.
  • Trusting a formula that has not been filled to the bottom: A correct formula in C2 does not compare the rest of the column until you copy it down. Confirm that the result range covers every relevant record.

Tips

  • Use a descriptive results heading, such as “Same row?” or “In list B?”, so another person can tell what the result means.
  • Keep a copy of the original data before cleaning or sorting. Sort both lists together with their related columns; sorting just one column can separate values from their associated records.
  • For a two-way missing-value check, run the membership test in both directions: check list A against list B, then list B against list A. A one-way check only identifies values in the lookup list that are absent from the search list.
  • If the data is an Excel table, formulas may fill automatically down a calculated column. Still verify the table includes all records, particularly after pasting new rows.
  • When you expect numeric values, check whether they are numbers or text. A code such as 0012 may be intentionally stored as text; converting it to the number 12 removes the leading zeros and may change what you consider a match.
  • For large sheets, avoid selecting entire columns in many repeated calculations if a bounded range or Excel table will do. Clear ranges make the comparison easier to audit and can keep a workbook responsive.

Troubleshooting

The formula displays the formula text instead of a result. The cell may be formatted as Text, or Show Formulas may be on. Change the cell format to General, edit the formula (for example, press F2 and then Enter), and check Formulas > Show Formulas. On Windows, Ctrl+` toggles formula display; on Mac, the shortcut may vary by keyboard, so use the ribbon command if needed.

Excel shows an error or rejects the formula. Check that the opening equals sign is present, quotation marks are straight, and parentheses are balanced. If Excel expects semicolons in your regional settings, replace argument-separating commas with semicolons. Also confirm that the formula was not pasted with an extra apostrophe at the beginning.

Values look the same but return Different or Missing. Inspect both cells in the formula bar and check for extra spaces, nonprinting characters, different data types, or dates stored as text. If the issue is ordinary leading or trailing spaces in text, you can compare cleaned helper columns. With the original text in A2, use =TRIM(A2) in a helper cell to remove repeated ordinary spaces and leading or trailing ordinary spaces. TRIM does not remove every nonprinting character or all nonbreaking spaces, so imported data may need additional cleaning. Do not clean IDs automatically if spaces are meaningful in your system.

Conditional formatting highlights the wrong rows. Check that the selected range starts on the same row used in the rule. For a range beginning at A2, the rule should compare row 2, not row 1. Open Home > Conditional Formatting > Manage Rules to inspect the formula and the “Applies to” range. A misplaced dollar sign can lock the row and make every highlighted cell compare against the same record.

COUNTIF reports Found for a value you thought was absent. Check for wildcard characters in the search value, duplicate entries, and cells that only appear blank. COUNTIF is case-insensitive and wildcard-aware. If you need an exact literal search for text that includes * or ?, escape those characters with a tilde in the criterion, or use a different comparison approach appropriate to the data.

The result is stale after editing the data. Excel may be set to manual calculation. Choose Formulas > Calculation Options > Automatic, or recalculate the workbook. Also confirm that the formula’s lookup range includes any rows added after the original formula was written.

Frequently Asked Questions

Can I compare two columns without a formula?
Yes. Select the cells and use Home > Conditional Formatting to highlight differences, or review the lists side by side. A formula is more useful when you need filterable labels, a repeatable check, or a record of which rows differ.

How do I compare columns when the entries are in a different order?
Use a membership check rather than comparing corresponding rows. COUNTIF can test whether each value in the first list appears anywhere in the second. If you need to identify omissions in both lists, perform the check in both directions.

Can Excel compare two columns and return only the differences?
The formulas above label differences; filter the result column to show only “Different” or “Missing.” You can then copy the visible rows to another area if you need a separate exceptions list.

Does Excel treat uppercase and lowercase text as different?
Ordinary equality comparisons and COUNTIF are generally case-insensitive. Use EXACT for a case-sensitive comparison of two cells on the same row.

Why do two dates that look alike not match?
One cell may contain a real Excel date and the other text, or the underlying date values may differ even though their displayed formats look alike. Check the formula bar and cell formats, and compare the underlying values before changing the data.

Do these steps work in Excel for Mac or Excel for the web?
The formulas work in current Excel for Mac and Excel for the web. Ribbon labels and keyboard shortcuts can differ slightly, especially on Mac; use the equivalent Conditional Formatting or calculation commands in the ribbon if a Windows shortcut is unavailable.

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.