Compare Two Excel Sheets and Find Differences

To compare two Excel sheets cell by cell, put a formula on a third sheet such as =IF(Sheet1!A1=Sheet2!A1,"Same","Different"), then fill it across and down; use Power Query or a key-based lookup when rows may be in a different order. The right method depends on whether you need to spot changed cells, changed records, or differences between whole workbooks.

Choose the comparison method

First decide what “different” means for your task. If the two sheets have the same layout and corresponding entries remain in the same cells, a comparison grid is usually fastest. If records may have moved, compare using a unique identifier such as an order number or employee ID. For larger tables or repeatable imports, Power Query can match and report records without relying on row positions.

Same layout, same row order: use a cell-by-cell formula or conditional formatting.
Same records, possibly different order: match on a key column, then compare the other fields.
Large or recurring data sets: use Power Query to merge tables and inspect unmatched or changed records.
Two separate workbook files: use formulas that reference both files, Power Query, or the Spreadsheet Compare tool if it is available in your Excel installation.

Before comparing, make sure you are comparing like with like. A sheet with a title row at the top and another without one is offset, so a direct cell comparison will report many false differences. Confirm that headings, column order, units, and date conventions match.

Compare matching cells with a formula

For a simple example, assume the sheets are named Sheet1 and Sheet2, and their data occupies A1:D100 in the same layout. Create a third sheet called Compare. With the first result in Compare!A1 and the cells to compare at Sheet1!A1 and Sheet2!A1, enter:

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

The formula returns “Same” when the two referenced values compare equal and “Different” when they do not. For example, if Sheet1!A1 contains North and Sheet2!A1 contains South, the result is “Different.” If both cells contain 125, the result is “Same.” Select the result cell and drag its fill handle across four columns and down 100 rows; Excel adjusts the references for each position. If a sheet name contains spaces, surround it with apostrophes, as in =IF('January Data'!A1='February Data'!A1,"Same","Different").

This comparison tests the values Excel evaluates, not whether the underlying formulas are identical. Two cells containing different formulas can both return 10 and be marked “Same.” Also, the ordinary equals comparison does not distinguish text by letter case: “West” and “WEST” compare as equal. Decide whether those distinctions matter before trusting the result.

For a visual result that shows the value from the first sheet only when it differs, use a second formula. With the comparison cells still at Sheet1!A1 and Sheet2!A1, enter this in Compare!A1:

=IF(Sheet1!A1=Sheet2!A1,"",Sheet1!A1&" | "&Sheet2!A1)

If the first cell says 125 and the second says 130, the result is “125 | 130”; if they match, the result is blank. The ampersands join the values with a separator. This is useful for review, but blank results can be ambiguous if a genuine difference involves empty cells or values that display as blank.

Check for missing rows and changed records

Cell-by-cell formulas assume each record is on the same row in both sheets. If rows were sorted or inserted, they can flag differences even when the underlying records are present in both places. In that case, match each record by a stable key first. The key should identify one record uniquely; names alone often do not, because two people can share a name or a name can be entered differently.

For a simple presence check, suppose Sheet1 has unique IDs in A2:A100 and Sheet2 has IDs in A2:A100. In a helper column on Sheet1, with the ID being checked in A2, use:

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

If A2 contains ID 1042 and that ID occurs in Sheet2’s listed range, the result is “Found”; if it does not occur, the result is “Missing from Sheet2.” Fill the formula down to check the other IDs. Repeat in the opposite direction to find records that exist only in Sheet2. COUNTIF can return a misleading presence result when IDs are duplicated, so check that the key is unique or use a method that reveals duplicates.

To compare a field after finding a matching ID, you can use XLOOKUP in Microsoft 365 or Excel 2021 and later. For example, assume Sheet1 IDs are in A2:A100, amounts are in B2:B100, and Sheet2 IDs and amounts are in A2:A100 and B2:B100. With the ID in Sheet1!A2 and its amount in Sheet1!B2, enter this helper formula on Sheet1:

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

If ID 1042 has an amount of 75 in both sheets, it returns “Same”; if the amount on Sheet2 is 80, it returns “Different.” If the ID is not found, the formula returns “Missing ID.” This assumes IDs are unique and that the amount is stored as a comparable number in each sheet. XLOOKUP is not available in every older Excel version; for older versions, use a compatible lookup approach or Power Query rather than assuming the function will work.

Highlight differences with conditional formatting

Conditional formatting makes changes easier to scan when the sheets share a fixed layout. One straightforward approach is to create the comparison grid first, then highlight its “Different” results. Select the result range on Compare, for example A1:D100, and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different, choose a format, and confirm. This colors the comparison results without changing either source sheet.

You can also compare values directly in a helper range and format its results, but a separate comparison sheet is often clearer: it preserves the source data and leaves an auditable record of which positions were checked. If you want to color the original cells, use conditional formatting rules that reference an appropriately arranged comparison range, and test the rule on a few known matches and mismatches before applying it widely.

