How to Compare Two Excel Sheets for Differences

Before You Start: Two Types of Comparison

There are two fundamentally different comparison scenarios, and they require different approaches:

• Position-based comparison: Both sheets have the same structure and the same number of rows in the same order. Row 5 in Sheet1 corresponds to row 5 in Sheet2. This is common for version comparisons of the same report updated over time.
• ID-based comparison: Both sheets contain data about the same records, but rows may have been added, deleted, or sorted differently. Comparing row positions would show false differences. Instead, you match records by a unique identifier like a customer ID, order number, or product code, and then compare the field values for matched records.

Choosing the wrong approach leads to misleading results. A position-based formula reports every row as changed if someone inserted a row at the top of Sheet2, even if no actual data changed. Determine which scenario applies before choosing a method.

Method 1: Cell-by-Cell Formula in a Third Sheet (Position-Based)

This is the fastest approach when both sheets have identical structure and no rows were inserted or deleted. Create a third sheet and enter this formula starting at cell A1:

=IF(Sheet1!A1=Sheet2!A1,"","Changed")

Copy it across as many columns as the data has, and drag it down as many rows. Blank cells mean the values match. Cells showing "Changed" are the differences.

To see the actual values that changed, use a more informative formula:

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

This shows what the old value was and what it changed to, formatted as OldValue → NewValue. Filter the third sheet for non-blank cells to see only the changed cells.

Limitation: This method breaks completely if rows were inserted, deleted, or sorted between versions. One extra row at the top shifts every comparison by one row, producing thousands of false positives.

Method 2: Conditional Formatting to Highlight Differences

Conditional formatting can visually mark cells that differ from another sheet without creating a separate comparison sheet:

1. Select the data range in Sheet2 (the version you want to compare against Sheet1).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter: =Sheet2!A1<>Sheet1!A1 (adjust A1 to match the top-left cell of your selection).
5. Choose a fill color and click OK.

Cells in Sheet2 that are different from the corresponding cell in Sheet1 will be highlighted. This is good for a quick visual review but does not produce a printable or downloadable report, and shares the same limitation as Method 1 — it breaks when row positions differ.

Method 3: VLOOKUP-Based ID Comparison (For Row-Independent Data)

When rows may have been added, removed, or sorted, compare by a unique identifier column rather than by row position. This method finds matching records first, then compares their fields.

Assume Sheet1 has customer data with Customer ID in column A and Revenue in column B. Sheet2 is an updated version. To check whether Revenue changed for each customer:

=IFERROR(VLOOKUP(A2,Sheet2!$A:$B,2,0)-B2,"Not in Sheet2")

Place this in a helper column on Sheet1. It looks up each Customer ID in Sheet2 and returns the difference between the old and new revenue. A result of 0 means no change. A positive or negative number shows the change. "Not in Sheet2" means the record was removed from the updated version.

To find records that were added to Sheet2 but not in Sheet1, run the same VLOOKUP from Sheet2 looking up into Sheet1.

Method 4: View Side by Side for Small Datasets

For small datasets where you can manually review differences, Excel's View Side by Side feature lets you scroll both sheets simultaneously:

1. Open both sheets (or both workbooks).
2. Go to View > View Side by Side.
3. Enable Synchronous Scrolling so both panes scroll together.

This works well for sheets with fewer than 50 rows where visual review is practical. It does not generate a report, does not scale to large datasets, and does not flag differences automatically. Use it for a final manual spot-check after using a formula or tool-based method.

Method 5: Compare Excel Files Tool

The Compare Excel Files tool performs a cell-by-cell comparison between two workbooks and generates a downloadable report showing which cells changed, what the old value was, and what the new value is. It handles different sheet names and works across multiple sheets in the same comparison.

This is the right approach when:

• You need a report that documents what changed, not just a visual highlight.
• You need to share the comparison results with someone who did not build the spreadsheet.
• The sheets are too large for manual review.
• You want to compare multiple sheets in both workbooks at once.

What to Check After Comparing

Once you have identified differences, verify them in four categories:

• Changed values: The same record exists in both versions but a field value was edited. Verify whether the change was intentional or accidental.
• Added rows: Records that exist in Sheet2 but not in Sheet1. These are new entries — confirm they were expected.
• Removed rows: Records that existed in Sheet1 but are missing from Sheet2. These were deleted — confirm the deletion was deliberate.
• Formula changes: Cell values may look the same while the underlying formula changed. If formulas matter, compare them separately using Ctrl + ` to toggle formula view on both sheets.

Common Mistakes in Excel Sheet Comparison

• Using position-based comparison when rows moved: Sort both sheets by the same ID column before applying position-based formulas, or switch to ID-based comparison.
• Comparing formatted numbers vs stored numbers: A cell displaying "$1,000" stores the number 1000. A cell displaying "1000" also stores 1000. These compare as equal, even though the display differs. Formatting differences are not detected by value comparison.
• Ignoring precision differences in numbers: 0.1 + 0.2 in Excel equals 0.30000000000000004 due to floating-point precision. Two cells that display the same value may compare as different. Round both values to the relevant precision before comparing financial or scientific data.
• Not accounting for leading/trailing spaces: "East" and "East " compare as different. Use TRIM on both values before comparing if the source data may have inconsistent spacing.

How to Compare Two Workbooks (Not Just Two Sheets)

The same methods apply when comparing two separate Excel files rather than two sheets in the same workbook. For formula methods, open both files and reference the other workbook using its full name: =[OtherFile.xlsx]Sheet1!A1. For the tool-based method, the Compare Excel Files tool accepts two separate file uploads and compares them directly.

For a more detailed guide to comparing separate files, see How to Compare Two Excel Files.

Frequently Asked Questions

Does Excel have a built-in feature to compare two sheets?

Standard Excel editions do not have a built-in compare feature for sheets within the same workbook. The Inquire add-in available in Microsoft 365 E3/E5 and Office Professional Plus can compare workbooks, but it is not available in standard Microsoft 365 subscriptions. For most users, the formula methods or the Compare Excel tool are the practical options.

How do I compare two Excel sheets if they have different column orders?

If the same data exists in a different column order across two sheets, you cannot use position-based cell comparison. Instead, use the column header names to look up values. Create a comparison sheet, list each column name, and use INDEX/MATCH to pull values from the matching column in each sheet. This is more complex but handles reordered columns correctly.

How do I compare two sheets and highlight only the rows that are different?

Use the IF formula method in a helper column: =IF(Sheet1!A2=Sheet2!A2,"Match","Different"). Add this check for all columns of interest, concatenating the results. Then filter the helper column for rows where any field shows "Different". Conditional formatting on the entire row based on that helper column can also highlight the full rows automatically.