Compare Two Sheets in Excel: Methods That Work

How-to basics · Updated November 21, 2026

To compare cells in the same positions, put this formula in a third sheet; with the values to compare in Sheet1!A1 and Sheet2!A1, use =IF(Sheet1!A1=Sheet2!A1,"Same","Different"). For lists whose rows may be in a different order, compare a unique ID or other key instead; Power Query is useful for larger or repeat comparisons.

Choose a comparison method first

The right method depends on what “compare” means for your workbook. If two sheets have the same layout and corresponding items occupy the same cells, a cell-by-cell formula is quick and transparent. If records can move between rows, match them by a stable identifier such as an invoice number or product code. If you need a repeatable report across large tables, Power Query can combine and compare the data without manually checking every row.

Before comparing, check that both sheets represent the same time period and use compatible layouts. A new column inserted in one sheet can make a position-by-position comparison misleading: values may differ simply because they no longer refer to the same field. Decide whether the goal is to find changed values, missing records, new records, or differences in formulas. Those are related but distinct questions, and a single formula may not answer all of them.

Compare matching cells in the same positions

For two worksheets with aligned rows and columns, enter a comparison formula in a third sheet or in a spare column. With Sheet1 and Sheet2 containing corresponding values in A1, the formula from the opening paragraph returns “Same” when Excel considers the values equal and “Different” otherwise. Fill it across and down to compare a range. You can also refer directly to the sheets with spaces in their names by enclosing each name in single quotation marks, such as 'January Data'!A1.

For a visual check, a simpler formula can return TRUE or FALSE instead of labels. With the same layout—Sheet1!A1 and Sheet2!A1 holding the values—use =Sheet1!A1=Sheet2!A1. A result of TRUE means Excel evaluates the two cell values as equal; FALSE means it does not. This is useful when you plan to filter a comparison column or count the mismatches later.

One important detail: ordinary Excel equality comparisons are not case-sensitive for text. For example, “north” and “North” are treated as equal by the basic comparison. If capitalization itself matters, use a case-sensitive check. With text in Sheet1!A1 and Sheet2!A1, enter =IF(EXACT(Sheet1!A1,Sheet2!A1),"Same","Different"). EXACT is designed to distinguish text capitalization, so use it when case is part of the data standard rather than just a display preference.

Blank cells are another edge case. Two genuinely empty cells compare as equal, and a blank cell can also behave like a zero in some comparison contexts. If one sheet uses formulas that return an empty string while the other has genuinely empty cells, the results may not match your expectations. Test a few known examples before filling a formula across a large range.

Highlight differences without changing the source data

A formula result is easy to filter, but a colored comparison sheet can make exceptions quicker to spot. A reliable approach is to put comparison results in a helper range, then apply conditional formatting to that range. Select the helper cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then enter “Different” and choose a format. The helper range remains separate from the two source sheets, so the original data is not overwritten.

You can also use a formula-based conditional formatting rule. If comparison labels begin in C2, select the relevant helper range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the first selected cell at C2, enter =C2="Different" as the rule formula and choose a fill color. Excel adjusts the relative reference for the other cells in the selected range. This highlights the comparison results; it does not itself compare the source worksheets.

For large grids, consider returning TRUE/FALSE in the helper range and filtering for FALSE, or use a count to summarize the number of differences. With comparison results in C2:C100, use =COUNTIF(C2:C100,"Different") to count the labeled mismatches. This returns the number of cells marked Different, not the number of rows containing at least one difference. If a row can have several mismatches, count rows separately or create a row-level result.

Compare records when their row order can change

Position-by-position comparison is wrong when a record can move to a different row. In that case, match rows using a unique key, such as a customer ID. Suppose the old list has IDs in A2:A100 and amounts in B2:B100, while the new list has matching IDs and amounts in New!A2:A100 and New!B2:B100. With the old sheet’s row 2 as the current record, this formula checks whether its ID exists in the new list and whether the amount changed: =IFERROR(IF(B2=XLOOKUP(A2,New!$A$2:$A$100,New!$B$2:$B$100),"Same","Changed"),"Missing from New").

Fill that formula down the old list. For example, if ID 104 is present in both sheets and its old and new amount are 25, the result is “Same”; if the new amount is 30, it is “Changed”; and if ID 104 is absent from the new range, it is “Missing from New.” XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It returns the first match, so the key should be unique; duplicate IDs need to be resolved or compared with a more specific key.

To identify records that are new rather than missing, reverse the check: evaluate each ID on the New sheet against the old ID range. With the new record’s ID in New!A2 and old IDs in A2:A100 on the old sheet, use =IF(COUNTIF($A$2:$A$100,New!A2)=0,"New record","Already existed"). COUNTIF is available in current Excel versions. This checks existence only; it does not compare the other fields in a record.

Keys should be stable and consistently stored. An ID stored as the number 104 in one sheet and as text “104” in another may not match as intended. Leading zeros can matter too: codes 00104 and 104 may represent different identifiers even though they look related. Decide whether to preserve leading zeros, remove extra spaces, or standardize data types before relying on a match. Avoid using a person’s name alone as a key if names can repeat or change.

Check formulas, displayed values, and formatting

