Compare Two Excel Sheets and Find Every Difference

To compare two Excel sheets, use a cell-by-cell formula when the rows line up, or match records by a unique key when they may be in a different order. For a quick visual check, open both sheets side by side; for repeatable checks, use formulas, conditional formatting, or Power Query.

What It Means to Compare Excel Sheets

A comparison can answer several different questions, and choosing the right one matters. You might want to find cells with different values, spot rows missing from one sheet, identify changes to formulas, or check whether two whole tables contain the same records. These are not interchangeable tasks: a direct cell comparison assumes the same information is in the same position, while a record comparison needs a field that identifies each row.

Before comparing, decide what counts as a difference. Should extra spaces matter? Should a formula that returns the same value as another formula count as a change? Are two rows considered identical if their values match but their order differs? Write down the answer and, if other people will review the results, keep the original sheets unchanged. A comparison result is only meaningful when its rules match the question.

Quick Visual Check: View Sheets Side by Side

For a small workbook, a visual check is often the fastest starting point. In Excel for Microsoft 365 on Windows, open the workbook windows you want to inspect, then select View > View Side by Side. If more than two workbook windows are open, Excel asks which one to compare with the active window. You can scroll through both views to inspect matching areas, and View > Synchronous Scrolling can keep their scrolling positions together.

This method is useful for reading labels, checking a short report, or understanding the layout before building a more formal comparison. It does not automatically mark changes, prove that every cell matches, or align records that have moved. A different sort order can make two identical lists appear different. On Mac, use the View tab’s side-by-side viewing controls if available in your Excel version; labels and window arrangement can vary. Excel for the web has fewer desktop window-comparison controls, so formulas or a downloaded desktop workbook may be more suitable for a systematic check.

Compare Cells in the Same Positions with a Formula

If both sheets have the same structure and corresponding data sits in the same cells, a comparison formula is direct and easy to audit. For example, assume the worksheets are named Jan and Feb and the data occupies A1:F100. Put the check in a separate results sheet or in an unused column, then fill it across and down.

With the corresponding values in Jan!A1 and Feb!A1, enter this formula in the result cell for A1:

=IF(Jan!A1=Feb!A1,"Same","Different")

The formula returns “Same” when Excel considers the two cell values equal and “Different” otherwise. Copy it across and down to check the rest of the range; relative references adjust to each position. If worksheet names contain spaces, enclose each name in single quotation marks, as in 'January Data'!A1. You can replace the text results with TRUE and FALSE if another formula or a filter will use the results.

Equality comparisons ordinarily test the cell values, not whether the formulas that produced them are identical. For example, two different formulas can both return 10 and therefore be reported as matching. If you need to compare formulas themselves, inspect the formula bar or use a formula-text check for cells that contain formulas; handle cells without formulas separately. Also remember that a formula result can change when the workbook recalculates, so a comparison of calculated values is a snapshot of the current results.

Highlight Differences with Conditional Formatting

When the ranges have matching layouts, conditional formatting can color differing cells directly instead of creating a separate report column. Select the comparison range on the first sheet, such as A1:F100 on Jan, and choose Home > Conditional Formatting > New Rule. Choose the option to use a formula to determine which cells to format, enter the formula below, choose a fill or font format, and confirm the rule.

With Jan and Feb arranged identically and the selected range beginning at A1, use this conditional-formatting formula:

=A1<>Feb!A1

The relative references shift across the selected range, so B1 is compared with Feb!B1, and so on. If the second sheet’s name has spaces, write the reference as 'February Data'!A1. Check the rule’s “Applies to” range if highlighting seems offset. A rule applied to a range beginning at B2 must use references appropriate to B2, not A1.

Conditional formatting is convenient for review, but it is not a durable audit report by itself: colors can be missed, rules can be overwritten, and filters do not automatically explain why a cell differs. Consider adding a status column or copying the comparison results to a review sheet. Blank cells also deserve attention: two blanks compare as equal, while a blank and a cell containing a formula that returns an empty string may not behave as a reader expects in every test. Decide how those cases should be treated and verify them in your workbook.

Compare Records When Rows May Be Reordered

If one sheet is sorted differently from the other, compare by a stable, unique identifier such as an invoice number or employee ID. Do not use a person’s name alone if names can repeat. Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100, while Sheet2 has the same fields in columns A and B. In C2 on Sheet1, use this formula to look up each ID and compare its amount:

