Compare Two Excel Sheets: Find Differences Step by Step

To compare two Excel sheets, use a cell-by-cell formula when corresponding cells line up, or match records by a unique key when the rows may be in a different order. For a quick visual review, use Spreadsheet Compare if it is available in your Excel edition.

Choose a comparison method

The right approach depends on what “different” means for your task. Two sheets might contain the same records arranged differently, or they might have matching layouts where you need to find changed values. A comparison can also focus on formulas, formatting, missing records, or all of these.

Matching layouts: Compare corresponding cells with a formula or a visual highlight.
Rows in a different order: Match records using a stable identifier, such as an invoice number or product ID.
Workbook-level review: Use Spreadsheet Compare when available to inspect differences between workbooks.
Small, simple sheets: View them side by side, while remembering that visual inspection can miss subtle changes.

Before comparing, save a copy of the files and confirm that each sheet uses the same units, date conventions, and column meanings. A comparison can accurately identify unequal cells while still being misleading if, for example, one sheet records dollars and the other records cents.

Compare cells when the layouts match

If each row and column represents the same thing on both sheets, a helper column can label matching and differing cells. Assume the sheets are named Old and New, and the values to compare are in cell A1 on each sheet. Enter this formula in A1 of a third sheet:

=IF(Old!A1=New!A1,"Same","Different")

If Old!A1 contains 120 and New!A1 contains 125, the result is Different. If both contain 120, it is Same. Copy the formula across and down to cover the full area. If the sheet names contain spaces, enclose each name in apostrophes in the reference, as in a reference to a sheet named Old Data.

This simple comparison checks the values Excel evaluates in the cells; it does not check whether the formulas that produced those values are identical. For example, a cell calculated with a sum and a cell containing the same typed-in number can compare as equal. It also treats text comparisons as case-insensitive in ordinary Excel equality tests, so “North” and “north” can match. Decide whether those distinctions matter before relying on the result.

For a large range, fill the formula efficiently: select the destination range, type the formula, then press Ctrl+Enter on Windows to enter it throughout the selection. On Mac, use Control+Return for the corresponding multi-cell entry. Alternatively, enter the formula once and use the fill handle to copy it. Check that the references adjust as expected; relative references such as A1 move when copied, while references with dollar signs stay fixed.

Match records when rows are in a different order

A cell-by-cell comparison can report many false differences if one sheet has been sorted differently. In that case, compare records by a unique key, such as an employee ID, order number, or SKU. The key should identify exactly one record in each sheet. Names alone are often poor keys because different people can share a name, and a person’s name can be entered differently.

For example, assume the New sheet has IDs in column A and amounts in column B, while the Old sheet also has IDs in column A and amounts in column B. In New!C2, use this formula to find the old amount for the ID in A2 and compare it with the new amount in B2:

=IFERROR(IF(B2=XLOOKUP(A2,Old!A:A,Old!B:B),"Same","Different"),"Not found")

If New!A2 is P104, New!B2 is 125, and Old contains P104 with an amount of 120, the result is Different. If P104 is absent from Old, the result is Not found. Copy the formula down for the remaining New records. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may need a different lookup approach.

XLOOKUP returns the first match it finds. If the key appears more than once, the comparison may not represent every record, so check for duplicates or use a key made from multiple fields. Also note that this example looks for every New record in Old; it does not by itself identify records that exist only in Old. To find those, run a corresponding check in the other direction, using each Old key to look for a match in New.

Use Spreadsheet Compare for workbook reviews

Spreadsheet Compare is a Microsoft utility for examining differences between two workbooks. Depending on the Excel edition and installation, it may be available through the Inquire add-in on Windows. Availability varies; it is associated with certain business and professional editions, rather than being a feature guaranteed in every Excel installation.

To check whether it is available, open File > Options > Add-ins. In the Manage box, select COM Add-ins, choose Go, and look for Inquire. If present, enable it and look for the Inquire tab on the ribbon. The comparison command is typically Inquire > Compare Files. Follow the prompts to select the two workbooks. The results window can help review changes such as values, formulas, and formatting, with the exact options depending on the installed version.

This tool is useful when a reviewer needs a structured report rather than a handful of formulas. It is not the same as comparing two cells inside an ordinary worksheet, and it is not available in Excel for the web or Excel for Mac as the same Inquire workflow. If the add-in is missing, use worksheet formulas or another approved comparison tool instead. For sensitive workbooks, follow your organization’s rules before uploading files to any third-party service.

