Excel Sheet Comparison Formula: Find Differences

Formulas & functions · Updated November 19, 2026

To compare the same cell on two Excel sheets, enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different") on a third sheet, then fill the formula across or down. It returns “Match” when the two cell values are equal and “Different” when they are not; replace the sheet names and cell reference to fit your workbook.

What a sheet comparison formula checks

A cell-by-cell comparison checks the values Excel gets from two corresponding cells. If Sheet1!A1 contains 25 and Sheet2!A1 also contains 25, the formula reports a match. If one contains 25 and the other contains 30, it reports a difference. The formula does not alter either source sheet: it creates a separate result that you can inspect or filter.

By default, Excel’s equals comparison is not case-sensitive, so text such as “North” and “north” is treated as equal. The comparison also looks at the cell value, not its appearance. Different number formats, font colors, borders, or column widths do not make two cells different according to this formula. Likewise, if a cell contains a formula, the comparison normally checks the formula’s calculated result, not the formula text itself.

A blank cell compared with another blank cell is generally reported as a match. A cell containing a formula that returns an empty string can also look blank, so a value comparison may not reveal that one cell has a formula and the other is truly empty. Decide whether you care about displayed results, underlying formulas, or formatting before choosing a comparison method.

Set up the basic formula

Assume the source worksheets are named Sheet1 and Sheet2, and that the two values to compare are in A1 on each sheet. On a third worksheet, click A1 and enter:

With the corresponding values in Sheet1!A1 and Sheet2!A1, and the result in the third sheet’s A1:

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

For this layout, if both source cells contain 25, the result is “Match”; if Sheet2!A1 contains 30, the result is “Different.” The IF function tests the equality condition, then returns the first text label when it is true and the second when it is false. You can replace these labels with alternatives such as “OK” and “Review,” but keep the straight quotation marks around text.

If a worksheet name contains spaces or punctuation, enclose it in single quotation marks and put an exclamation mark between the name and cell reference. For example, the formula below compares A1 on worksheets called January Sales and February Sales:

With the corresponding values in January Sales!A1 and February Sales!A1, and the result in a third sheet’s A1:

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

Sheet names are not case-sensitive, but spelling and spaces must match the worksheet tabs. You can also build a reference by typing an equals sign, clicking the source sheet and cell, and continuing the formula, which can help avoid mistyped names.

Compare a whole range efficiently

To compare multiple cells, put the formula in the first cell of a blank comparison area, then copy it to the rest of the corresponding range. For example, if the source worksheets each have data in A1:D100, put the basic formula in A1 of a third sheet and fill it through D100. Excel adjusts relative references as the formula moves: the comparison in B2 will compare Sheet1!B2 with Sheet2!B2.

On Windows, drag the fill handle at the bottom-right corner of the selected cell, or copy the formula and paste it into the target range. On Mac, the fill handle and copy-and-paste approach work similarly. Check the first, last, and a few middle results to confirm that the formulas are pointing to matching positions rather than shifted ranges. If you change the layout, keep the two source ranges aligned.

For large comparisons, use a dedicated results sheet and add column headers or row labels there. That makes it easier to see which result belongs to which record. A “Different” result alone does not tell you which source value is correct, so consider placing the original values beside the result or adding a clear identifier such as an account number or product code.

Make comparisons case-sensitive or ignore extra spaces

When capitalization matters—for example, when comparing codes that distinguish ABC from Abc—use EXACT. With text values in A1 on Sheet1 and Sheet2 and the result in a third sheet’s A1, enter:

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

If Sheet1!A1 contains “ABC” and Sheet2!A1 contains “Abc,” this returns “Different.” EXACT is useful for text, but use a normal equality comparison when case is irrelevant or when your cells may contain varied data types.

Extra ordinary spaces can cause text values that look alike to compare as different. TRIM removes leading and trailing spaces and reduces repeated internal spaces to one. With text values in A1 on Sheet1 and Sheet2 and the result in a third sheet’s A1, use:

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

For example, “Blue sky” and “ Blue sky ” will match after trimming. TRIM does not remove every invisible character: data copied from web pages may contain nonbreaking spaces, which need separate cleanup. Also, trimming deliberately treats some different text strings as equivalent, so use it only when those extra spaces are not meaningful in your data.

Highlight differences visually

A comparison formula can return labels, while Conditional Formatting can make those labels easier to scan. Suppose your comparison results are in A1:D100 on a worksheet named Compare. Select that range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter Different. Choose a format and confirm. Excel will highlight result cells containing that word.

You can also use a formula-based rule on the results range. With comparison labels in Compare!A1:D100 and the range selected, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:

With the selected range beginning at Compare!A1 and the comparison labels in that range:

