Excel Compare Two Sheets and Show Differences Clearly

To compare matching cells on two worksheets, put this formula in a third sheet and fill it across and down: =IF(Sheet1!A1=Sheet2!A1,"Match","Different"). This assumes the worksheets are named Sheet1 and Sheet2 and the same records occupy the same cells; the result identifies each matching or different pair so you can review changes.

Choose a comparison method that fits your data

The right method depends mainly on whether the two sheets have the same layout. If row 10 and column C represent the same thing on both sheets, a cell-by-cell formula is direct and easy to audit. If rows have been sorted differently, compare records using a shared identifier, such as an employee ID, product code, or invoice number. Comparing by position in that situation can label many correct records as different simply because they moved.

Also decide what you want the result to show. A comparison can mark every matching cell, flag only differences, display the old and new values side by side, or produce a list of records that are missing from one sheet. For a quick review, color highlights may be enough. For a repeatable monthly check, formulas or Power Query are more useful because they can be refreshed or recalculated after the source data changes.

Same structure and order: compare corresponding cells with a formula.
Same records, different order: look up each record by a unique key.
Large, recurring imports: consider a Power Query merge or a dedicated workbook comparison tool available in your Excel edition.

Compare cells when both sheets have the same layout

For a straightforward comparison, insert a blank worksheet and enter the formula in its top-left cell. The assumed layout is that the worksheets are named Sheet1 and Sheet2 and their values begin in A1; in the comparison sheet, enter this in A1:

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

Press Enter, select the formula cell, and drag or copy its fill handle across and down to cover the area you need. Excel adjusts the relative references as the formula moves. For example, the next cell to the right compares B1 on the two source sheets, and the cell below compares A2. A result of Match means Excel considers the two cell values equal; Different means they are not equal.

If your worksheet names contain spaces, enclose each name in single quotation marks. For instance, with worksheets named January Sales and February Sales and the corresponding values in A2, enter this in the comparison sheet’s A2:

=IF('January Sales'!A2='February Sales'!A2,"Match","Different")

The formula compares underlying cell values, not the appearance of the cells. A value formatted as currency on one sheet and as a plain number on the other can still compare as equal. Conversely, formulas that display the same rounded number may have different underlying results. If a difference matters only when it changes a displayed result, consider comparing rounded values deliberately rather than assuming the formats settle the question.

Show only differences and make them easy to scan

If a grid full of Match labels is distracting, have the comparison sheet return a blank for equal cells and a label for unequal ones. With the same Sheet1 and Sheet2 layout as above, enter this in the comparison sheet’s A1 and fill across and down:

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

Now the comparison grid remains mostly empty, and each difference is visible. You can also show the values themselves. For example, make separate comparison columns for the value from each source sheet, then place a status formula beside them. This is often clearer than a single “Different” label because reviewers can see what changed without switching worksheets.

To add color, select the comparison-result range and use Home > Conditional Formatting > Highlight Cells Rules to highlight cells containing the text Different. Another option is to apply a fill color to all nonblank cells through Home > Conditional Formatting > New Rule, choosing a rule for cells that are not blank. Because the comparison formula puts its result on the comparison sheet, the formatting rule only needs to inspect cells on that same sheet.

Excel’s Conditional Formatting rules have limits on direct references to other worksheets. Building the comparison formula first avoids relying on a cross-sheet conditional-formatting reference and leaves a visible audit trail. To share the result without live links, copy the comparison range and use Home > Paste > Paste Values in a separate area or workbook; values will no longer update when the source sheets change.

Compare lists by a key when rows are out of order

When records may appear in different row positions, match them by a key rather than comparing A2 with A2. Suppose Sheet1 has product IDs in column A and prices in column B, while Sheet2 has the same fields in columns A and B. In Sheet1, enter this in C2 to find the matching ID on Sheet2 and compare its price:

=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B),"Match","Different"),"Missing from Sheet2")

Copy the formula down for the records in Sheet1. XLOOKUP searches for the ID in Sheet2 column A and returns the corresponding price from column B. If that price differs from B2, the result is Different; if the ID cannot be found, the result is Missing from Sheet2. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, an INDEX/MATCH or VLOOKUP-based approach can perform a similar lookup, but the formula needs to be adapted to the column layout.

