How to Compare Two Excel Sheets: A Practical Guide

To compare two Excel sheets, first make sure corresponding data is in the same positions, then use a helper formula such as =IF(A2=Sheet2!A2,"Match","Different") and fill it across and down. This quickly flags cell-by-cell differences. For sheets where rows may be in a different order, compare records by a unique ID instead. You will need Excel for Microsoft 365 on Windows, the two worksheets or workbooks, and a copy of the original files before making changes.

What You’ll Need

The two sheets you want to compare. If they are in separate workbooks, open both, or copy the sheets into one workbook for simpler formulas.
A clear idea of whether the records are in the same order. A position-by-position comparison is suitable only when matching records occupy matching rows and columns.
A spare worksheet or columns for comparison results. Avoid overwriting the original data.
For matching records by ID, a field that uniquely identifies each record, such as an order number. Duplicate IDs make lookup results ambiguous.

Step-by-Step

Make a safe working copy. Save a duplicate of the workbook, or use Save As to create a comparison copy. Keep the original sheets unchanged so you can verify an unexpected result. If the sheets are in different workbooks, open both in desktop Excel. Copying both sheets into a new workbook often makes references easier to manage, particularly if files will be moved or renamed.
Decide what counts as a match. Check whether the sheets have the same headings, column order, and row order. A cell-by-cell comparison assumes that, for example, the value in A2 on the first sheet is meant to match the value in A2 on the second. If rows have been sorted differently, use a unique ID to find the corresponding record instead. Also decide whether differences in capitalization, spaces, or formatting matter. A standard equality formula compares values, not visual formatting, and does not distinguish uppercase from lowercase text.
Check the sheets side by side. Open both workbooks, or two windows of the same workbook, and select View > View Side by Side. If Excel asks which workbook to compare, choose the other one. Select Synchronous Scrolling on the View tab to move both windows together. This visual check is useful for confirming headings and layout, but it is not a reliable way to find every difference in a large dataset. On Mac or Excel for the web, these window controls can differ or be unavailable; the formulas below work in desktop Excel for Mac as well.
Set up a helper area for matching positions. Suppose the first sheet is named Sheet1, the second is named Sheet2, and both contain data in A2:D100 with headers in row 1. On Sheet1, enter a comparison formula in an unused area, such as F2, to compare the first data cell. The formula assumes the value to compare is in A2 on both sheets; enter it in F2, not over the source data: =IF(A2=Sheet2!A2,"Match","Different"). It returns “Match” when the two cell values are equal and “Different” when they are not. If a sheet name contains spaces, enclose it in single quotation marks in the reference, as in 'Second Sheet'!A2.
Fill the comparison formula through the data range. Select the formula cell, copy it, and paste it across the helper area and down for every row and column you need to compare. In this example, copy F2 across four columns and down to row 100. Excel adjusts the cell references as the formula moves, so the next comparison checks B2 against the corresponding cell on Sheet2, then A3 against the matching cell in the next row. Leave the source columns in the same order on both sheets. If the two ranges have different sizes, extend the comparison far enough to cover the larger range and inspect the extra rows or columns separately.
Filter or highlight the differences. Select the helper results, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different, select a visible format, and click OK. You can also filter the helper area to show only cells marked Different. A helper grid makes the location of a mismatch clear; note the sheet, row, and column before deciding which value is correct. Do not replace values automatically unless you have confirmed which sheet is authoritative.
Compare records when rows are not aligned. If both sheets contain a unique ID in column A and the values you want to compare are in column B, use a lookup rather than comparing the same row numbers. For example, assume Sheet1 has IDs in A2:A100 and values in B2:B100, while Sheet2 has the corresponding IDs in A2:A100 and values in B2:B100. Enter this in an unused column on Sheet1, starting on row 2: =IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different"),"Missing from Sheet2"). XLOOKUP finds the ID from A2 in Sheet2 and returns its column B value. If ID 104 has value 250 on both sheets, the result is “Match”; if Sheet2 has 275, it is “Different”; if the ID is absent, the result is “Missing from Sheet2.” XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Check that IDs are unique on both sheets before relying on the result.
Review missing records and totals. A formula filled only to the last row on Sheet1 will not automatically tell you that Sheet2 has additional records beneath that point. Compare the row counts, inspect the area below the shorter list, and check whether important totals agree. If the lists are large, sort or filter by the ID column and review IDs that appear on only one sheet. Do not treat matching totals as proof that every individual value matches; different errors can cancel each other out.
Use Spreadsheet Compare if it is available. Some Windows editions of Microsoft Excel include the Inquire add-in and Spreadsheet Compare. To check, select File > Options > Add-ins, choose COM Add-ins in the Manage box, select Go, and see whether Inquire is available to enable. If enabled, open the Inquire tab and select Compare Files, then choose the two workbooks. The tool reports differences in workbook contents and structure. It is not included in every Excel edition and is not generally available in Excel for Mac or the web. If you do not see it, use the formula method instead.
Save a readable comparison report. Keep the helper results in the comparison copy, label the sheets and result columns clearly, and record the date or source of each file. Before sharing, confirm that the formulas cover the full intended range and that the recipient can access any referenced workbook. If the comparison is complete, you can copy the results and use Paste Special > Values in a separate report area to preserve the findings without live links.

