Compare Two Excel Sheets for Differences: A Practical Guide

To compare two Excel sheets, use a cell-by-cell formula when the same records occupy the same positions, or match rows by a unique ID when their order may differ. For a quick visual review, open both sheets side by side; for workbook-level comparisons, use Spreadsheet Compare if your Microsoft 365 edition includes it.

Choose a comparison method that fits the data

Start by deciding what “different” means for your task. You might need to find changed values, missing records, altered formulas, formatting changes, or differences between two versions of a whole workbook. These are not all the same comparison.

A direct cell-by-cell check is efficient when both sheets have the same layout: matching headers, matching row order, and values in corresponding cells. If rows have been sorted, inserted, or removed, comparing A2 with A2 can produce misleading results. In that case, match records using a stable identifier such as an account number, product code, or employee ID.

Same layout and row order: compare corresponding cells with a formula.
Same records, different order: look up each record by a unique key.
Need a visual scan: arrange the sheets side by side or highlight formula results.
Need to compare workbook structure or formulas: consider Spreadsheet Compare, if available in your Excel edition.

Compare matching cells with a formula

For sheets in the same workbook, put a comparison formula in a spare sheet or an empty area. With the sheets named Sheet1 and Sheet2 and the cells you want to compare at A1, enter this in the comparison sheet’s A1:

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

Copy the formula across and down over the area you want to check. It returns “Same” when the cell values compare equally and “Different” otherwise. For sheets with names containing spaces, Excel requires apostrophes around the sheet name, such as 'January Data'!A1.

If you prefer a blank result for matching cells, use this version instead. With the same layout—Sheet1!A1 and Sheet2!A1 as the cells being compared—enter:

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

Be aware that a basic equality check compares values, not necessarily the formulas that produced them. Two different formulas can return the same result. Also, Excel comparisons are generally not case-sensitive, so “North” and “NORTH” compare as equal with the basic formula. To make capitalization matter, use the following formula for the same two cells:

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

Blank cells deserve a little care: a direct comparison can treat a blank reference and a numeric zero as equal. To flag a truly empty cell differently from a cell containing zero, use this formula for Sheet1!A1 and Sheet2!A1:

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

This distinguishes a genuinely empty cell from a zero. A formula that returns an empty string is not a genuinely blank cell, so it will not be treated the same as an untouched cell by ISBLANK.

Highlight differences on the worksheet

A comparison formula can be easier to interpret when differences stand out visually. One straightforward approach is to create a helper grid containing the comparison results, then apply conditional formatting to that grid. Select the result range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then enter “Different” and choose a format. This keeps the source sheets unchanged and makes discrepancies easy to scan.

You can also inspect the original sheets side by side. In Excel for Microsoft 365 on Windows, use View > View Side by Side after opening both workbooks; if both sheets are in one workbook, open a second window with View > New Window, then arrange the windows. Synchronous Scrolling, when enabled, helps keep the two views aligned as you move through them. Side-by-side viewing is useful for context, but it does not automatically detect or mark every difference.

Conditional formatting rules applied directly to one worksheet cannot always refer freely to cells on another worksheet. A helper formula grid avoids that limitation and gives you an auditable result. If you do use a rule based on another sheet, check Excel’s reference requirements for conditional formatting rather than assuming an ordinary cross-sheet formula will work in every rule.

Compare records when rows have moved

When records are not in the same row order, compare by a key rather than by position. A unique ID is important: names alone may not be unique, and a product description may change between versions. First check that the key exists in both sheets and that there are no accidental duplicates.

For example, with IDs in Sheet1!A2:A100, values to compare in Sheet1!B2:B100, and corresponding IDs and values in Sheet2!A2:A100 and Sheet2!B2:B100, enter this formula in Sheet1!C2 and fill it down:

=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Same","Changed"),"Missing from Sheet2")

If ID 104 is found in Sheet2 with the same value, the result is “Same”; if it is found with another value, the result is “Changed”; if the lookup cannot find it, the result is “Missing from Sheet2.” XLOOKUP is available in Microsoft 365 and Excel 2021 or later. This example assumes one row per ID: XLOOKUP returns the first matching record, so duplicate IDs need to be resolved or handled separately. To find records that exist only in Sheet2, run a corresponding check in the other direction.

A matching ID does not prove that every field in a record matches. Repeat the comparison for each important field, or build a deliberate record-level check that includes all relevant columns. Avoid joining values casually with a separator if that separator could appear in the data, since different records can then produce the same combined text.

Compare separate workbook files