This example assumes IDs are unique and nonblank. XLOOKUP returns the first match it finds, so duplicate IDs can conceal additional records that need attention. Check for duplicates in each source before relying on a one-to-one comparison. To find records that exist only on Sheet2, make a similar check in Sheet2 against Sheet1; a one-directional check will not report records found only in the second list.

Use Power Query for repeatable comparisons

For sizeable tables that are imported or updated regularly, Power Query can merge the two datasets using a shared key. In Excel for Microsoft 365 on Windows, turn each range into a table with Insert > Table, then use Data > Get Data to bring the tables into Power Query. In the Power Query Editor, choose Home > Merge Queries, select the key column in each table, and choose an appropriate join type. A full outer join includes rows from both inputs, making it useful when you need to identify records present on only one side as well as matched records.

After merging, expand the second table’s columns and inspect the paired values and any nulls. A null on one side can indicate that a key has no match; unequal values on matched rows need a comparison step in the query or a review of the resulting columns. Select Home > Close & Load to return the result to Excel. When the source tables are updated, refresh the query rather than rebuilding the comparison manually.

Power Query is more involved to set up than a cell formula, and the available tools and interface differ by Excel version and platform. Excel for the web and Mac may not expose the same Power Query options as Excel for Windows. If the workbook must work across several environments, test the workflow in the versions your colleagues use, or choose formulas that those versions support.

Understand what Excel counts as a difference

A basic equality test is useful, but it does not answer every interpretation question. Excel’s ordinary equality comparison is generally case-insensitive for text, so apple and Apple compare as equal with the formulas above. If capitalization itself matters, use the EXACT function. If spaces or inconsistent spelling are common, decide whether to clean the source values first; trimming spaces or standardizing text can prevent apparent mismatches, but it can also hide a real difference if the original text must be preserved.

Numbers stored as text are another frequent source of confusion. A cell containing the number 123 and a cell containing text that looks like 123 may not behave identically in calculations or comparisons. Dates are stored as serial numbers, so matching dates can compare equal even if their display formats differ. Floating-point calculations can also produce tiny underlying differences that are not obvious on screen. For measurements or calculated values, define a sensible tolerance or rounding rule rather than treating every tiny variance as meaningful.

Blank cells need attention too. A formula that compares two empty-looking cells can report a match, even when one cell is truly empty and the other contains a formula returning an empty string. If the distinction matters, inspect the source formulas or use a separate check for whether a cell is empty. Finally, make sure the comparison range covers all relevant rows and columns. A formula-filled grid cannot reveal records outside the area you included.

Common problems and practical checks

Every row appears different: check whether the lists are sorted in the same order. If not, match by a key.
The formula shows a reference error: confirm that the worksheet names are spelled correctly and use single quotation marks around names containing spaces or punctuation.
Some records are missed: check for duplicate or blank keys, and compare in both directions to find records present in only one sheet.
Values look identical but do not match: examine number-versus-text storage, hidden spaces, formulas, and the underlying precision of numbers.
Highlights do not update: verify that the conditional-formatting range covers the comparison cells and that calculation is set to update automatically.

For a reliable review, keep an untouched copy of the original sheets, label the comparison output clearly, and note the date or source of each dataset. If you share a workbook containing formulas linked to other sheets, those links can update as the source data changes. For a fixed report, save a separate values-only copy after checking the results.

Frequently Asked Questions

Can Excel automatically highlight differences between two sheets?
Yes. A dependable approach is to create a comparison grid with formulas, then use Conditional Formatting on that grid to highlight cells labeled Different. This avoids the limitations of conditional-formatting rules that refer directly to another worksheet.

How do I compare two sheets when the rows are in a different order?
Use a shared, preferably unique key such as an ID to find the corresponding record on the other sheet. XLOOKUP can compare related values in Microsoft 365 and Excel 2021 or later; check both directions to find records missing from either list.

Does Excel compare uppercase and lowercase text as different?
Not with the basic equality formula in this article: ordinary text comparisons are not case-sensitive. Use EXACT when capitalization must count as a difference.

Can I compare two workbooks instead of two sheets?
Yes. You can use formulas that refer to cells in another open workbook, or import the relevant tables into Power Query and merge them. Keep the source file accessible while setting up external references, and check links before sharing the result.

Will the comparison update when I edit the source sheets?
Formula results recalculate when their referenced values change, subject to the workbook’s calculation settings. A Power Query result generally needs to be refreshed. A values-only copy is static and will not update.

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.