Formatting differences are not always data differences. One cell might be formatted as currency and another as a plain number while both contain the value 25. Conversely, two dates may look the same but contain different underlying time values. A formula comparison evaluates cell values, not font color, borders, number formats, or other formatting. If formatting itself matters, inspect it separately or use a workbook comparison tool that reports formatting changes.

Use Power Query for larger tables

Power Query is useful when tables are large, arrive repeatedly, or may be sorted differently. In Excel for Microsoft 365 on Windows, convert each data range to a table with Insert > Table, give the tables clear names, and load them into Power Query using Data > Get Data. The exact import choices depend on whether the tables are in the current workbook or separate files. In the Power Query Editor, use Home > Merge Queries, choose the two tables, select the matching key column in each, and choose a join type.

A full outer join includes rows found on either side, so it can expose records missing from one table. Expand the columns you need from the merged result, then compare corresponding fields and filter for unequal values or nulls that indicate a missing match. Check that both key columns use compatible data types, and handle duplicate keys deliberately: repeated keys can produce multiple joined combinations rather than one clean match. When the source data changes, refresh the query to update the comparison.

Power Query is not a live cell-by-cell formula grid. It produces a transformed result when loaded or refreshed, which is helpful for repeatable audits but may be less convenient for a quick one-off check. The available menus can vary slightly by Excel build and data source. Excel for Mac includes Power Query features, but availability and supported connectors differ from Windows; if a particular import or merge option is missing, use formulas or check the features available in that Mac version.

Compare separate workbooks

If the sheets are in different files, formulas can still refer to both workbooks, but both files should be accessible and the references can become harder to maintain. A practical alternative is to import the tables into Power Query and merge them by a key. When comparing just a small, fixed range, open both workbooks and build a comparison sheet with references to each source; verify the links before distributing the result, especially if files will be moved or renamed.

Some Windows installations also provide Spreadsheet Compare, an Inquire feature that can report differences between workbook files. Availability depends on the Excel edition, license, and whether the feature is installed or enabled. If present, check File > Options > Add-ins, choose COM Add-ins in the Manage box, and select Go to see whether Inquire is available. Its Compare Files command is intended for workbook-level inspection and can report more than cell values, including certain formula and formatting changes. Do not assume every Microsoft 365 installation has it.

Common causes of false differences

When a comparison flags many unexpected cells, check the data before changing formulas. Common causes include an extra header row, hidden spaces, numbers stored as text, dates with different underlying values, and missing records that shifted subsequent rows. A value that displays as 1,000 may be stored differently from a text entry that looks identical. Likewise, a displayed date may conceal a different time component.

Confirm the two ranges start and end on the intended rows and columns.
Check that identifiers are unique and consistently typed as text or numbers.
Inspect a few flagged cells in the formula bar to see their actual contents.
Decide whether capitalization, spaces, formatting, or formula differences matter for the task.
Keep the original sheets unchanged and save a copy before cleaning or restructuring data.

Do not treat an empty-looking result as proof that two source cells are identical. A formula that returns an empty string is not necessarily the same as a truly empty cell in every comparison context, and a formula output can hide an underlying error or different calculation. Review unusual cases directly, particularly before using comparison results for financial, compliance, or operational decisions.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. For matching layouts, a formula grid can compare corresponding cells, and conditional formatting can make results easier to scan. Power Query or Spreadsheet Compare may be more suitable when records are reordered, tables are large, or workbook-level changes matter.

How do I find only the cells that changed?
Create a comparison grid with an IF formula that returns “Different” for mismatches, then filter the results or apply conditional formatting to highlight them. Keep in mind that this finds positional differences, so it is reliable only when corresponding records stay in the same positions.

Can I compare sheets when the rows are in a different order?
Yes. Match records using a unique ID rather than comparing the same row numbers. COUNTIF can identify IDs that are missing, while XLOOKUP or Power Query can retrieve and compare related fields.

Does a cell comparison check formatting or formulas?
A normal equality formula compares evaluated values; it does not report formatting changes or tell you whether two different formulas produced the same result. Inspect formulas and formatting separately, or use an available workbook comparison feature when those differences are important.

Why does Excel say two values are different when they look the same?
The cells may contain hidden spaces, a number stored as text, different date or time values, or other underlying content that is not visible in the current formatting. Compare the formula-bar contents and confirm the data types before deciding which value to correct.

Choose a result you can verify

For a quick check of two identically arranged sheets, a formula grid is transparent and easy to review. For reordered records, use a unique key; for recurring, larger comparisons, use a Power Query merge. Whichever approach you choose, test it against a few deliberately known examples, check for missing and duplicate keys, and preserve the original data so that every reported difference can be traced back to its source.

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.