A normal cell comparison checks the values Excel evaluates, not whether the underlying formulas are written the same way. For instance, one sheet might calculate a total with a SUM formula and another might contain the same total typed as a fixed number. Their displayed values can agree even though one will update automatically and the other will not. Conversely, formulas can differ while currently returning the same result.

If you need to compare formulas, inspect the formula bar, use Excel’s Show Formulas command on each sheet, or create a separate audit that compares formula text. The Show Formulas command is on the Formulas tab in the Formula Auditing group; the Windows shortcut is Ctrl+` (the grave accent key). On Mac, the shortcut is Control+` in many Excel configurations, though keyboard layouts can affect the key combination; the ribbon command is an alternative. Remember to turn Show Formulas off to return to the normal display.

Formatting is a separate kind of difference. Two cells can contain the same value but use different number formats, fills, fonts, or borders. Cell equality formulas do not report those changes. If format differences matter, inspect the cells directly or use a comparison tool that reports formatting. Also distinguish the stored value from what is displayed: a date may be a serial number formatted as a date, and a rounded display can hide extra decimal places that affect calculations.

Use Power Query for repeatable table comparisons

Power Query is a practical option when you regularly compare sizable tables or need a refreshable list of additions and removals. In Excel for Microsoft 365 on Windows, convert each source range into a table if it is not already one, then load it with Data > From Table/Range. In the Power Query Editor, use Home > Merge Queries, choose the two tables and their matching key columns, and select a join type appropriate to the question.

A Full Outer join keeps matching and unmatched records from both tables, which is useful for finding additions and removals. A Left Anti join returns rows from the first table that have no match in the second; switching the table order checks the opposite direction. A regular matching join can help compare fields for records that exist in both tables. After merging, expand the matched columns and compare the fields you care about, then select Home > Close & Load to return the result to Excel. Use Data > Refresh All to update the query after the source tables change.

Power Query does not make a poor key reliable: duplicate or inconsistent IDs can still produce confusing results. Confirm that the chosen key identifies one record, and check how nulls and data types are handled. Power Query’s interface and available connectors can differ between Windows, Mac, and Excel for the web, so the steps above describe the Windows desktop workflow. If you only need a one-time check of a small, aligned range, formulas are usually easier to set up.

Other comparison tools and their limits

Some Windows editions of Office include the Spreadsheet Compare application, which can report differences between workbook files, including changes to formulas and formatting. Availability depends on the Office edition and installed components. If it is available, search the Windows Start menu for “Spreadsheet Compare,” open it, and choose the files to compare. In some installations, the Inquire add-in provides access through an Inquire tab and its Compare Files command; the add-in may need to be enabled through File > Options > Add-ins > COM Add-ins > Go.

This is different from a formula on two worksheets in one workbook: Spreadsheet Compare is intended for comparing workbook versions and can show a broader set of changes. It is not available in every Excel edition, and this Windows desktop feature is not a general Excel for the web or Mac workflow. If you cannot find it, use formulas or Power Query rather than assuming a missing menu means the workbook is damaged.

For a small one-off task, copying the two ranges side by side and using a formula is often the simplest option. For an ongoing monthly process, Power Query is easier to refresh consistently. For investigating workbook-level changes such as formulas, formatting, or cell edits across versions, Spreadsheet Compare may be a better fit when your installation includes it.

Common problems and how to avoid them

  • Rows are sorted differently: Match records by a unique ID instead of comparing the same row numbers.
  • One range is shorter: Extend the comparison to cover all used rows, or identify missing records with an existence check.
  • Spaces or hidden characters differ: Check for leading or trailing spaces and nonprinting characters. Do not clean data automatically if those characters might be meaningful.
  • Duplicates exist: A lookup may return only the first matching record. Verify key uniqueness or compare using multiple fields.
  • Values look identical but differ: Check number formats, decimal precision, dates, and whether one value is stored as text.
  • The comparison says Same for different capitalization: Use EXACT when case-sensitive text comparison is required.

For a useful audit, keep source data unchanged, put results in a separate column or query output, and label what each result means. A “Same” result should not be interpreted as proof that every aspect of two records matches unless the method checks every relevant field. For example, a key-based formula that checks only the amount says nothing about whether the address or status also changed.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. A formula can compare matching cells, and Power Query can refresh a comparison of tables. Choose the method based on whether rows align, whether records need matching by key, and whether you need a one-time result or a repeatable process.

How do I find values that are in one sheet but not the other?
Check each row’s unique ID against the other sheet’s ID range with COUNTIF or a lookup. Run the check in both directions: one pass finds records missing from the second sheet, and the reverse finds records missing from the first.

Can I compare two Excel sheets side by side?
Yes. You can view two worksheets in the same workbook at once using View > New Window, then View > View Side by Side. This helps visual inspection but does not identify differences automatically; use formulas or a comparison tool to flag them.

Does Excel treat uppercase and lowercase text as different?
Ordinary equality comparisons are not case-sensitive. Use the EXACT function when capitalization differences should count as a mismatch.

Can Excel compare sheets with different row orders?
Yes, but compare by a stable key such as a unique record ID rather than by cell position. XLOOKUP or Power Query can match records across differently sorted lists.

Will a comparison formula detect formatting changes?
No. A regular formula compares cell contents or evaluated values, not fills, fonts, borders, or number formats. Use direct inspection or a suitable workbook comparison tool if formatting changes matter.

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.