Compare Numbers in Two Excel Sheets: A Practical Guide

How-to basics · Updated November 21, 2026

To compare values in the same rows of two sheets, put this formula in a spare column: with the values in B2 on sheets named Jan and Feb, enter =IF(Jan!B2=Feb!B2,"Match","Different"). Fill it down to flag equal and unequal entries. If rows are not in the same order, match records using a unique ID instead. First make a copy of the workbook and decide whether you are comparing by row or by record.

What You’ll Need

  • An Excel workbook containing both sheets. This guide is for Microsoft Excel for Microsoft 365 on Windows.
  • A clear comparison basis: corresponding rows, or a shared identifier such as an account number, product code, or invoice ID.
  • A spare column for results, and ideally a backup copy of the workbook before you edit it.
  • If the sheets are separate files, both files should be open while you build formulas, or you can copy the data into one workbook first.

For comparisons by ID, the guide uses XLOOKUP, which is available in Microsoft 365 and Excel 2021 or later. If you use an older Excel version, see the troubleshooting section for an alternative approach.

Step-by-Step

  1. Choose what counts as a match. Decide whether the same position in each sheet represents the same item, or whether you need to find the same item by a key. Row-by-row comparison is suitable when both lists are sorted identically and each row has a counterpart. If one sheet may be sorted differently, use an ID-based comparison. Also decide whether values must be exactly equal or whether a small rounding difference is acceptable. Excel compares the stored values, not just the way they appear on screen.
  2. Check and prepare the sheets. Confirm that both sheets contain the intended number columns and that the values are numeric rather than text. Look for headers, totals, blank rows, and duplicate IDs. Do not include a heading row in a formula range unless you mean to compare the headings. If you are comparing separate workbooks, consider copying the relevant columns into two tabs in one workbook; this makes formulas easier to manage and reduces broken external links.
  3. Compare matching rows with a formula. This is the simplest method when row 2 on Jan corresponds to row 2 on Feb, row 3 corresponds to row 3, and so on. With the values in B2 on Jan and B2 on Feb, enter the formula below in C2 on either sheet or in a results sheet. It returns “Match” when the values are equal and “Different” when they are not.

With values in Jan!B2 and Feb!B2, enter this in C2 of a results column: =IF(Jan!B2=Feb!B2,"Match","Different"). If the two values are 125 and 125, the result is Match; if they are 125 and 127, the result is Different.

  1. Fill the comparison down the list. Select the formula cell and drag the small square at its lower-right corner down to the last data row, or double-click that square when the neighboring data column is continuous. Excel adjusts B2 to B3, B4, and so on. Check the final few rows to make sure the formula reaches every record and does not extend into totals or unrelated data.
  2. Filter or highlight the results. Select the comparison column and choose Data > Filter. Open its filter arrow and show only Different to focus on mismatches. You can also select the result cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then enter Different and select a format. The helper-column approach is usually easier to audit than relying on color alone.
  3. Compare records when rows are in a different order. Use a unique ID to look up the matching value on the second sheet. For example, assume Jan has IDs in A2:A100 and numbers in B2:B100, and Feb has IDs in A2:A100 and numbers in B2:B100. Enter the formula in Jan!C2, then fill it down. XLOOKUP finds Jan’s ID in Feb and returns the corresponding number; the formula then compares that number with Jan’s value.

With IDs in A2:A100 and numbers in B2:B100 on both Jan and Feb, enter this in Jan!C2: =IFNA(IF(B2=XLOOKUP(A2,Feb!$A$2:$A$100,Feb!$B$2:$B$100),"Match","Different"),"Missing ID"). If Jan’s ID is found and both values are 125, the result is Match. If the ID is found but the Feb value is 127, the result is Different. If the ID is absent from Feb, the result is Missing ID. The dollar signs keep the lookup ranges fixed as you fill the formula down.

  1. Show the size and direction of each difference. A status label tells you that values differ, but a subtraction can show how much they differ. This is useful for checking quantities, counts, or other numeric measures. A positive result means the first-sheet value is greater; a negative result means it is smaller. Format this result as a number with enough decimal places to see meaningful differences.

With Jan’s value in B2 and the matching Feb value in C2 on the same results row, enter this in D2: =B2-C2. If B2 is 127 and C2 is 125, the result is 2; if B2 is 123 and C2 is 125, the result is -2. This subtraction assumes that C2 already contains the Feb value matched to the same record.

  1. Check a sample before relying on the results. Pick several rows marked Match, Different, and, for an ID comparison, Missing ID. Compare their source cells manually. Verify the first row, a middle row, and the last row. If the list is important, sort or filter the results and review all mismatches before changing source values. Keep the original data intact so you can trace how each result was produced.

On Excel for Mac, the formulas work the same way, and you can fill them down with the fill handle. The ribbon labels are generally similar, though their placement can vary by release. In Excel for the web, the same formulas and basic filtering are available; some ribbon controls may appear in a different location.

