How to Compare Two Excel Sheets in Excel for Differences

To compare two Excel sheets cell by cell, add a comparison sheet and enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different"), replacing the sheet names and cell reference as needed. Fill the formula across and down to flag differences. This works best when both sheets have the same layout; if rows are in a different order, match them using a unique key instead. The steps below are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

An Excel workbook containing both sheets. If the data is in separate workbooks, open both files before comparing them.
For a cell-by-cell comparison, the same kind of information arranged in the same cells on both sheets, with matching row and column positions.
A blank worksheet for comparison results, or space for a results column that will not overwrite existing data.
If records may be in a different order, a shared, unique identifier for each record, such as an order number or employee ID.

Step-by-Step

Check and prepare both sheets. Open the workbook and confirm that the sheets contain the data you intend to compare. Check that column headings make sense, that each sheet covers the same date range or group of records, and that neither sheet has been accidentally sorted or filtered in a way that changes which rows correspond. Clear filters if they could hide records you need to review. For a direct cell-by-cell comparison, you do not need identical formatting, but you do need corresponding values in corresponding positions.
Choose the right comparison method. Use a cell-by-cell comparison when row 10 on the first sheet represents the same record as row 10 on the second sheet, and the columns also line up. Use a key-based comparison when the same records appear in a different order. These methods answer different questions: the first checks whether values at matching positions agree, while the second looks up a corresponding record by its identifier. If you compare reordered lists cell by cell, Excel will report differences even when the records themselves match.
Create a results sheet. Click the plus button beside the sheet tabs to insert a worksheet, then rename it something clear, such as Compare. In the comparison sheet, you can copy the headings from one source sheet for context. If the source sheets have headings in row 1, start comparisons in row 2 so the headings are not treated as data. Keep the results sheet separate from your source data so that you can review changes without altering either original list.
Enter a comparison formula for matching layouts. With the data beginning at A1 on both source sheets and the corresponding values in the same cells, enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different") in the first result cell. If the tabs have different names, replace Sheet1 and Sheet2 with their actual names. For a sheet name containing spaces, put single quotation marks around it, as in ='January Data'!A1. For example, if Sheet1!A1 and Sheet2!A1 both contain 125, the result is Match; if one contains 125 and the other 130, the result is Different. This comparison ignores capitalization in text, so “North” and “north” count as a match.
Copy the formula over the full comparison area. Select the result cell, then drag its fill handle—the small square at the lower-right corner—across the columns and down the rows you need to compare. Excel adjusts relative references as the formula fills, so the next result checks the next corresponding pair of cells. Double-check the bottom-right result cell to make sure the filled area covers all the data and has not included irrelevant blank rows. If your source sheets have headings, do not fill over them unless you intentionally want to compare those headings too.
Make differences easy to scan. Select the results range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different, choose a formatting style, and confirm. The results that say Different will stand out. You can also use the filter buttons on a header row to filter the results column to Different, if you have included a suitable heading and enabled filtering with Data > Filter. Review the highlighted cells against both source sheets; a flag identifies a mismatch but does not explain which version is correct.
Compare records when rows are in a different order. Suppose each sheet has unique IDs in column A and amounts in column B, with records in rows 2 through 100. On Sheet1, enter this formula in C2 and fill it down: =IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing from Sheet2",IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different")). If ID 104 has an amount of 250 in both sheets, the result is Match; if Sheet2 has 255, it is Different; if ID 104 is absent from Sheet2, the result is Missing from Sheet2. This assumes IDs are unique and amounts are in the stated columns. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. To check the other direction too, create a corresponding check on Sheet2 for IDs missing from Sheet1.
Review the results and preserve your originals. Investigate every flagged difference before deciding what to change. Check whether the change is intentional, whether a value was entered as text in one sheet and a number in the other, and whether a record was omitted. Save the workbook with a clear name so you retain the original data and the comparison results. A comparison formula updates when its source values change, so the results may change too.

For a visual review instead of formula results, Excel for Windows can show two workbook windows together. Open the workbooks, use View > New Window if you need a second view of the same workbook, then choose View > View Side by Side. This makes it easier to inspect corresponding areas but does not automatically identify mismatches. The View tab and available window arrangements can vary slightly by Excel version. Excel for Mac also has window-arrangement options on the View tab; in Excel for the web, formulas and conditional formatting work, but arranging workbook windows may depend on your browser and open windows.

Common Mistakes

