Compare Two Sheets in Excel: Differences and Methods

To compare two sheets in the same Excel workbook, compare corresponding cells with a formula such as =IF(Sheet1!A1=Sheet2!A1,"Same","Different"). This assumes the sheets have the same layout and that A1 on each sheet contains the values to compare; use a key-based lookup instead if the rows may be in a different order.

Choose a comparison method

The right approach depends on what “compare” means for your workbook. A cell-by-cell check is quick when both sheets have matching rows and columns. If records can move, use a shared identifier—such as an invoice number or employee ID—to match records before comparing their details. If you need to find changes repeatedly, Power Query can provide a refreshable process.

Before choosing, decide whether you want to detect differences in calculated values, formulas, layout, or entire records. A formula that compares two cells normally checks their current results, not whether the formulas that produced those results are identical. That distinction matters if, for example, two cells both display 100 but one contains a hard-coded number and the other contains a formula.

Same rows and columns: use a formula grid or highlight the cells that differ.
Rows may be sorted differently: match records by a unique key, then compare the relevant fields.
Recurring, larger comparisons: consider Power Query and refresh the result after updating the source tables.
Formula or formatting differences: inspect those separately; a value comparison alone will not report every kind of change.

Compare cells in the same positions

For two sheets with corresponding data in the same cell addresses, add a third sheet named, for example, Compare. In Compare!A1, enter the formula below, then copy it across and down to cover the area you want to check. The example assumes the source sheets are named Sheet1 and Sheet2 and both contain the corresponding data beginning at A1.

=IF(Sheet1!A1=Sheet2!A1,"Same","Different")

The formula displays “Same” when the two cell values compare as equal and “Different” otherwise. For example, if Sheet1!A1 contains 42 and Sheet2!A1 contains 45, the result is “Different.” The formula checks the values Excel calculates, so if both cells contain formulas that currently return 42, the result is “Same” even if the formulas themselves are not alike.

When a sheet name contains a space or punctuation, enclose it in single quotation marks in the reference. For example, a comparison between cells A1 on sheets named January Data and February Data can use =IF('January Data'!A1='February Data'!A1,"Same","Different"). Excel usually adds the quotation marks automatically when you select a cell on a sheet with a multiword name while composing a formula.

To compare a full rectangular range, fill the formula over a matching-size area on the comparison sheet. On Windows, select the formula cell and use Ctrl+C, select the destination range, and press Ctrl+V; the relative references adjust as the formula is copied. On Mac, use Command+C and Command+V. Check the top-left and bottom-right formulas after filling to confirm that the references moved as intended.

Highlight differences visually

A comparison grid is useful when you want a clear result for each cell, but you can also make the differences stand out with color. One straightforward option is to put the comparison formula in a third sheet and use conditional formatting on its results:

Select the result range containing “Same” and “Different.”
Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Enter Different, choose a format, and confirm.

This colors the comparison results rather than the original source cells, which helps preserve the source sheets. You can also filter a comparison table to show only rows marked as different. If you want to highlight the original data itself, consider copying the relevant data to a review sheet first; conditional formatting rules that compare cells on another worksheet can have reference limitations, so a local comparison grid is often simpler and easier to maintain.

Compare records when row order differs

Position-by-position comparison can give misleading results if one sheet has been sorted, filtered and copied, or had rows inserted. In that situation, match each record by a key that identifies it, then compare the associated values. The key should be present on both sheets and ideally be unique. Examples include a product code, transaction ID, or staff number.

Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100, while Sheet2 has the same fields in columns A and B. In Sheet1!C2, this formula checks whether the amount for the ID in A2 matches the amount found on Sheet2, or reports that the ID is missing. It uses XLOOKUP, available in Microsoft 365 and Excel 2021 or later.

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing",XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)=B2)

With ID 104 and amount 250 in Sheet1, and ID 104 and amount 250 in Sheet2, the formula returns TRUE. If the matching amount on Sheet2 is 275, it returns FALSE; if ID 104 is absent from the listed IDs on Sheet2, it returns “Missing.” Copy the formula down to check the other Sheet1 records. The formula assumes each ID should identify one record. If keys are duplicated, XLOOKUP returns the first match, so investigate duplicates or define a combined key before relying on the result.

Checking in both directions matters. The formula above identifies Sheet1 records that are missing from Sheet2, but it does not find IDs that appear only in Sheet2. Create a corresponding check starting from Sheet2, or use a full outer comparison in Power Query, to find additions and removals on either side.

Understand what a cell comparison does—and misses

The equality operator in the basic formula compares values, not appearance. Cells can look different because of number formats—such as 0.5 displayed as 50% on one sheet and 0.50 on another—while their underlying numeric values are equal. Conversely, two numbers that display identically after rounding may differ internally. If the displayed result is what matters, define a rounding or formatting rule for the comparison rather than assuming the display tells the whole story.