Common Mistakes

  • Assuming row positions identify records. If one list has been sorted or has missing entries, a row-by-row formula can compare unrelated items and still return a plausible answer. Check IDs or other identifying columns first.
  • Comparing numbers stored as text. A value that looks like 125 may be stored as text, especially after importing a CSV or copying from another system. Text and numeric values can behave differently in comparisons. Use the warning indicator or inspect the cell format and source data; changing the display format alone may not convert the underlying value.
  • Including headers or totals. A formula copied over a heading or a summary row can produce confusing results. Start on the first data row and stop at the last actual record.
  • Ignoring duplicate IDs. XLOOKUP returns the first matching ID it finds. If an ID occurs more than once, it may not compare the intended record. Check that the key is unique, or build a genuinely unique key from multiple fields before looking up values.
  • Expecting displayed rounding to determine equality. Two cells can display 1.23 while storing slightly different values, or display different decimals because of formatting. Increase decimal places to inspect the values, and decide whether exact equality or an accepted tolerance is appropriate.
  • Treating blanks as ordinary numbers. A blank cell may compare equal to another blank, while a blank compared with zero may not represent the distinction you intended. Decide whether blank means missing, zero, or not applicable, and handle it deliberately.

Tips

  • Give sheets short, descriptive names, such as Jan and Feb. If a sheet name contains spaces, Excel needs single quotes around it in a formula, for example 'January Data'!B2.
  • Use a clear header such as Comparison or Difference for the helper column. This makes it easier to filter, review, and explain the results to someone else.
  • For a fair comparison, keep the original values unchanged and put formulas in new columns or a separate results sheet. This preserves an audit trail and makes it easier to correct mistakes.
  • When decimals differ only because of expected rounding, agree on the allowed tolerance before comparing. Do not silently round source data: that can conceal a real discrepancy. For a tolerance-based test, create a formula specifically for the agreed threshold and check it against sample values.
  • If you compare the same two sheets regularly, turn each data range into an Excel table with Ctrl+T on Windows. Tables expand as new rows are added and make it easier to keep headers and records organized. On Mac, use Command+T to create a table in many current Excel releases; menu and shortcut availability can vary by version.
  • Save the workbook after you have checked the formula range and sampled the outcomes. If the source lists will be replaced later, keep the comparison formulas separate from the imported data.

Troubleshooting

  • Every row says Different, even though the values look alike: Increase the displayed decimal places, check for text-formatted numbers, and inspect for leading or trailing spaces if the data was imported. Number formatting changes appearance, not the stored value. Convert or clean the source data only after preserving a copy.
  • The ID formula says Missing ID for a record you can see: Check both IDs for extra spaces, different punctuation, or different data types, such as a number on one sheet and text on the other. Confirm that the ID is inside the lookup range; the example range stops at row 100. Extend the range if your data has more rows.
  • The formula displays a formula error: Check that the sheet names are spelled correctly and that each reference points to the intended column. If a sheet name has spaces, wrap it in single quotes. Make sure the formula begins with an equals sign and that quotation marks and parentheses are paired. If Excel rejects commas as argument separators because of regional settings, use the separator expected by your Excel installation; formula examples here use commas.
  • XLOOKUP is not recognized: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016. In an older version, sort and align the lists by ID before doing a row-by-row comparison, or use a compatible lookup method such as INDEX and MATCH. Verify the aligned records rather than assuming the original row order is correct.
  • The result seems wrong when IDs repeat: A lookup by a non-unique ID cannot distinguish separate records with that same ID. Add another identifying field, such as date or line number, to make a unique key, then match using that key. Do not accept a first-match result as proof that all duplicates agree.
  • New rows are not included: Check whether you filled the formula down far enough and whether the lookup ranges cover the new data. If you add records frequently, use Excel tables or update the ranges and confirm that the comparison results extend to the new rows.

Frequently Asked Questions

Can I compare two sheets without a formula?

Yes. If both lists are already aligned, you can inspect corresponding cells manually, but that becomes slow and error-prone for a long list. A helper-column formula makes each result visible and can be filtered to show mismatches.

How do I compare sheets when the rows are in a different order?

Use a shared, unique ID to find the corresponding value rather than comparing the same row number. The XLOOKUP example above does this. If you do not have a unique identifier, first determine a reliable way to pair each record; matching by position alone is unsafe.

Can Excel highlight values that do not match?

Yes. A helper column that returns Different can be filtered or highlighted with Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. For direct cell-by-cell visual comparison, conditional formatting can also use a formula rule, but the helper column is often simpler to maintain and review.

Why do equal-looking numbers return Different?

The cells may contain different underlying values hidden by rounding or number formatting, or one value may be text while the other is numeric. Show more decimal places and check the cell contents and data types before deciding how to standardize them.

Does the comparison update if I change a source value?

Yes. Excel recalculates the formula when a referenced value changes, provided calculation is set to Automatic. If results do not update, check Formulas > Calculation Options > Automatic, and confirm that the formula references the cells you changed.

Related

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.