Compare Values Between Two Sheets in Excel

How-to basics · Updated November 18, 2026

To compare cells in the same positions on two Excel sheets, enter =IF(Sheet1!A2=Sheet2!A2,"Match","Different") in a result column and fill it down. If the records may be in a different order, compare them by a unique ID instead; otherwise, a position-by-position check can report a difference even when the same records are present on both sheets.

Choose the right comparison method

The best method depends on what “compare” means for your workbook. If row 2 on one sheet is meant to correspond to row 2 on the other, a direct cell comparison is quick and clear. If rows can be sorted, added, or removed independently, first match each record by a key such as an order number or employee ID. To check whether a value appears anywhere in another list, use a membership check rather than comparing row positions.

Before choosing, consider what should count as equal. You might compare displayed values, underlying values, formulas, text with capitalization, or entire records. A workbook can contain values that look alike but are stored differently—for example, the number 25 and the text “25”—so decide whether those should match for your task.

  • Same row and column: compare corresponding cells directly.
  • Same record, possibly different row: look up a unique key and compare the associated fields.
  • Present anywhere in a list: check whether a value occurs in the other range.
  • Many columns or frequent updates: consider Power Query or a structured reconciliation process.

Compare cells in the same positions

Suppose Sheet1 and Sheet2 each have product codes in column A and quantities in column B, with the first data row in row 2. If the rows line up, enter a comparison formula in an unused column on either sheet. With the values to compare in A2 on each sheet, the formula is:

=IF(Sheet1!A2=Sheet2!A2,"Match","Different")

For example, if both A2 cells contain 418, the result is Match; if Sheet1!A2 contains 418 and Sheet2!A2 contains 421, the result is Different. Excel’s equals comparison is not case-sensitive for text, so “North” and “north” count as equal. If both cells are blank, this formula returns Match. That may be appropriate for a simple grid comparison, but it is worth deciding whether two empty cells should instead be labeled separately.

To compare quantities in column B, use the same pattern with B2 references. Copy the formula down by dragging the fill handle or double-clicking it. The row references adjust automatically, so the next formula compares row 3 on Sheet1 with row 3 on Sheet2. If a sheet name contains spaces, enclose it in single quotation marks, as in 'January Sales'!A2. Sheet names without spaces do not need quotation marks.

Compare records when rows are in a different order

A positional comparison is not a reliable record comparison if someone has sorted one sheet or inserted rows. For example, an order might appear on row 12 in Sheet1 and row 38 in Sheet2. Match using a unique ID, then compare the field you care about. In this example, each sheet has IDs in column A and quantities in column B, and Sheet2’s records occupy rows 2 through 100. Enter this formula on Sheet1 in C2:

=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different"),"ID not found")

If Sheet1 row 2 contains ID 104 with quantity 12, and ID 104 has quantity 10 on Sheet2, the result is Different. If ID 105 has quantity 8 on both sheets, the result is Match. If an ID from Sheet1 is absent from Sheet2, the result is ID not found. Fill the formula down to check the remaining records. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; in older Excel versions, a lookup can be built with INDEX and MATCH.

This formula checks IDs from Sheet1 against Sheet2, but it does not reveal IDs that exist only on Sheet2. To find those, repeat the check in the other direction or create a combined list of IDs from both sheets and test each against both. A unique key matters: if an ID appears more than once in the lookup range, XLOOKUP returns the first matching record, which may conceal duplicates. Check for duplicate keys before treating the results as a one-to-one reconciliation.

Check whether a value appears anywhere in another list

Sometimes row order does not matter and you only need to know whether each item in one column occurs in another. With values to check in Sheet1!A2:A100 and the reference list in Sheet2!A2:A100, enter this formula in an adjacent column on Sheet1:

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Found","Missing")

If A2 contains a code that appears at least once in the Sheet2 range, the result is Found; otherwise it is Missing. This answers an existence question, not a one-to-one matching question. If a code appears three times on one sheet and once on the other, COUNTIF still reports Found. For inventory or transaction reconciliation, where counts and duplicates matter, compare IDs and quantities or count occurrences rather than relying on a simple presence check.

Make the comparison case-sensitive or ignore extra spaces

Excel’s ordinary equals operator does not distinguish uppercase and lowercase letters. For a case-sensitive comparison, with the text to compare in A2 on Sheet1 and Sheet2, use:

=IF(EXACT(Sheet1!A2,Sheet2!A2),"Match","Different")

EXACT returns TRUE only when the text is identical, including capitalization. Thus “AB12” and “ab12” produce Different. This can be useful for codes where letter case is meaningful, but it is often unnecessary for names or ordinary labels.

Hidden spaces are another frequent source of unexpected differences. A cell containing “West” may not equal one containing “West ” with a trailing space. If your goal is to compare text after removing leading and trailing ordinary spaces, use TRIM around each cell. With the corresponding text in A2 on both sheets, use:

