Excel Compare Two Sheets: Methods and Formulas

To compare matching cells on two sheets in the same workbook, enter ='Sheet1'!A1='Sheet2'!A1 in a blank cell; Excel returns TRUE when the values match and FALSE when they differ. For a clearer result, use an IF formula, fill it across the matching range, or choose a key-based method if the records may appear in a different order.

What comparing two sheets can tell you

A comparison can answer several different questions, and the right method depends on which one you mean. You might want to check whether two identically arranged tables have the same value in every cell, find records that are present on one sheet but missing from the other, or identify changes in selected fields such as price or status. These are not interchangeable tasks: a cell-by-cell formula assumes the corresponding records are in the same positions, while a key-based comparison can match records by an ID even when rows have been sorted differently.

Excel does not have one universal Compare Two Sheets command on the standard ribbon that solves every version of this problem. Formulas, conditional formatting, side-by-side viewing, and Power Query each help with a different kind of comparison. Start by checking whether the sheets have the same layout and row order, and decide whether you need to compare values, formulas, formatting, or whole records.

Compare matching cells with a formula

For a direct comparison, first choose a blank area or a third worksheet for the results. Assuming the sheets are named Sheet1 and Sheet2, and the first values to compare are both in A1, enter ='Sheet1'!A1='Sheet2'!A1. The formula returns TRUE for equal values and FALSE for unequal values. It compares the results stored in the cells, not whether the underlying formulas are written the same way.

If you prefer words instead of TRUE and FALSE, with the two values still in Sheet1!A1 and Sheet2!A1, enter =IF('Sheet1'!A1='Sheet2'!A1,"Match","Different"). Copy the formula across and down to cover the area you want to check. In Microsoft 365 for Windows, use the fill handle at the lower-right corner of the selected cell, or copy and paste the formula into the destination range. The relative A1 references adjust as the formula moves.

If a worksheet name contains spaces or punctuation, keep the single quotation marks around the name, as in ='January Sales'!A1='February Sales'!A1. Sheet names without spaces can also be quoted; using the quotes consistently makes the reference easier to read. If rows or columns are inserted later, check that the formulas still point to the intended cells.

To compare a rectangular range, place the formula in the corresponding top-left result cell and fill it across and down. For example, if the source data occupies A1:D50 on each sheet, the result range should also be 50 rows by four columns. A matching pattern of TRUE results means the compared cell values agree; FALSE cells indicate differences. The result range is useful as a diagnostic map, but it is not a permanent record of past changes: it recalculates when the source values change.

Show only the differences or compare text exactly

A large grid of TRUE and FALSE values can be difficult to scan. An IF formula makes each result more readable, and you can filter a results table for Different if you arrange the output so it can be filtered. Another practical option is to put the comparison formula beside the source data, then use Home > Sort & Filter > Filter to show only rows with a difference. Make sure the filter range includes the relevant headers and result column.

Ordinary equality comparisons are generally not case-sensitive for text, so lowercase and uppercase versions of a word can count as equal. If capitalization matters, with the text values in Sheet1!A1 and Sheet2!A1, use =EXACT('Sheet1'!A1,'Sheet2'!A1). EXACT returns TRUE only when the text strings have the same characters and capitalization. It is useful for codes and labels where letter case is meaningful, but it should not be used if case differences are unimportant.

Spaces and invisible characters are another source of surprising results. A value that looks identical on screen may contain a leading space, a trailing space, or a nonprinting character. If the comparison reports a difference, inspect the source cells in the formula bar and consider cleaning imported data before comparing. Functions such as TRIM can remove ordinary extra spaces in a separate helper column, but they do not remove every possible nonprinting character or every type of whitespace.

Compare records when the row order may differ

Cell-by-cell comparison can give misleading results when someone sorts one sheet, inserts rows, or exports the same records in a different order. In that situation, compare using a stable identifier such as an order number, employee ID, or product code. The identifier should be present on both sheets and, ideally, be unique for each record. If it is not unique, a match may identify the wrong occurrence or fail to distinguish duplicate records.

For a basic presence check, suppose Sheet1 has unique IDs in A2:A100 and Sheet2 has IDs in A2:A100. In a result column beside the first ID on Sheet1, enter =COUNTIF('Sheet2'!$A$2:$A$100,A2)>0, then fill down. TRUE means that the ID from the current Sheet1 row appears at least once in the specified Sheet2 range. FALSE means it was not found there. Repeat the check in the other direction to find IDs that exist only on Sheet2. COUNTIF treats some criteria characters, such as * and ?, as wildcards, so take care if IDs can contain those characters.

When you need to compare a field after locating its record by ID, XLOOKUP can return the corresponding value from the other sheet. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Suppose IDs are in Sheet1!A2:A100, values to compare are in Sheet1!B2:B100, and the corresponding ID and value columns on Sheet2 are A2:A100 and B2:B100. Beside the first Sheet1 record, enter =IFERROR(XLOOKUP(A2,'Sheet2'!$A$2:$A$100,'Sheet2'!$B$2:$B$100)=B2,FALSE) and fill down. TRUE means the looked-up value matches the value in B2; FALSE means the value differs or the ID was not found. XLOOKUP returns the first match, which is why unique IDs matter.

