Compare Excel Sheets: Methods, Formulas, and Tools

Updated 10 November 2026 · Standard formula, shown below

To compare two Excel sheets, first decide whether you need to find different cell values, missing rows, or broader layout changes. For a quick check, use Excel’s side-by-side view or a formula that tests matching cells; for larger tables, conditional formatting or Power Query can make differences easier to identify and review.

What Comparing Sheets Can Reveal

“Compare two sheets” can mean several different tasks. A useful method depends on whether the sheets have the same structure and what counts as a match. For example, one sheet may contain this month’s inventory and another last month’s, while a separate task might compare two versions of a budget or a list of customer IDs.

Cell-by-cell differences: Identify values that differ at the same row and column, assuming the layouts align.
Missing or added records: Find IDs or rows present in one list but not the other, even if rows have been rearranged.
Formatting or structural changes: Check whether formulas, styles, or worksheet structure changed, not just displayed values.

A comparison can also produce false alarms. Numbers stored as text, extra spaces, different date formats, and formulas that display the same rounded value can make apparently similar cells behave differently. Before comparing, clarify whether you care about the underlying value, displayed result, formula, or formatting.

Compare Matching Cells With a Formula

When both sheets use the same layout, a formula is a direct way to flag differences. Suppose the sheets are named January and February, and you want to compare cell A2 on each. Enter this formula in a third sheet:

=IF(January!A2=February!A2,"Same","Different")

Copy it across and down over the area you want to inspect. Excel reports “Same” when the two referenced values compare as equal and “Different” otherwise. If the sheet name contains spaces, surround it with apostrophes, as in ='January Sales'!A2.

To display only a TRUE/FALSE result, use =January!A2=February!A2. To flag a difference directly, use =January!A2<>February!A2. A formula comparison is most reliable when corresponding records occupy the same positions and the compared data types are consistent.

For text where capitalization should matter, Excel’s ordinary equality comparison is generally not case-sensitive. Use EXACT when case matters: =EXACT(January!A2,February!A2). If extra spaces are a known issue, a cleaned comparison such as =TRIM(January!A2)=TRIM( February!A2) can help, though cleaning should be deliberate: spaces can be meaningful in some data.

Highlight Differences With Conditional Formatting

Conditional formatting is useful when you want to see differences in the original grid rather than create a separate results table. It can color cells based on a formula, but it does not automatically align records or explain why two values differ.

Open one sheet and select the range to compare, such as A2:D100.
Choose Home > Conditional Formatting > New Rule.
Select the rule option that uses a formula to determine which cells to format.
For a same-position comparison, enter a formula such as A2<>February!A2, adjusting the sheet name and starting cell to fit.
Choose a format, then confirm the rule. Check several highlighted and unhighlighted cells to confirm the references behave as intended.

Because a conditional-formatting formula is interpreted relative to the selected range’s first cell, an incorrect starting reference can shift the comparison. Also, rules that refer to another worksheet may be restricted or behave differently depending on the rule and Excel version. If Excel rejects a cross-sheet reference, put the comparison formula in a helper column or use a formula that refers to both sheets, then apply formatting to its results.

Compare Lists When Rows Are Rearranged

A cell-by-cell test is not suitable when the same records appear in a different order. In that case, compare a unique key such as an order number or product code. For example, if IDs are in column A on both sheets, a formula on the first sheet can check whether each ID appears on the second:

=IF(COUNTIF(February!A:A,A2)=0,"Missing from February","Found")

This detects whether the ID in A2 occurs anywhere in February’s column A. It does not confirm that the rest of the row matches, and duplicate IDs may make a simple presence test insufficient. To compare associated fields, look up the matching record by its key and test the relevant columns, or use a tool that compares complete rows.

For newer Excel versions, lookup functions such as XLOOKUP can retrieve a value from the other sheet using a key. For example, you can look up an ID and compare its corresponding price or status. If the key is absent, account for that separately rather than treating a missing record as an ordinary value difference.

Use Spreadsheet Compare or Power Query

Some Microsoft 365 or Office installations include Spreadsheet Compare, a desktop tool for comparing workbooks. Availability depends on the installed edition and configuration. It can report differences in cell values and formulas, among other workbook elements, and is a better fit than a hand-built formula when you need a broader workbook review. It is not present in every Excel installation; check the available Office tools rather than assuming it is installed.

Power Query is useful for repeatable comparisons of structured tables. Load each table as a query, then merge them using a shared key. A left anti join can identify records in the first table that have no matching key in the second; reversing the tables finds records unique to the second. A regular merge can bring corresponding fields together so you can compare values. This approach is especially helpful when data is refreshed regularly, but it requires a reliable key and some familiarity with query steps.

Common Problems and How to Avoid Them

Different row order: Do not compare cell A2 with cell A2 unless the rows represent the same record. Match on an ID instead.
Numbers stored as text: The text value "100" and numeric value 100 may not behave alike in every comparison or formula. Standardize the columns before drawing conclusions.
Hidden characters or spaces: Leading, trailing, or nonbreaking spaces can make text look identical while comparing differently. Use appropriate cleaning tools, and preserve characters that are meaningful.
Formulas versus results: Two cells can display the same result but contain different formulas. Decide whether you need to compare formulas, calculated values, or both.
Blank cells and errors: Test how your chosen method treats blanks, and inspect formula errors rather than assuming they mean a normal mismatch.
Workbook changes during review: Work from saved copies or a controlled snapshot when the source data may be edited while you compare it.

Excel’s View Side by Side command is another option when the goal is visual inspection rather than a computed difference report. Open both workbooks, go to View > View Side by Side, and scroll through them together. This helps with manual review, but it cannot reliably detect every change in a large dataset.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. Formulas, conditional formatting, Power Query, and (in some installations) Spreadsheet Compare can identify differences. Choose based on whether you are comparing aligned cells, keyed records, or entire workbooks.

How do I find rows in one sheet that are missing from another?
Compare a unique ID column with COUNTIF, a lookup formula, or a Power Query anti join. Check both directions if you need to find records unique to either sheet.

Can I compare two sheets in different workbooks?
Yes. Open both workbooks and use formulas that reference the other workbook, or use a comparison tool or Power Query. External references may depend on the source workbook’s location and availability, so keep files accessible while working.

Why does Excel show a difference when the cells look the same?
Possible causes include stored-as-text values, extra spaces, hidden characters, different underlying precision, or formulas that produce similar displayed results. Inspect the formula bar and cell formats, and compare cleaned or normalized data when appropriate.

Can I compare formatting as well as values?
Some workbook comparison tools can report formatting changes. Ordinary equality formulas compare values, not cell color, font, or other formatting. For a small area, inspect formatting manually; for a broader review, use a suitable workbook comparison tool if available.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.