Common Mistakes

Comparing row numbers when records have moved. A formula comparing A2 with A2 can mark perfectly good records as different if one list has been sorted. Match by a stable ID when order is not guaranteed.
Using an incomplete range. A formula copied only partway down can miss changes near the end. Check the final row and column in both sheets, including records present in only one.
Assuming matching appearance means matching data. A number stored as text may look like a number but compare differently. Formulas also do not detect differences in font, fill, borders, or number format.
Overlooking duplicates or blanks in IDs. A lookup is dependable only when the key identifies one record. Duplicate IDs can return an unexpected match, and blank IDs can be mistaken for records.
Overwriting source values or trusting every flag immediately. Put formulas in an unused area and investigate mismatches before editing either original sheet. A difference is a signal to review, not a recommendation about which value is right.

Tips

Keep headers in row 1 and data in consistent columns. Matching headings make it easier to spot shifted or missing fields before comparing values.
For large comparisons, add a clear label above each helper column, such as “A comparison” or “Status by ID.” This makes it easier to trace a result back to the relevant field.
Standard equality comparisons in Excel are not case-sensitive. If capitalization must count as a difference, use the EXACT function. For example, with the values to compare in A2 on both sheets, enter this in an empty result cell: =IF(EXACT(A2,Sheet2!A2),"Match","Different"). EXACT treats “North” and “north” as different.
Clean imported text before comparing if you suspect stray spaces. TRIM can remove ordinary leading and trailing spaces, but nonbreaking spaces copied from websites may need separate cleanup. Keep cleaned values in new columns so you can still inspect the original text.
For recurring monthly comparisons, keep a reusable copy of the workbook with clearly named input and results sheets. Replace the source data carefully, then confirm the comparison range still covers all rows.
On Mac, the formulas and worksheet references are the same. Ribbon placement and window comparison commands may differ slightly from Windows; Excel for the web also has fewer desktop comparison features.

Troubleshooting

The formula displays #REF!. The referenced sheet may have been renamed or deleted, or a workbook link may point to a file Excel cannot find. Check the sheet tab name and the reference. If the name contains spaces, use single quotation marks around it, such as 'Second Sheet'!A2.
Everything says Different, even when values look identical. Check that the formulas compare the intended columns and rows. Then look for numbers stored as text, extra spaces, dates represented differently, or formulas that produce different underlying values. A displayed value rounded to two decimal places can hide a difference in the stored value.
Everything says Match, but you know records differ. Confirm that the formula was copied across every relevant column and down to the last row. Check that the sheet reference points to the correct workbook or worksheet. If capitalization matters, use EXACT; ordinary equality does not flag case-only changes.
XLOOKUP reports a missing ID that appears to exist. Compare the ID cells for leading or trailing spaces, different data types, or characters that look alike but are not identical. Confirm the lookup range includes the ID and that the return range covers the corresponding values. If IDs are duplicated, resolve that data issue before interpreting the result.
The results miss extra rows on one sheet. Extend the comparison or create a separate check for records beyond the shorter list. A row-by-row formula cannot report data it was never copied far enough to examine.
You cannot find Inquire or Compare Files. The feature depends on the installed Excel edition and platform. It may not be included in your license, and it is not available in Excel for the web. Use formulas or request an edition that includes the Inquire add-in rather than relying on a missing menu item.

Frequently Asked Questions

Can I compare two Excel sheets without a special add-in?
Yes. A helper formula such as IF can compare corresponding cells, and XLOOKUP can compare records by ID in Microsoft 365 or Excel 2021 and later. Spreadsheet Compare is optional.

Can I compare sheets in separate workbooks?
Yes. Open both workbooks and reference the other workbook in the formula, or copy the worksheets into a comparison workbook. External links can break if a source file is moved or renamed, so keeping both sheets together is often simpler.

Does the comparison formula detect formatting changes?
No. A formula compares cell values, not formatting such as font color, borders, or number formats. Use Spreadsheet Compare where available for workbook-level checks, or inspect formatting separately.

Can Excel identify differences when rows are in a different order?
Yes, if each record has a reliable unique ID. Use XLOOKUP to find the matching ID on the other sheet and compare the associated field. A direct cell-by-cell formula cannot reliably match reordered records.

Why does Excel say two cells differ when they look the same?
They may contain invisible spaces, different data types, or values with more precision than the displayed format shows. Check the formula bar and, if needed, clean the values in helper columns before comparing again.

Will these formulas work in Excel for Mac?
Standard worksheet formulas such as IF and XLOOKUP work in current Excel for Mac versions that support those functions. Some Windows-only tools, including Spreadsheet Compare, may not be available; use the formula approach when they are absent.

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.