Excel Compare Two Sheets: Tools, Formulas, and Tips

Formulas & functions · Updated November 18, 2026

To compare two Excel sheets, use Spreadsheet Compare if it is available in your Microsoft 365 or Office edition: open the Inquire tab and choose Compare Files. For a quick check, put a comparison formula beside the data, or use conditional formatting to highlight changed cells; the best method depends on whether rows line up and whether you need an audit of the whole workbooks.

What an Excel comparison tool can do

“Compare two sheets” can mean several different tasks. You might want to spot changed values in corresponding cells, find records present in one sheet but missing from another, compare formulas and formatting, or create a reusable report of differences. Excel does not offer one universal Compare Sheets button in every edition, so it helps to choose a method based on the question you need answered.

For a controlled workbook review, Microsoft’s Spreadsheet Compare utility is the closest thing to a dedicated comparison tool. It can examine two workbooks and report differences in areas such as cell values and formulas. Depending on the available options and workbook contents, it can also compare other workbook details. For a smaller, repeatable task, formulas are often more flexible: they let you specify what counts as a match and leave the results in the workbook.

  • Use Spreadsheet Compare when you need a visual review of two workbook versions.
  • Use formulas when you need a result column, a list of missing records, or a repeatable check.
  • Use conditional formatting when the sheets have the same layout and you mainly want differences to stand out.

Find and open Spreadsheet Compare

In Excel for Microsoft 365 on Windows, look for the Inquire tab on the ribbon. If it is present, choose Inquire > Compare Files and select the two workbooks. Spreadsheet Compare opens a comparison report; use its options to focus on relevant differences and review the results rather than assuming every reported change matters.

If you do not see Inquire, availability depends on the Excel edition and installed components. You can check whether the add-in is available by going to File > Options > Add-ins. At the bottom, set Manage to COM Add-ins, select Go, and see whether Inquire is listed. If it is available, enable it and check the ribbon again. If it is not listed, your edition may not include it, or your organization may manage which add-ins are installed. The standalone Spreadsheet Compare app may also be available from the Windows Start menu in supported Office installations.

Spreadsheet Compare is not a standard feature in Excel for the web, and the Inquire comparison workflow is not available in Excel for Mac in the same way as on Windows. On those platforms, use formulas, helper columns, or a supported desktop Windows installation. If you share a workbook with colleagues, remember that a formula-based comparison can be viewed in the web app even when the dedicated utility cannot be opened there.

Compare cells in the same positions with a formula

A direct formula works when the sheets have the same structure: row 2 on one sheet refers to the same record as row 2 on the other, column A matches column A, and so on. Put the formula in a third sheet or in an unused column, then fill it across and down. If the worksheet names contain spaces, surround each name with apostrophes, as in 'January Data'!A2.

With corresponding values in Sheet1!A2 and Sheet2!A2, enter this in a comparison cell:

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

If both cells contain 125, the result is Match; if one contains 125 and the other 130, the result is Different. Copy the formula to check other pairs of cells. For a whole rectangular range, compare each relevant column, or build a separate result for each row. A row-level check can combine several fields, but separate checks make it easier to see exactly which field differs.

Be aware of what is being compared. A formula reference returns a cell’s calculated value, not a full explanation of how it was produced. A blank cell and a cell containing zero can also be easy to confuse in simple comparisons. If blank-versus-zero distinctions matter, explicitly check whether cells are truly empty with ISBLANK, and test the formula against your workbook’s cases before relying on the result. Text with different capitalization is normally treated as equal by the equals operator; use a case-sensitive comparison only if that distinction is important.

Match records when rows are in a different order

When sheets contain the same records in different orders, comparing A2 with A2 is not meaningful. Match using a stable key, such as an invoice number, product code, or employee ID, then compare the corresponding fields. Keys should be unique if each record is supposed to appear once; duplicate keys can make a lookup return an unintended match.

For example, assume Sheet1 has unique IDs in A2:A100 and amounts in B2:B100. Sheet2 has the same kind of IDs in A2:A100 and amounts in B2:B100. Put this formula beside the Sheet1 record to check its amount against the amount found for the same ID:

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

If Sheet1 row 2 has ID 1042 and amount 75, and the matching ID in Sheet2 has amount 75, the formula returns Match. If that matching amount is 80, it returns Different. If ID 1042 is not in the lookup range, it returns Missing. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions can use alternatives such as INDEX and MATCH, or a carefully designed lookup formula.