=IFERROR(IF(B2<>XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Different","Same"),"Missing from Sheet2")

For this layout, the result is “Same” when the amount for the matching ID is equal, “Different” when the ID exists but the amount differs, and “Missing from Sheet2” when the lookup cannot find the ID. For example, if ID 104 has an amount of 250 on Sheet1 and 275 on Sheet2, the result is “Different.” Fill the formula down the records. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older versions can use a lookup formula such as INDEX and MATCH, with care to make the missing-record test explicit.

This check only verifies records from Sheet1 against Sheet2. To find rows present in Sheet2 but absent from Sheet1, repeat the check in the other direction. XLOOKUP returns the first matching item, so duplicate IDs can hide additional records; confirm that the key is unique or use a method designed to compare repeated rows. If several fields together define a record, create a reliable combined key or compare the fields individually.

Use Power Query for Repeatable Table Comparisons

For recurring comparisons or larger tables, Power Query can load each table and join them using a key, producing a structured result that is easier to refresh than a large set of manually copied formulas. On Excel for Microsoft 365 for Windows, format each data range as a table with Insert > Table, then load each table through Data > From Table/Range. In the Power Query Editor, use Home > Merge Queries, select the matching key column in each table, and choose the join type that suits the question.

A full outer join keeps matching and nonmatching rows from both inputs, making it useful for identifying records that occur on only one side. Expand the joined columns and compare the fields you care about; a key match alone does not prove that the rest of the row matches. After shaping the result, choose Home > Close & Load to return it to Excel. When the source tables change, refresh the query. Exact command labels can differ slightly by Excel build, and Power Query capabilities differ across Windows, Mac, and web editions.

Power Query is especially helpful when the same comparison happens every month, but it requires more setup than a single formula. Check that both key columns use compatible data types and that leading or trailing spaces do not split what should be matching records. A merge can also produce multiple matches when keys are duplicated, so inspect key uniqueness before relying on a summary.

Common Comparison Problems and How to Avoid Them

Rows shifted or sorted differently: A cell-by-cell test reports positional differences even when records are the same. Match on a unique key instead.
Numbers stored as text: A value that looks like 100 may be numeric in one sheet and text in the other. Check number formats and underlying data types before treating the mismatch as a real business change.
Extra spaces or inconsistent capitalization: Names such as “North ” and “North” may appear alike but contain different text. Clean or normalize data only if the comparison rules allow it; preserve the original values for audit purposes.
Duplicate keys: A lookup may return one matching row and overlook another. Verify uniqueness or compare records with a method that accounts for duplicates.
Formula errors or changing results: A comparison can return an error if either source cell contains an error. Resolve or explicitly classify source errors, and recalculate before recording the result.
Precision differences: Calculations can produce tiny decimal differences that are not visible in the displayed format. If a tolerance is appropriate, define it deliberately rather than rounding away changes without review.

For sensitive or high-impact work, keep a copy of the original workbook, document the comparison method and date, and have someone review exceptions. A highlighted difference identifies a mismatch; it does not establish which value is correct or why it changed.

Choosing the Right Method

Use the simplest approach that answers the actual question. A side-by-side view is efficient for a short, informal review. A formula or conditional-formatting rule works well when ranges share the same layout. Key-based formulas suit moderate tables where records may be reordered, while Power Query is a stronger choice for repeated table comparisons and refreshable outputs. For a one-time comparison, avoid building a complex process unless you need its audit trail or repeatability.

Before sharing results, check coverage in both directions, confirm the formulas span the intended ranges, and test a known match, a known mismatch, and a missing record. Save the output separately from the source data and label what each status means. This makes the result understandable to another person and reduces the risk that a comparison is mistaken for a correction or approval.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. Use formulas or conditional formatting for cell-by-cell checks, and use Power Query for repeatable table comparisons. Excel does not automatically know whether you mean matching positions, matching records, values, or formulas, so set the comparison method to fit the data.

How do I compare two sheets and highlight differences?
For identically arranged ranges, select the first range and create a conditional-formatting rule using a formula such as =A1<>Feb!A1. Make sure the selected range starts at A1 and that the rule covers the full area to compare.

How can I compare sheets if the rows are in a different order?
Use a unique key to find the corresponding record on the other sheet, then compare the fields you need. Check both directions to find records missing from either sheet, and investigate duplicate keys before trusting lookup results.

Does a cell comparison check formulas or displayed values?
A standard equality formula compares the values available in the cells, including calculated results, rather than checking whether the formula text is identical. If formula changes matter, compare formula text separately and account for cells that contain constants instead of formulas.

Can I compare two Excel files, not just two sheets?
Yes. Open both workbooks and use formulas that reference the other workbook, or load their tables into Power Query. Keep both source files available while creating external references, and consider placing the comparison output in a separate workbook so the originals remain intact.

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.