To compare two Excel sheets, use a cell-by-cell formula when the same records occupy the same cells, or match records by a unique key when rows may be in a different order. With corresponding data in A1:Z500 on worksheets named Sheet1 and Sheet2, enter =IF(Sheet1!A1=Sheet2!A1,"Same","Different") in a third worksheet and fill it across and down. For reordered records, compare by an ID or use Power Query.
Choose a comparison method
The right approach depends mainly on whether the worksheets have the same shape and whether corresponding records are in the same positions. A quick visual review can be enough for two small tables, but a formula or query is more dependable when there are many rows or changes must be documented.
- Same layout and row order: compare corresponding cells with a formula. This is usually the fastest method for two versions of the same template.
- Same records, different row order: match on a stable identifier, such as an invoice number, employee ID, or product code.
- Large tables or repeated updates: use Power Query to combine the lists and identify unmatched or changed records.
- Two separate workbook files: if available in your Excel edition, Spreadsheet Compare can show workbook-level differences. Side-by-side viewing is useful for inspection, but does not itself identify every changed value.
Before starting, decide what “different” means for your task. You might care about changed displayed values, missing records, formulas, formatting, or all of these. Excel’s ordinary equality formulas compare calculated cell values, not every aspect of the cells.
Prepare the sheets before comparing
Make a copy of both workbooks before changing or cleaning data. Give the worksheets clear names, and check that headers identify the same fields. If one sheet has an extra title row, a different starting column, or a subtotal mixed into the records, a position-by-position comparison can report many misleading differences.
Check for common sources of false mismatches:
- Numbers stored as text on one sheet and as numeric values on the other.
- Extra spaces, inconsistent punctuation, or different spellings in text fields.
- Dates that look alike but are stored differently, or values with different underlying precision.
- Duplicate IDs, blank rows, hidden rows, or records sorted differently.
- Formulas that display the same result but are written differently.
Keep an untouched copy of the original data. If you remove spaces, standardize dates, or convert text to numbers to make a fair comparison, do that in helper columns or copies so the original values remain available for checking.
Compare cells in the same positions
When both sheets use the same layout and row order, a comparison worksheet makes the results easy to inspect. With values in A1:Z500 on Sheet1 and Sheet2, enter this formula in A1 of a third sheet:
=IF(Sheet1!A1=Sheet2!A1,"Same","Different")
Fill the formula right and down to cover the data area. Each result refers to the matching cell on the two source sheets. For example, if Sheet1!B2 contains 18 and Sheet2!B2 contains 20, the comparison cell returns Different. If both contain 18, it returns Same. The formula compares values as Excel evaluates them; it does not prove that the original formulas or formatting match.
If worksheet names contain spaces, put single quotation marks around each name in the reference, such as 'January data'!A1. You can also replace the words Same and Different with shorter markers, but descriptive labels are easier to filter and review.
Equality comparisons in Excel are generally not case-sensitive: text such as “North” and “north” is treated as equal by the equals operator. If capitalization matters, with the same A1 cell layout on Sheet1 and Sheet2, use this case-sensitive alternative in the comparison sheet:
=IF(EXACT(Sheet1!A1,Sheet2!A1),"Same","Different")
To inspect the results, turn on filters for the comparison area and filter for Different. For a large grid, you can also use Find to search for the word Different. Keep the source sheets unchanged so each reported difference can be traced back to its original cells.
Match records by a key when rows move
A position-based comparison is unreliable if a record can appear on a different row in each sheet. Instead, use a unique key to find the corresponding record first. An order number, customer ID, or SKU is often suitable, provided it is present and unique in both lists. Names alone are risky keys because different people can share a name and one person’s name can be entered in different ways.
For example, suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100, while Sheet2 has the same fields in columns A and B but may have the IDs in a different order. With the Sheet1 record’s ID in A2 and its amount in B2, enter this formula in C2 on Sheet1, then fill down:
=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Same","Different"),"Missing in Sheet2")
XLOOKUP searches Sheet2 for the ID from A2 and returns the amount from the matching row. If the amounts match, the formula says Same; if the ID is found but the amount differs, it says Different; if the ID is not found, it says Missing in Sheet2. For instance, if ID 102 has amount 18 on Sheet1 but 20 on Sheet2, the result is Different regardless of which row contains ID 102 on the second sheet.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The formula assumes the key identifies only one record. If an ID appears more than once, XLOOKUP returns the first match, which may conceal additional differences. Check for duplicate keys before relying on the result, and repeat the comparison in the opposite direction to find records that exist only on Sheet2.
Highlight differences without losing the data
A comparison formula can create a readable report, while conditional formatting can make the report easier to scan. Select the formula results, then choose Home > Conditional Formatting > Highlight Cells Rules and set a rule for cells containing the text Different. You can choose a fill color to make those results stand out. This highlights the comparison results rather than altering the source values.
For a small number of differences, the comparison sheet is often enough. For a clearer audit trail, keep the key, the value from each workbook, and a status column together. That lets a reviewer see which record changed and what each version contained, instead of seeing only a colored cell. Avoid sorting just one source range: sort complete tables so IDs remain attached to the correct data.
Compare separate workbook files
If the two versions are in separate files, you can open both and compare them using the same formula or Power Query methods. Another option on some Windows installations is Spreadsheet Compare, associated with the Inquire add-in. When the Inquire tab and Compare Files command are available, use Inquire > Compare Files, select the two workbooks, and review the reported differences.
Availability depends on the Office edition and installation; the command may not appear in every Microsoft 365 setup. On Windows, you can check File > Options > Add-ins and look for Inquire under COM Add-ins. Spreadsheet Compare is a Windows tool, so Mac users should use formulas, Power Query where available, or another compatible workflow instead. Regardless of method, review workbook structure and formulas as well as cell values if those are important to the comparison.
Use Power Query for larger or recurring comparisons
Power Query is useful when you need to repeat a comparison or work with substantial tables. In Excel for Microsoft 365 on Windows, import each workbook using Data > Get Data > From File > From Workbook. In the Power Query Editor, make sure each table has consistent column names and data types. Then use Home > Merge Queries, choose the shared key column in each table, and select a full outer join to retain records found on either side.
After expanding the merged table, compare the fields of interest and identify rows with no match or values that differ. A full outer join is helpful because it can expose records missing from either source, rather than only showing matches. Power Query steps can be refreshed when the source files change, which is valuable for monthly reconciliations. It takes more setup than a simple formula, and the query must use the correct key and columns; it does not decide by itself which differences matter. Excel for Mac has Power Query capabilities that vary by version, so menu availability and supported connections may differ.
Common problems and how to interpret results
A “Different” result is a signal to investigate, not necessarily proof of a meaningful change. A value may differ because a number is stored as text, or because a calculated result changed due to an upstream formula. Conversely, a Same result does not mean the formulas, formatting, comments, or validation rules are identical. Choose a comparison method that tests the property you actually care about.
Floating-point calculations can also produce tiny differences that are not obvious in the displayed number. If the task is to compare rounded amounts, decide on the appropriate precision and compare values rounded to that precision in a separate, clearly labeled helper column. Do not round values automatically if the underlying precision is significant to your work.
Other useful checks include:
- Compare record counts and key counts before interpreting results.
- Check missing IDs in both directions, not only from the first sheet to the second.
- Confirm that dates, currency, and units represent the same things in both files.
- Review several reported matches and differences manually to confirm the setup is correct.
- Keep a dated copy of the comparison output if you need to explain what was checked later.
One common misconception is that sorting both sheets the same way always makes a cell-by-cell comparison safe. Sorting helps only when the sort field is consistent and unique; ties, missing keys, and duplicates can still pair unrelated rows. Matching on a verified identifier is more reliable.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Excel does not have one universal command that identifies every kind of difference in every workbook. Use formulas for cell values, Power Query for table records, or Spreadsheet Compare if the tool is available in your Windows Office installation.
How do I compare two sheets and find missing rows?
Match records using a unique key, then check each sheet against the other. A one-way lookup finds records missing from the second sheet; reversing the check finds records missing from the first. Power Query with a full outer join can show unmatched records from both sides.
Can I compare sheets when the rows are in a different order?
Yes. Do not compare cells by row number in that case. Use a unique ID to look up each record, or merge the tables in Power Query using that ID.
Why does Excel show a difference when the cells look the same?
The underlying values may differ even if formatting makes them look alike. Common causes include text-versus-number storage, extra spaces, hidden decimal places, or different date values. Inspect the formula bar and cell formats, and compare cleaned helper values only if that matches your requirements.
Can I compare formulas as well as results?
A basic equality formula compares calculated values, not formula text. To check formulas, inspect the formulas directly or use a workbook comparison tool that reports formula changes, if available. Decide whether a changed formula matters even when its current result is unchanged.
Related
- Group Rows in Excel by Column Value: 3 Methods
- Goal Seek in Excel: Find the Input for a Target
- Gold Data in Excel: Build a PivotTable
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Digital Signature in WordIn Word for Microsoft 365 on Windows, add a visible signing area with Insert > Text > Signature…
- Excel Page Break Dotted Lines: How to Turn Them OffTo hide page-break dotted lines in Excel for Microsoft 365 on Windows, select the affected worksheet, then go…
- Excel Table Design: Create and Format a TableTo create a table in Excel, place your data in a simple grid with one header row, select…
- How to Redo in Excel With the Correct Keyboard ShortcutTo redo an action in Excel for Microsoft 365 on Windows, press Ctrl+Y. On a Mac, press Command+Y.…
- Excel Repeatedly Not Responding? Causes and FixesIf Excel repeatedly shows “Not Responding,” first give it a short time to finish, especially if a large…
- How to Lock Specific Cells in Google Sheets for EditingTo lock specific cells in Google Sheets, select the cells, open Data > Protect sheets and ranges, add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.