=A1="Different"

Choose a format and confirm the rule. The relative reference adjusts through the selected range, highlighting cells whose result is Different. This approach formats the comparison output rather than directly comparing two worksheets inside the conditional-formatting rule, which avoids complications with cross-worksheet references in such rules.

Find missing records when row order changes

A cell-by-cell comparison assumes the records line up in the same order. If a row was inserted or the lists were sorted differently, corresponding records may no longer occupy the same row, and a direct comparison can show many misleading differences. In that situation, compare by a unique identifier, such as an order ID, rather than by row position.

For a simple check, assume IDs are in Sheet1!A2:A100 and Sheet2!A2:A100, and enter a result beside the first ID on Sheet1. This formula checks whether the ID in Sheet1!A2 appears anywhere in Sheet2’s list:

With IDs in Sheet1!A2:A100 and Sheet2!A2:A100, and the formula entered beside Sheet1!A2:

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Found","Missing")

If A2 contains an ID that occurs at least once in Sheet2’s range, the result is “Found”; otherwise it is “Missing.” The dollar signs keep the lookup range fixed as you fill the formula down. This tests presence, not equal counts: if an ID appears twice in one list and once in the other, each occurrence may still be marked Found. COUNTIF is also case-insensitive and treats asterisks and question marks in criteria as wildcard characters, so it is not a strict character-for-character test for every possible ID.

Common causes of unexpected results

  • Misaligned rows: The basic formula compares positions, not matching names or IDs. Sort both sheets consistently or use a key-based lookup when order can differ.
  • Numbers stored as text: A value that looks like a number may have been imported as text. Check the cell format and data source, and standardize the data before comparing if the distinction matters.
  • Hidden characters: Leading or trailing spaces, nonbreaking spaces, and line breaks can make text differ even when it looks similar. Clean imported text deliberately rather than assuming TRIM removes every character.
  • Errors in source cells: If a compared cell contains an Excel error, the comparison can return that error rather than a match label. Resolve or handle the errors separately if the result sheet must remain error-free.
  • Formatting differences: A value formula will not detect a red fill, changed number format, or different font. Use a formatting comparison method if appearance is what matters.
  • Rounded display: Two numeric values can look identical because the cells display fewer decimal places, even though their stored values differ. Increase displayed precision or use an appropriate tolerance when small numeric differences are acceptable.

For numeric measurements where tiny differences are expected, decide on a tolerance rather than comparing for exact equality. For example, if both cells contain numbers, a difference smaller than 0.01 can be treated as a match. With numeric values in Sheet1!A1 and Sheet2!A1 and a result in a third sheet’s A1, enter:

=IF(ABS(Sheet1!A1-Sheet2!A1)<0.01,"Match","Different")

If the values are 12.345 and 12.349, their absolute difference is 0.004, so this formula returns “Match.” The 0.01 threshold is only an example, not a universal rule; choose a tolerance that fits the units and purpose of the data. This formula is for numeric cells, not text or error values.

When a formula is not the best comparison method

Formulas are a good choice for a repeatable comparison of aligned values, especially when you want results to update as source data changes. For a one-time review of two large, differently structured workbooks, manual formulas may be cumbersome. Excel’s View Side by Side can help you inspect two open workbooks visually, but it does not produce a cell-by-cell difference report.

Power Query can help clean, reshape, and merge tables using common columns, which is useful when records are not in the same order. For comparing workbook structure, formulas, or formatting in depth, Microsoft offers Spreadsheet Compare in certain Office editions; availability depends on the installed edition and configuration. These tools serve different needs: a formula is transparent and easy to customize, while comparison and data-preparation tools can be more practical for complex workbooks.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. Enter a comparison formula in a results sheet and fill it across or down. The basic formula compares matching cell positions; for records in different orders, compare using a unique ID instead.

How do I compare two sheets and show only differences?
Return “Different” for unequal values, then turn on Filter for the results range and filter that column to show only Different. Alternatively, apply Conditional Formatting to make those results stand out.

Does the formula compare formulas or their results?
The basic equality formula compares the values produced by the cells, not the text of the formulas. Two different formulas can therefore match if they currently return the same value. A value comparison also does not check cell formatting.

Can I compare worksheets with different names?
Yes. Replace Sheet1 and Sheet2 in the example with the actual tab names. If a name contains spaces, surround it with single quotation marks, as in ‘January Sales’!A1.

Why does a formula show a difference when the cells look the same?
Possible causes include hidden spaces or characters, text-versus-number differences, or extra decimal places that are not displayed. Check the actual cell contents and stored precision, and clean or standardize the data only when doing so matches your comparison goal.

Related

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.