=IF(TRIM(Sheet1!A2)=TRIM(Sheet2!A2),"Match","Different")

TRIM also reduces repeated ordinary spaces between words to single spaces. It does not remove every invisible character that can arrive from web pages or external systems; nonbreaking spaces, for example, may need separate cleanup. Keep the original data intact if the exact source text matters, and use a helper column for cleaned comparisons.

Highlight differences visually

A formula result column is easy to filter, while color can make a large sheet easier to scan. A dependable approach is to put the comparison formula in a helper column, then use conditional formatting on that helper result. Select the result cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter Different, and choose a format. You can apply another rule for ID not found. This avoids relying on a conditional-formatting formula that directly references a different worksheet.

To highlight mismatches directly across two sheets, another practical option is to bring or copy the comparison values into one sheet and apply conditional formatting there. For same-position comparisons, a helper formula such as the one above makes the rule’s logic visible and easy to audit. Color should support the result, not replace it: labels such as Match and Different remain understandable when printed in grayscale or viewed by someone who cannot distinguish the chosen colors.

Understand what Excel is comparing

A formula can compare the calculated result in a cell or inspect the formula that produced it; those are different checks. For example, two cells may both display 100, even though one contains the typed number 100 and the other contains a formula that calculates 100. A normal equality comparison treats their values as equal. If you need to compare formulas themselves, use Excel’s formula bar or a formula-inspection approach rather than assuming that a matching value means the formulas match.

Formatting is separate too. Two cells can contain the same number but use different number formats, such as displaying one as 1.0 and another as 100%. A value comparison does not flag that display difference. Conversely, cells that look identical can have different underlying precision; a number may be displayed rounded to two decimal places while retaining more digits. If the business rule allows a tolerance, define it explicitly instead of assuming that the displayed rounding is the stored value.

Errors also deserve attention. A direct comparison involving an error value can return an error rather than Match or Different. If source sheets may contain errors, identify and handle them as a separate data-quality check before interpreting the comparison results. Similarly, distinguish a missing ID from a matched ID whose associated value is blank; both can otherwise be mistaken for an ordinary empty result.

Use Power Query for repeatable comparisons

For recurring comparisons or large tables, Power Query can make the process easier to refresh and document. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Excel Workbook to import workbook tables or sheets as queries. In the Power Query Editor, use Home > Merge Queries to join the two datasets on a key. A full outer join can retain records from both sides, including records with no partner; after expanding the matched columns, inspect nulls and compare the fields of interest.

Power Query is useful when the same reconciliation must be repeated after new data arrives, because the steps can be refreshed rather than rebuilt. It is not automatically the best choice for a one-off check of a few dozen cells: a formula is often quicker and more transparent. Check that the join key has the same data type in both queries, and decide how to handle duplicate keys before interpreting a merge. The Power Query interface and available connectors can differ by Excel platform and build; on Mac, availability and import options may differ from Windows, so verify the commands in the installed version.

Common mistakes and practical safeguards

  • Assuming rows line up: Sorting, inserted rows, or deleted records can make positional results misleading. Match by a stable ID when records can move.
  • Ignoring missing records: A comparison in only one direction cannot identify items found exclusively on the other sheet. Check both sides or use a full outer join.
  • Overlooking duplicates: A lookup may return just the first match. Validate key uniqueness or compare occurrence counts when duplicates are meaningful.
  • Comparing text to numbers: A number stored as text may behave differently from a numeric value in lookups and calculations. Standardize types when appropriate.
  • Treating formatting as data: Standard equality checks compare values, not colors, fonts, or number formats.
  • Trusting a single color: Keep a text result or other accessible indicator so the outcome is clear without color.

For a dependable review, preserve copies of the source sheets, record the comparison rule, and filter the result column for Different and ID not found. Then investigate a few matching records as a sanity check, especially after changing the formula or input ranges. A formula reports the result of the rule you gave it; it cannot decide whether that rule reflects the real-world meaning of the data.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. A formula can compare cells or match records by ID, and conditional formatting can make results easier to spot. For recurring data imports, Power Query can repeat a merge and comparison when refreshed.

How do I compare two sheets when the rows are not in the same order?
Use a unique identifier to look up each record on the other sheet, then compare the associated values. A direct row-by-row formula is suitable only when corresponding records are guaranteed to stay on the same rows.

Why does Excel say two cells are different when they look the same?
Extra spaces, nonprinting characters, different stored data types, or unshown decimal precision can cause a mismatch. Inspect the cell contents and clean or standardize the data according to the comparison you need.

Does a normal Excel comparison distinguish uppercase and lowercase?
No. The equals operator is case-insensitive for text. Use EXACT when capitalization must match precisely.

Can I compare entire worksheets at once?
Yes, but first determine whether you need to compare matching positions, records identified by a key, formulas, or formatting. For large or changing datasets, a structured Power Query merge or a carefully designed comparison table is usually easier to review than checking cells manually.

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.