Compare Similarity Between Two Excel Sheets

How-to basics · Updated November 18, 2026

To compare two Excel sheets, first decide whether the same cells should match or whether records may appear in a different order. For sheets with the same layout, use a comparison formula such as =IF('Sheet 1'!A1='Sheet 2'!A1,"Match","Different"); for reordered records, match rows using a unique key with XLOOKUP or Power Query.

What Does Similarity Between Sheets Mean?

“Similarity” can mean several different things, and the best comparison depends on which one matters. Two sheets might contain the same data in the same cells, the same records in a different order, or broadly similar values that differ only within an acceptable tolerance. A comparison can also focus on formulas, formatting, or the structure of the worksheets rather than their displayed values.

Before comparing, define what counts as a match. For example, should “North” and “north” count as equal? Should an empty cell match a cell containing zero, or a formula that displays nothing? Should amounts that differ by a fraction of a cent be flagged? These choices affect the result, so a similarity score is meaningful only when its rules are clear.

For most routine checks, start with the data itself. Compare formulas if you need to confirm calculations, and inspect formatting separately if colors, number formats, or layout are part of the requirement. A cell-by-cell check cannot tell you whether two differently ordered lists contain the same records.

Choose a Comparison Method

Use the method that fits the structure of your workbooks:

  • Same rows and columns: Compare corresponding cells with a formula. This is usually the quickest option for monthly reports or two versions of a template.
  • Same records, different order: Match records by a stable identifier, such as an invoice number or product ID, then compare the fields.
  • Repeated or larger data refreshes: Use Power Query to join tables on one or more keys and inspect unmatched or changed records.
  • Visual review: Put the sheets side by side or use conditional formatting to make differences stand out.
  • Formatting or workbook structure: Use a dedicated comparison tool if available, because ordinary cell formulas do not compare every workbook feature.

Check that both ranges cover the intended records before choosing. A formula comparing A1:D100 cannot identify a new record in row 101, and a row-by-row check will report apparent changes if one list has been sorted differently.

Compare Cells in the Same Positions

For a direct comparison, create a third worksheet called “Compare” and enter a formula in A1. This example assumes the sheets are named Sheet 1 and Sheet 2 and the data in both begins at A1. The formula treats two truly empty cells as a match and distinguishes an empty cell from zero:

=IF(AND(ISBLANK('Sheet 1'!A1),ISBLANK('Sheet 2'!A1)),"Match",IF(AND(NOT(ISBLANK('Sheet 1'!A1)),NOT(ISBLANK('Sheet 2'!A1)),'Sheet 1'!A1='Sheet 2'!A1),"Match","Different"))

If A1 contains 125 on both sheets, the result is “Match.” If Sheet 1 A1 is empty and Sheet 2 A1 contains 0, the result is “Different.” Copy the formula across and down to cover the full comparison range. Excel adjusts the cell references as the formula is filled. If a sheet name contains spaces, keep its single quotation marks as shown.

A simpler formula is adequate when the distinction between an empty cell and zero does not matter: =IF('Sheet 1'!A1='Sheet 2'!A1,"Match","Different"). Direct equality is not case-sensitive, so text such as “ABC” and “abc” is treated as equal. If capitalization matters, use EXACT instead. With the same sheet names and A1 layout, =EXACT('Sheet 1'!A1,'Sheet 2'!A1) returns TRUE for identical text, including capitalization, and FALSE for “ABC” versus “abc.”

After filling the comparison grid, filter it for “Different” or use Find to locate those results. You can also count the differences with a COUNTIF formula, but only if you have chosen a comparison range and a formula output that labels differences consistently. The comparison grid is an audit aid: it does not change either source sheet.

Highlight Differences Visually

Conditional formatting can make differences easier to scan, but a rule that compares two ranges on different worksheets may require extra setup. A reliable simple approach is to copy the two data ranges onto the same comparison sheet, placing the first range in A1:D100 and the second in F1:I100. Select A1:D100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

With the first range in A1:D100 and the corresponding second range in F1:I100, use this rule formula: =A1<>F1. Choose a format, such as a fill color, and confirm the rule. Excel adjusts the relative references across the selected range, so A1 is compared with F1, B1 with G1, and so on. This works only when the ranges are aligned; it does not find a matching record that has moved to another row.

On Windows, the formula-entry shortcut is Ctrl+Shift+Enter only for some legacy array formulas; it is not needed for the comparison formulas above. On Mac, the corresponding shortcut for selecting a range is generally Command-based rather than Ctrl-based, but the ribbon path is available in Excel for Mac as well. Menu labels can vary slightly by release.

Compare Records When Row Order Changes

If rows may be sorted differently, use a key that identifies each record. An invoice ID, for example, is more useful than a row number. The following formula assumes Sheet 1 has IDs in A2:A100 and amounts in B2:B100, while Sheet 2 has the corresponding IDs in column A and amounts in column B. Enter it in C2 on Sheet 1 and fill down:

=IFERROR(IF(B2=XLOOKUP(A2,'Sheet 2'!$A:$A,'Sheet 2'!$B:$B),"Match","Different"),"Missing ID")