This formula checks the amount only. Add checks for other fields, or create a combined status that tests several comparisons. Also check for duplicate IDs before trusting the report: XLOOKUP returns the first matching item, so it will not flag every duplicate automatically. To identify IDs missing from the other sheet separately, use a count check.

With a record ID in Sheet1!A2 and the comparison IDs in Sheet2!A2:A100, use:

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

The result is Missing if A2 does not occur in the specified Sheet2 range; otherwise it is Found. Fill down to check the IDs in Sheet1. Reverse the sheet references to find IDs that appear only in Sheet2.

Highlight differences with conditional formatting

Conditional formatting is useful when you want a visual scan rather than a separate report. It works best when the two ranges have matching dimensions and values in matching positions. One practical approach is to create a comparison area containing formulas, then color the cells whose results say Different. Select that result range and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter Different, and choose a format.

Another option is to show the values from one sheet next to the other and use a formula-based conditional formatting rule on a same-sheet helper range. This avoids the limitations of direct cross-sheet references in conditional-formatting rules. Keep the original data visible or label each side clearly: a colored cell is a signal to investigate, not an explanation of why the values differ. For large or frequently refreshed datasets, a formula result column is easier to filter and document than color alone.

Choose the right method for your task

Before comparing, decide what constitutes a difference. A change in a displayed value may come from formatting, rounding, or a changed formula. A changed formula may still return the same value today. A missing row is a different problem from a changed cell, and it requires matching records by key rather than comparing fixed addresses.

  • Same layout, occasional review: use direct formulas or a helper comparison sheet.
  • Different row order: match by a unique ID, then compare the fields that matter.
  • Workbook version review: use Spreadsheet Compare where available, especially if formulas or workbook structure need attention.
  • Simple visual scan: use conditional formatting on a comparison range.
  • Recurring imports or larger datasets: consider Power Query to load and transform both sources, then merge them using a key. Power Query is available in many Microsoft 365 desktop editions, though features and menus can vary by platform and edition.

Do not compare only totals if record-level accuracy matters. Two sheets can have the same total while containing offsetting errors—for example, one amount is 10 too high and another is 10 too low. A useful review may therefore include both record-level comparisons and a summary of how many rows match, differ, or are missing.

Common problems and checks before trusting results

Most misleading comparison results come from mismatched data rather than a broken Excel feature. Extra spaces, inconsistent number formats, dates stored as text, duplicate IDs, and hidden rows can all complicate a review. A value displayed as 00125 might be stored as text on one sheet and as the number 125 on the other; those may not compare as expected. Decide whether the difference is meaningful before normalizing the data.

  • Confirm that both ranges cover the same intended records and include the final row.
  • Check whether IDs are unique and whether leading zeros or spaces are significant.
  • Decide how to treat blank cells, zeroes, errors, and formulas that return an empty string.
  • Compare underlying values separately from formatting if both kinds of change matter.
  • Filter the result column for Different or Missing, then inspect a sample against the source cells.

For dates and decimal values, establish an acceptable tolerance if tiny calculation differences should count as a match. Do not silently round source data just to make a comparison pass; instead, document the rule in the comparison logic. If a workbook is protected, linked to external data, or contains formulas that have not recalculated, refresh or recalculate it as appropriate before drawing conclusions.

Frequently Asked Questions

Does Excel have a built-in Compare Sheets button?
Not in every edition. Some Windows installations include Spreadsheet Compare through the Inquire add-in and provide Inquire > Compare Files. Otherwise, use formulas, conditional formatting, or another comparison workflow.

Can Excel compare two sheets with different row orders?
Yes. Match records using a stable key such as an order ID, then compare the fields for the matching records. A position-by-position comparison is only reliable when corresponding records are already on the same rows.

Can I compare sheets in Excel for the web or on a Mac?
You can use worksheet formulas and supported formatting features, but the Windows Spreadsheet Compare workflow is not available in the web app and is not provided on Mac in the same way. A Windows desktop installation with the required add-in is one option for that feature.

Will a comparison formula detect formatting changes?
No. A standard formula compares cell contents or calculated values, not all formatting details. Use Spreadsheet Compare where available or inspect formatting separately if font, number format, borders, or other presentation changes matter.

What is the safest way to compare two versions of a workbook?
Keep untouched copies of both files, compare the versions with Spreadsheet Compare if available, and review the reported differences. For important data, confirm key changes in the source cells and save the comparison results separately.

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.