Comparing the wrong positions. A cell-by-cell formula assumes that corresponding records occupy the same rows and columns. If one sheet was sorted independently, use a unique key to look up records instead of trusting row positions.
Including headings or excluding data. A formula range that starts on the wrong row may compare column labels as if they were records, or leave the last records unchecked. Confirm the first and last cells in the filled range.
Assuming a match means everything is identical. The basic formula compares cell values, not formatting, comments, or the formula text behind a calculated result. It also treats text that differs only by letter case as equal. Decide which kind of difference matters for your task.
Overlooking duplicates in a key column. XLOOKUP returns a match for the first matching key it finds. If an ID appears more than once, the key-based result may not compare the intended record. Check that identifiers are unique or create a genuinely unique combined key before relying on the lookup.
Editing source data while reviewing. Sorting, deleting, or changing cells can affect formulas and make the comparison harder to audit. Keep a copy of the original workbook and make corrections only after checking the flagged results.

Tips

Use descriptive headings such as Status or Comparison result, and keep those headings outside the formula-filled range. Clear labels make filtered results easier to understand later.
If leading or trailing spaces may be causing apparent mismatches, inspect the original cells before changing anything. Extra spaces can make text values compare as different. Clean a separate copy of the data rather than overwriting the source until you know the cleanup is appropriate.
If the sheets contain formulas, decide whether you want to compare their displayed results or the formulas themselves. A normal cell comparison checks calculated values. It will not tell you that two cells use different formulas when those formulas currently return the same value.
For large ranges, compare only the relevant columns and records. A smaller, clearly defined range is quicker to fill, easier to inspect, and less likely to produce confusing flags from unused cells.
When you need a permanent record, save the comparison workbook with a date or version label. Keep the source sheets unchanged so another person can trace each result back to the original data.

Troubleshooting

The formula shows #REF!. A referenced sheet may have been renamed or deleted. Check the sheet tabs and update the formula so every sheet reference uses the current tab name. If a name includes spaces, enclose it in single quotation marks.
Every result says Different. First verify that the comparison cells represent the same records and that the formula references the intended sheets. Then check whether values have different underlying types, such as a number in one sheet and text in the other, or whether text contains extra spaces. A displayed number can look the same while its stored value differs.
Some expected differences are not flagged. The basic equality comparison is not case-sensitive, so capitalization alone will not trigger Different. It also compares values rather than formatting or formula structure. If case matters, use a case-sensitive comparison method; if formatting matters, inspect formatting separately.
The results do not change after editing the data. Confirm that calculation is set to automatic under Formulas > Calculation Options > Automatic. If calculation is manual, select Formulas > Calculate Now or press F9 on Windows to recalculate. On Mac, use the Formulas tab or the Mac keyboard equivalent for recalculation, which can vary by keyboard and Excel version.
XLOOKUP returns an error or the wrong record. Confirm that the lookup and return ranges cover the same rows, that the ID values use consistent formats, and that the lookup key is unique. The example formula labels a key absent from the lookup range as missing; duplicate keys can still make the returned value ambiguous.
Conditional formatting does not highlight the results. Check that you selected the result cells and entered the word Different exactly as it appears in the formula output. Open Home > Conditional Formatting > Manage Rules to confirm that the rule applies to the correct range.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. A formula can compare matching cells and recalculate when either source value changes. Excel does not automatically know which records correspond if the sheets have different layouts or row orders, so use a shared key for that situation.

Can I compare sheets in two different workbooks?
Yes. Open both workbooks and create references to cells in the other workbook, or copy the data into one workbook first. External workbook references can be lengthy, and Excel may ask you to update links when a source file moves or is renamed. Keeping both sheets in one workbook makes the comparison easier to maintain.

Does the formula detect changes in formatting?
No. The comparison formula checks cell values, not font color, fill color, borders, or number formatting. Review formatting separately if it matters, or use a comparison tool available in your Excel edition that is designed to compare workbook structure and formatting.

Will this work in Excel for Mac or Excel for the web?
The IF and XLOOKUP formulas described here work in current Microsoft 365 versions on Windows, Mac, and the web. Ribbon locations and window-arrangement controls can differ. XLOOKUP also requires Microsoft 365 or Excel 2021 or later; older editions may need a different lookup approach.

Can I compare formulas rather than their results?
The basic formula compares the values Excel calculates, not the formula text. If two cells show the same result but use different formulas, compare their formula text separately or use a suitable workbook-comparison feature available in your edition.

What if one sheet has extra rows?
For matching layouts, fill the comparison far enough to cover the larger range, then review the extra rows carefully. For reordered records, use the key-based method and run checks in both directions so you can identify records missing from either sheet.

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.