If you need to compare two versions of a workbook, Microsoft’s Spreadsheet Compare utility can provide a more comprehensive report than a hand-built cell grid. Availability depends on the Excel edition and organization license; it is not present in every Microsoft 365 installation. If the Inquire tab is available, choose Inquire > Compare Files, select the two workbooks, and review the comparison report. The utility can identify workbook differences such as changed cell values and formulas, depending on the comparison settings and supported content.

If Inquire is not visible, check File > Options > Add-ins on Windows. At the bottom, choose COM Add-ins in the Manage box and select Go; if Inquire is listed and permitted, enable it. If it is absent or disabled by your organization, use formulas, a manual review, or another approved comparison tool. Spreadsheet Compare is a Windows utility; the same ribbon path is not available in Excel for the web, and Mac users should check their installation rather than assume the Windows add-in exists.

For an ordinary two-sheet comparison within one workbook, Spreadsheet Compare may be unnecessary. A formula grid is more transparent for a small, specific range, while a workbook comparison tool is useful when you need to examine many sheets or changes beyond a selected block. Keep an unchanged copy of each source file so you can verify findings and recover the original data.

Understand what a reported difference means

A flagged cell is a lead to investigate, not automatically an error. A value may differ because someone corrected a typo, updated a price, refreshed imported data, or changed a formula. Before replacing anything, establish which sheet is authoritative and whether the difference is expected.

Check data types and display formats as well. The number 1 stored as a number and the text “1” may look alike but behave differently in calculations. Dates can also be stored as serial numbers and merely displayed in different date formats. A value comparison may therefore report equality even when the display differs, or report a difference that is only meaningful because of how a value is stored. If formatting itself matters, inspect formatting separately or use a workbook comparison utility that reports it.

For formulas, decide whether you care about the formula text or its calculated result. If two cells show the same total but use different formulas, a value-oriented check may call them equal even though the logic has changed. Conversely, formulas can return different results because of volatile functions, external links, calculation settings, or data that changed after the workbooks were saved. Confirm that both workbooks have current, comparable data before drawing conclusions.

Common problems and a safer workflow

Most misleading comparisons come from a mismatch in assumptions rather than a bad formula. A shifted row, duplicate key, hidden space, inconsistent data type, or overlooked formula returning an empty string can all complicate the result. Imported text may contain leading or trailing spaces that are hard to spot; do not remove them automatically unless you know they are not meaningful.

Save copies first. Keep the original workbooks untouched while investigating.
Confirm the comparison basis. Decide whether you are checking values, formulas, formatting, or record presence.
Check headers and keys. Verify that columns correspond and identifiers are unique where required.
Compare a small sample. Test a few known matching and mismatching cells before filling a formula over a large range.
Review flagged records in context. Check neighboring fields, dates, formulas, and source notes before changing data.
Document changes. Record which version was accepted and why important differences were resolved.

Excel’s comparison formulas recalculate as source data changes, which is helpful during review but means results can change too. If you need a fixed record of the findings, save a dated copy of the comparison workbook or copy the results and use Paste Special > Values in a separate report after reviewing them. Do this only in the report, not over the source data you still need to inspect.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. A formula can compare corresponding cells, while Spreadsheet Compare can review workbook-level changes if your Windows Excel edition includes the utility. Excel does not automatically know whether rows should be matched by position or by an ID, so choose the method that fits the data.

How do I compare two sheets when the rows are in a different order?
Match rows using a unique identifier such as an order number, then use a lookup such as XLOOKUP to bring the corresponding value from the other sheet. A simple A2-to-A2 comparison is not reliable after sorting or inserting rows.

Can I compare two Excel files instead of two sheets?
Yes. Spreadsheet Compare can compare workbook files where the tool is available. Otherwise, open both files and use formulas or a review process that compares the relevant sheets and fields.

Does a cell comparison check formulas or just results?
A basic equality formula compares the values Excel evaluates from the cells. Different formulas that return the same value may therefore appear equal. Inspect the formula bar or use a workbook comparison tool when formula changes matter.

Why does Excel say two cells are the same when one looks blank?
A cell may contain zero, a formula returning an empty string, or a space, even if its display looks empty. Check the formula bar and cell contents; use ISBLANK when you specifically need to distinguish a truly empty cell from a populated one.

Can I compare sheets in Excel for the web or on Mac?
You can use worksheet formulas in Excel for the web and Excel for Mac, though menu layouts and features can differ. The Windows Inquire and Spreadsheet Compare workflow is not available in the same way on every platform, so use formulas or check the tools included with your specific installation.

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.