Excel’s ordinary text comparisons are not case-sensitive: “North” and “north” are treated as equal by the simple equality formula. If capitalization is meaningful, use the case-sensitive EXACT function. With the same aligned-cell layout, =IF(EXACT(Sheet1!A1,Sheet2!A1),"Same","Different") reports those two text strings as different. Decide deliberately whether capitalization should count; a case-sensitive rule may flag changes that are unimportant in a particular dataset.

Be cautious with empty cells and zeros. A direct reference comparison can treat a referenced blank in ways that may surprise someone who intends to distinguish “no entry” from a numeric zero. If that distinction is important, test representative blank, zero, and formula-returning-empty-string cases in a copy of the workbook and use an explicit rule for blanks. Also note that an error in either referenced cell can cause a basic comparison formula to return an error rather than “Different”; that is a signal to inspect the source error, not proof that the values match.

Compare formulas, formatting, and sheet structure

A value comparison does not establish that the formulas are identical. For instance, =A1+B1 and =SUM(A1,B1) can produce the same result. If the formula logic itself is under review, inspect the formula bar or compare formula text as a separate task. Excel’s FORMULATEXT function can return a cell’s formula as text when the cell contains a formula, but formula text may differ even when two formulas are logically equivalent. A text match is therefore not a complete test of equivalent calculation logic.

Formatting also requires a separate check. A value grid will not reveal differences in fonts, fills, borders, number formats, data validation, comments, or conditional-formatting rules. For a small range, select corresponding cells and inspect the Home tab and formula bar. For a broad audit, define in advance which properties matter and use a dedicated comparison tool or a carefully reviewed macro rather than assuming a value check covers the workbook’s presentation and behavior.

Sheet dimensions can differ as well. If one source extends to row 500 and the other to row 480, a comparison filled only through row 480 will miss the extra records. Check the used ranges and include rows or columns that exist on only one sheet. A blank-looking cell may also contain a formula returning an empty string, so visible appearance alone is not a reliable way to determine whether the underlying cells are truly empty.

Use Power Query for repeatable comparisons

Power Query is useful when the sheets hold structured tables and you need to repeat the comparison after new data arrives. Convert each source range to an Excel table, select a cell in the first table, and choose Data > From Table/Range. Repeat for the second table. In the Power Query editor, use Home > Merge Queries to join the two queries using their shared key, then choose the appropriate join kind for the question—for example, a full outer join can expose records that occur on only one side.

After merging, expand the columns you need and compare the paired fields in the query. Load the result back into Excel, then refresh it when the source tables change. Power Query is especially helpful for repeatable imports and larger lists, but it requires clean, consistent keys and some familiarity with query steps. A merge is not automatically a judgment that two records are identical: you still need to decide which columns to compare and how to interpret missing or duplicate keys.

Common problems and safe working habits

Many confusing results come from comparing the wrong things: adjacent rows instead of matching IDs, formulas’ results instead of formula logic, or displayed text instead of the underlying values. Before filling a formula across thousands of cells, test a few known matches, a known difference, a missing record, and a blank. Verify that the sheet names and ranges point to the intended data, and check that the comparison covers the full extent of both sheets.

Keep a copy of the original workbook before sorting, removing duplicates, or changing source data.
Use stable, unique keys where records can move; do not rely on row position unless the layout is controlled.
Label the comparison output with the date, source sheet names, and rules used, especially when results will be reviewed later.
Do not treat a TRUE result as proof that an entire record matches if the formula checks only one field.
Protect sensitive workbook data when sharing comparison results; the output may reveal differences in confidential records.

For a one-time check of a modest, aligned range, formulas are usually transparent and easy to audit. For recurring updates, use a query or a documented process so the comparison can be repeated consistently. If you need a formal workbook-difference report that includes formatting or structure, check whether a comparison add-in is available in your Office edition rather than expecting a cell formula to report every workbook change.

Frequently Asked Questions

Can I compare two sheets without adding a third sheet?
You can enter formulas in unused columns or rows on one of the existing sheets, but that changes the sheet being reviewed. A separate comparison sheet keeps source data intact and makes it easier to inspect or share results.

How do I compare two sheets and show only differences?
Create a comparison formula for each aligned cell, then filter a row-level result column for “Different” or use conditional formatting to color those results. For unaligned lists, match records by a key before filtering.

Does Excel have a built-in Compare Sheets button?
Excel for Microsoft 365 does not provide a universal worksheet button that automatically compares every value, formula, and format between two sheets. Formulas, Power Query, and tools available in some Office editions address different comparison needs.

Why does my comparison say two cells are the same when their formulas differ?
The ordinary equality formula compares the cells’ calculated values. To investigate formula differences, inspect or compare formula text separately; different formulas can still be logically equivalent.

Can I compare sheets in different workbooks?
Yes. Excel formulas can refer to cells in another open or linked workbook, and Power Query can import data from separate files. For repeatable comparisons, keep file paths, table names, and key fields consistent.

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.