Review sheets side by side

For a quick visual check, open both sheets or workbooks and use View > View Side by Side on Windows when the command is available. This is handy for checking a short report, but it does not automatically flag every changed cell. Matching scroll positions can help when the rows are in the same order; it is much less reliable when one sheet has been sorted, filtered, or expanded.

Visual review is also vulnerable to display details. A narrow column may hide part of a value, a number format may make different underlying values look alike, and a long sheet may contain changes outside the visible area. Treat side-by-side viewing as a way to inspect context, not as proof that two large sheets are identical. On Mac or in the web app, use the comparison features available in that version or place the sheets in a workbook and use a helper formula.

Make differences easier to see

Once a comparison formula labels results, use filters or formatting to focus attention. For example, filter a helper column to show only “Different” or “Not found.” You can also apply conditional formatting to the helper results: select the result range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter the relevant label. This highlights the comparison output without needing a conditional-formatting formula that reaches directly into another worksheet.

For a matching layout, another practical option is to put the comparison result beside each original row rather than replacing data. That preserves the source values for review and makes it easier to trace a flagged result back to both sheets. If you are comparing thousands of rows, filtering a helper column is usually more manageable than scanning colored cells across the entire workbook.

Common causes of misleading results

A “Different” result is not always evidence that a business value changed. Conversely, “Same” does not prove that every aspect of two cells is identical. Check the comparison rules and the source data when a result is surprising.

Numbers stored as text: A value imported as text may behave differently from a numeric value, even if both appear to show the same digits. Check cell formats and test how the values sort or calculate.
Whitespace and hidden characters: Leading spaces, trailing spaces, nonbreaking spaces, and line breaks can make text differ. Imported data may need careful cleanup before comparison.
Rounding and precision: Values can display as 10.00 while their underlying values differ by a fraction. Increase displayed decimal places or decide on an appropriate rounding rule for the task.
Dates and regional formats: A date may display differently because of formatting or regional settings. Check the actual date values and make sure both sheets use the same interpretation.
Blank cells and errors: Two blank-looking cells may not have the same contents; one may contain a formula returning an empty string. Errors can also pass through a simple comparison formula rather than producing a clear label.
Duplicate or unstable keys: A lookup may find the wrong occurrence when IDs repeat. A row number is not a reliable record key if rows can be inserted, deleted, or sorted.

Formatting deserves a separate check. Two cells can have equal values but different number formats, fonts, fills, or borders. A basic equality formula will not flag those presentation differences. Conversely, identical-looking cells can contain different underlying numbers because a format rounds what is displayed. Be clear whether the job is to compare data, formulas, appearance, or a combination.

Choose a dependable workflow

For a repeatable comparison, preserve the original sheets, document which columns are being matched, and keep the results on a separate sheet or in a separate copy. Confirm that the key is unique, check both directions for missing records, and sample a few flagged and unflagged rows against the source. If the sheets are frequently refreshed, make the steps consistent so that each review uses the same columns and rules.

Before sharing a comparison, consider whether the result contains confidential information. A helper sheet may expose values from both sources, and a report can reveal changes that are not appropriate for every recipient. Keep the source files intact, label the comparison date and input versions, and avoid treating a visual highlight as a complete audit trail.

Frequently Asked Questions

Can Excel automatically compare two sheets?
Yes. A cell-by-cell formula can flag changes when the layouts match, and key-based lookup formulas can compare records in different row orders. Spreadsheet Compare can provide a broader workbook review in supported Windows editions with the Inquire add-in.

How do I find rows that are missing from one sheet?
Match a unique ID from one sheet against the ID column in the other. A missing-match result identifies records absent from the lookup sheet. Repeat the check in the opposite direction to find records that are present only in the other sheet.

Will a comparison formula detect formatting changes?
No. A standard equality formula checks cell values, not font, fill, border, or number-format differences. Use a suitable workbook comparison feature or inspect formatting separately if appearance matters.

Why does Excel say two values are different when they look the same?
Common reasons include hidden spaces, a number stored as text, an unshown decimal difference, or a formula returning an empty string in one cell. Inspect the underlying values and formats rather than relying only on what is displayed.

Can I compare sheets in Excel for the web or on Mac?
You can use worksheet formulas in both, though interface details can differ. The Inquire-based Spreadsheet Compare workflow described here is a Windows feature and is not the same as a built-in web or Mac comparison command.

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.