Use visual review and conditional formatting

For a quick manual inspection, open a second window of the workbook with View > New Window, then use View > View Side by Side. Arrange the windows so the relevant sheets are visible, and use Synchronous Scrolling if you want them to move together. This is useful for short tables or for checking layout and context around a difference. It is not a reliable substitute for formulas when a workbook has thousands of cells, and scrolling both windows together does not prove that records are aligned.

Conditional formatting can make formula results easier to spot. One dependable workflow is to create a comparison grid containing TRUE/FALSE or Match/Different results, then select that grid and choose Home > Conditional Formatting > Highlight Cells Rules to apply a visual style to the results you want to review. This avoids a common snag: formula-based conditional-formatting rules have restrictions on direct references to another worksheet. A helper comparison range also makes the logic visible and easier to audit.

Formatting deserves a separate check if the question is whether cells look the same. A value comparison does not report differences in fill color, font, number format, borders, or conditional-formatting rules. Conversely, two cells may display the same rounded number while holding different underlying values. Decide whether the requirement concerns stored values, displayed results, formulas, or appearance before treating a comparison as complete.

When Power Query is a better choice

Power Query is useful when the sheets contain larger tables, records need to be matched by columns, or you expect to repeat the comparison as new data arrives. Convert each data range to an Excel table, then select a cell in a table and choose Data > From Table/Range to open it in Power Query. Use Merge Queries to join the two tables on their shared key. A Left Anti join can return rows from the first table that have no matching key in the second; use the reverse direction to find rows found only in the second.

To compare fields for records that do match, merge on the key and expand the needed columns from the second table. The resulting query can expose records whose selected values differ, and it can be refreshed after the source tables change. Check the key columns for duplicates and confirm that the chosen join columns have compatible data types. Power Query is more setup than a one-cell formula, but it is often easier to refresh and document for a recurring reconciliation. Menu labels and available options can differ slightly between Excel builds, and Power Query features are more limited in Excel for the web than in the Windows desktop app.

Common causes of false differences

Before investigating every FALSE result, check the data itself and the assumptions behind the comparison. These common issues account for many apparent mismatches:

Different row order: the formula compares positions, not identities. Use a key-based lookup or join instead.
Numbers stored as text: an imported value such as the text string 00123 may not behave like the number 123, and converting it may also remove meaningful leading zeros.
Blank cells and zeros: decide whether an empty cell and a numeric zero should count as different for your task. Inspect the source cells and make the rule explicit.
Rounding and display formats: displayed values can look the same even when their underlying precision differs. Compare the stored values or round them deliberately if the business rule calls for that.
Duplicate or incomplete keys: a lookup can return the first duplicate, while a missing key cannot establish which record should match.
Different formulas with the same result: a value comparison can return a match even if one cell contains a constant and the other contains a formula.

If the task is to compare formulas rather than their calculated results, inspect the formula text separately. You can display formulas in a worksheet with Formulas > Show Formulas, or use the FORMULATEXT function where it is appropriate. Formula-text comparison is a different test: equivalent calculations can be written differently, and a formula comparison does not establish that either formula is correct.

Choose a method and keep the comparison useful

Use a direct equality formula when the two sheets have the same layout and row order. Add IF labels or a formatted helper grid when people need to review the result. Use EXACT when text capitalization matters. Use a unique-key check or XLOOKUP when records may move, and consider Power Query for repeatable table reconciliations. Side-by-side viewing is best for visual context and small-scale checks, not exhaustive verification.

For a dependable review, keep the original sheets unchanged, put comparison results on a separate sheet, and label the date or source version being compared. Record the range or key fields included, and check both directions when looking for missing records. A one-way check can find items missing from Sheet2 but will not reveal items that appear only in Sheet2. Finally, spot-check several results against the source cells, especially when formulas, imports, duplicate IDs, or blank values are involved.

Frequently Asked Questions

Can Excel compare two sheets in the same workbook automatically?
Yes. A formula can compare corresponding cells and update when values change. For repeated table comparisons, Power Query can also refresh a merge based on the latest source data. Excel does not automatically know which comparison rule you intend, so you must define whether to match by position, key, formula, or formatting.

How do I compare two sheets and highlight differences?
Create a comparison formula for each matching cell, then format the result grid or filter for Different results. For a visual check without formulas, display both sheets side by side, but use a formula or query for a larger range where manual scanning could miss a difference.

Can I compare sheets if the rows are in a different order?
Yes. Match the rows using a stable identifier rather than comparing A1 with A1 and so on. COUNTIF can check whether each ID exists, while XLOOKUP can retrieve a corresponding value for comparison. Unique identifiers make the result more dependable.

Does a TRUE result mean the formulas in both cells are identical?
No. A direct equality formula compares the cells’ values or calculated results. Different formulas can produce the same result, and a formula can match a constant. Use formula inspection if the formulas themselves are what you need to compare.

Can I compare two Excel sheets on a Mac or in Excel for the web?
The basic cross-sheet formulas work in current Excel for Mac and Excel for the web, though ribbon layout and some features can differ. The Windows directions for opening a second workbook window may not match the Mac interface, and Power Query capabilities vary by platform. If a command is missing, use the formula method or check the options available in your Excel build.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.