If ID 104 has an amount of 18 on Sheet 1 and 20 on Sheet 2, the result is “Different.” If ID 104 is not found on Sheet 2, the result is “Missing ID.” XLOOKUP is available in Microsoft 365 and Excel 2021 or later. By default, it looks for an exact match and returns the first matching ID.

Check for duplicate keys before trusting this result. If an ID appears more than once, XLOOKUP returns the first match, which may hide other differences. Also compare in both directions: checking every ID on Sheet 1 against Sheet 2 will not reveal IDs that exist only on Sheet 2. For a small list, repeat the check in reverse; for larger tables, a Power Query merge can help expose records missing from either side.

Compare Larger Tables with Power Query

Power Query is useful when you need to repeat a comparison as new versions of a workbook arrive. In Excel for Microsoft 365 on Windows, you can use Data > Get Data > From File > From Workbook to bring each workbook or table into Power Query. Load the relevant sheets or named tables, then use Home > Merge Queries to join them using the key column or columns.

Choose a join type based on the question. An inner join keeps records with keys in both tables; a left outer join keeps all records from the first table and matching records from the second. To identify entries found on only one side, use an appropriate outer-join approach and inspect the resulting null values. Expand the fields you want to compare, then load the result back into Excel. The query can be refreshed when the source data changes.

Power Query compares fields you bring into the query, not every workbook detail. It is not a direct check of cell formatting, comments, or formulas as written. In Excel for Mac, Power Query availability and features differ by version; Excel for the web does not provide the same full desktop query-editing experience. If your version lacks a needed command, use formulas or compare the data in a supported desktop version.

Some Windows desktop editions also include Spreadsheet Compare, commonly accessed through the Inquire add-in. Availability depends on the Office edition and whether the add-in is enabled. It can be useful for examining workbook-level changes, but it is not present in every Excel installation and is not a feature of Excel for the web.

Set Rules for Numeric Similarity

Exact equality can be too strict for calculated numbers. Floating-point calculations may produce tiny differences, and measurements may reasonably be considered equal within a chosen tolerance. Decide that tolerance from the meaning and precision of the data rather than selecting a threshold just to make the results look similar.

For numeric values in A1 on Sheet 1 and A1 on Sheet 2, this formula labels values within 0.01 as “Close”:

=IF(ABS('Sheet 1'!A1-'Sheet 2'!A1)<=0.01,"Close","Different")

For example, 10.004 and 10 differ by 0.004, so the formula returns “Close.” Values of 10.02 and 10 differ by 0.02, so it returns “Different.” This formula is for numbers; text or blank cells can produce misleading results or errors. Apply a numeric check to numeric fields and a separate comparison to text fields.

A percentage similarity score can also mislead if it treats every cell equally. A difference in a customer ID may matter more than a difference in a descriptive note, while blank cells may or may not belong in the denominator. If you report a score, state what was compared, how missing values were handled, and whether the score counts cells, rows, or fields. A list of specific mismatches is often more actionable than one percentage.

Prepare the Data and Avoid Common Errors

Before comparing, confirm that both ranges have the same headers and that the columns represent the same kinds of data. Check for leading or trailing spaces, inconsistent ID formats, duplicate keys, hidden rows, and formulas that display an empty string. A number stored as text may look identical to a number but behave differently in lookups and calculations.

  • Use stable, unique identifiers for record matching; do not rely on row position when the order can change.
  • Check date and number formats, but distinguish display differences from underlying value differences.
  • Decide how to treat blanks, zero values, capitalization, and acceptable numeric tolerances.
  • Compare formulas separately from results if you need to know whether the calculations themselves changed.
  • Keep an untouched copy of the source workbooks and verify the compared ranges include all data.

One common misconception is that two sheets that look alike are necessarily equal. Formatting can hide precision, and a displayed date may conceal a different underlying time. Conversely, different number formats can make the same stored value look different. Decide whether you need to compare displayed appearance, stored values, formulas, or all three.

Frequently Asked Questions

How can I compare two Excel sheets and show only differences?
For sheets with the same layout, fill a comparison formula across the shared range, then filter the results for “Different.” For differently ordered records, look up each row by a unique key and check for unmatched IDs as well as changed values.

Can Excel compare two sheets automatically?
Formulas, conditional formatting, Power Query, and—in some Windows editions—Spreadsheet Compare can help. Excel does not automatically know whether you mean cell values, formulas, formatting, or reordered records, so choose the comparison method and rules first.

How do I compare sheets if the rows are in a different order?
Match rows using a unique identifier, such as an order number, rather than comparing row 2 with row 2. XLOOKUP can retrieve the corresponding field in Microsoft 365 and Excel 2021 or later; Power Query is useful for repeatable table comparisons.

Does a normal Excel comparison distinguish uppercase and lowercase?
No. The equals operator is not case-sensitive. Use EXACT when capitalization matters; for example, it treats “North” and “north” as different text.

Can I compare two Excel sheets for a percentage match?
Yes, but define which cells or records count, how blanks are treated, and whether small numeric differences are acceptable. Without those rules, a percentage may conceal important missing records or high-impact mismatches.

Related

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.