Microsoft Excel Compare Two Sheets: Methods That Work

To compare two Excel sheets, use a formula when matching cells occupy the same positions, or compare records by a unique key when rows may be arranged differently. Assuming the cells to compare are Sheet1!A2 and Sheet2!A2, enter =IF(Sheet1!A2=Sheet2!A2,"Match","Different") in a third sheet and fill it across and down.

Choose a comparison method

The right method depends on what “compare” means for your task. A workbook can contain two sheets with identical layouts, two lists whose rows are in a different order, or two versions of a complex workbook with changes to formulas and formatting. A quick visual check may be enough for a few values; a formula or query is more dependable for hundreds or thousands of records.

Same cells, same layout: use a comparison formula or conditional formatting.
Same records, different row order: match rows using a unique ID, then compare the fields.
Find missing or added records: use a keyed formula or Power Query.
Compare whole workbook versions: consider the Inquire add-in, if your Excel edition includes it.
Only need to inspect visually: open the sheets side by side, but remember that this does not flag differences automatically.

Before comparing, check that the sheets contain the same kind of data and that you know whether differences in capitalization, spaces, formulas, or formatting should count.

Compare cells in the same positions with a formula

A cell-by-cell formula is the simplest repeatable option when both sheets use the same layout. The first paragraph’s formula reports “Match” when the two referenced values compare equal and “Different” otherwise. Put it in a spare sheet or an unused area, then use the fill handle to copy it through the corresponding comparison range. For example, if the source data runs from A2 to D100 on both sheets, the copied formulas compare each corresponding cell in that rectangle.

If a sheet name contains spaces, surround it with single quotes in the reference. Assuming the values are in cell A2 on sheets named January Data and February Data, use =IF('January Data'!A2='February Data'!A2,"Match","Different"). Excel updates relative references as you fill the formula. To keep a row or column fixed while copying, use dollar signs, as in $A2 or A$2; this is useful when comparing a fixed lookup column against many rows.

For a plain TRUE/FALSE result, assuming the same corresponding-cell layout, enter =Sheet1!A2=Sheet2!A2. This can be useful in a large comparison grid because TRUE cells agree and FALSE cells do not. Excel’s ordinary equality comparison is not case-sensitive, so “North” and “NORTH” compare as equal. If capitalization matters, assuming the values are again in A2 on both sheets, use =IF(EXACT(Sheet1!A2,Sheet2!A2),"Match","Different"). EXACT treats text case as significant.

These formulas compare the results of cells, not necessarily the formulas that produced them. If two cells display the same result but contain different formulas, a value comparison will report a match. Formula differences are a separate check, discussed below.

Compare records when the row order differs

Comparing A2 with A2 is unreliable if someone sorted one sheet, inserted rows, or exported the records in a different order. In that situation, identify each record with a stable key, such as an invoice number or product ID. The key should be unique on both sheets; if it is duplicated, a lookup can return one matching row without proving that all duplicate records agree.

For example, assume Sheet1 has unique IDs in A2:A100 and amounts in B2:B100, while Sheet2 has IDs in A2:A100 and corresponding amounts in B2:B100. In C2 on Sheet1, enter =IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different"),"Missing from Sheet2"), then fill down. A matching ID and amount returns “Match”; a found ID with a different amount returns “Different”; an ID that is not found returns “Missing from Sheet2.” XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

This example checks whether each Sheet1 record exists on Sheet2 and whether its amount agrees. To find records present only on Sheet2, run a corresponding check in the other direction. Also compare the number of records and investigate duplicate keys: matching every unique ID in one direction does not by itself prove the two lists contain precisely the same records.

Highlight differences instead of building a report

Conditional formatting makes discrepancies stand out, but a rule applied to one sheet cannot simply use an ordinary direct reference to another sheet in its formula. A practical workaround is to create a comparison grid first. Assuming a helper sheet has the corresponding values from Sheet1 and Sheet2 in columns A and B, use =A2<>B2 in C2 and fill it through the grid. TRUE marks a difference and FALSE marks a match.

Select the helper results and choose Home > Conditional Formatting > Highlight Cells Rules to apply a visible format to TRUE values, or create a rule through Home > Conditional Formatting > New Rule. You can also display the compared values beside the TRUE/FALSE result so the reader can see what changed. This approach works well for a fixed comparison, while formulas or Power Query are usually easier to refresh when the source data changes often.

For a small number of cells, you can use Excel’s built-in highlighting on each sheet to mark duplicates or values, but that is not the same as comparing corresponding cells across two sheets. Make sure the visual highlight is driven by a comparison result rather than assuming that similarly colored cells are equal.

Compare workbook versions with Inquire

If the task is to compare two versions of a workbook, the Inquire add-in can provide a more detailed workbook comparison than a cell grid. In supported Windows desktop editions of Excel, enable it through File > Options > Add-ins. At the bottom, choose COM Add-ins in the Manage box, select Go, and check Inquire if it is listed. The Inquire tab includes Compare Files, which opens the Spreadsheet Compare tool for comparing two workbooks.

Availability depends on the Excel edition and installation; Inquire is not included in every version, and the Windows desktop workflow should not be assumed to exist in Excel for the web or on Mac. If the tab or command is missing, check your organization’s Office edition and available add-ins. Compare Files is designed for workbook versions, not as a substitute for choosing a key when two lists have different row orders. Save copies before reviewing or changing important files, and treat the comparison output as a report to investigate rather than an automatic correction.

Use Power Query for repeatable list comparisons

Power Query is useful when the lists are large, refreshed regularly, or need a clear inventory of records that were added, removed, or changed. On Windows, turn each source range into an Excel table, select a table, and choose Data > From Table/Range to load it into Power Query. Repeat for the second table. In the Power Query Editor, choose Home > Merge Queries, select the matching key column in each table, and choose a join type.

A full outer join keeps records from both tables, including those without a match, so it can reveal additions and removals. Expand the columns from the second table after merging, then compare the relevant fields or filter rows where one side is missing. A left anti join returns rows from the first table that have no matching key in the second; a right-side anti comparison can reveal the reverse. The exact labels and interface can vary somewhat by Excel version, but the core process is to load both tables, merge on a key, and inspect unmatched or changed fields.

Power Query is especially useful for repeatable work because you can refresh the query after replacing or updating the source tables. It does not automatically decide which differences matter: choose reliable key columns, confirm that data types match, and select the fields that should be compared. Excel for Mac and the web may have different Power Query capabilities and menus from Windows desktop Excel, so check the features available in your installation before designing a recurring process around it.

Common problems that create false differences

Two cells can look alike but contain different underlying values. A number stored as text, an extra space, a date with a different time component, or a formula that returns a slightly different decimal can all affect a comparison. If results seem unexpected, inspect the formula bar and check the data type and full value rather than relying only on the displayed formatting.

Leading or trailing spaces: imported text may contain invisible spaces. Decide whether they are meaningful before cleaning them; trimming can change data.
Numbers stored as text: a value such as “0042” may be an identifier, not the number 42. Do not convert it blindly if leading zeros matter.
Dates and times: cells may display the same date while one includes a time fraction. Compare the full underlying values if time matters.
Rounding: displayed numbers can look identical while their stored decimals differ. Decide whether exact equality or a defined tolerance is appropriate.
Blank cells and formulas returning empty text: a truly empty cell is not the same thing as a formula that displays an empty string. Check the source formulas when blank-looking results do not behave as expected.
Duplicates or unstable IDs: a lookup by a repeated key can match the wrong record. Find or construct a dependable unique key before comparing fields.

Another common misconception is that a matching cell address proves a matching record. It proves only that the values at those positions compare equal. If rows can move, compare by identity rather than position. Likewise, a formula match may mean only that the displayed results agree; it does not certify that the underlying calculations or formatting are identical.

Inspect changes visually with side-by-side windows

For a small workbook or an initial review, viewing the two sheets at once can make layout and context differences easier to spot. In Excel for Microsoft 365 on Windows, open another window of the workbook with View > New Window, then choose View > View Side by Side. You can use this for sheets in one workbook or for separate workbook windows. Scrolling can be synchronized while side-by-side viewing is active.

This is a visual aid, not an automatic comparison: it will not identify changed values, missing rows, or formula differences. It is best used alongside a formula-based report when accuracy matters. The available commands and layout can vary in Mac and web versions of Excel, so use the equivalent viewing options available in that version rather than assuming every Windows ribbon command is present.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Excel can calculate comparisons with formulas, highlight comparison results with conditional formatting, or use Power Query for repeatable list checks. The method still needs a clear rule for what counts as a match, such as equal cell positions or equal IDs and values.

How do I find values that are missing from one sheet?
Match records using a unique ID, then identify IDs that have no match on the other sheet. For larger or recurring comparisons, a Power Query anti join can list unmatched rows; for a simpler task, a lookup formula can label missing records.

Does Excel compare text with capitalization?
Ordinary equality comparisons are not case-sensitive. Use the EXACT function when “North” and “NORTH” must count as different values.

Can I compare two sheets with different row orders?
Yes. Do not compare row 2 with row 2 unless the rows are guaranteed to represent the same records. Match a unique key, then compare the corresponding fields.

Can Excel compare formulas rather than their results?
A standard cell comparison checks evaluated values, not whether the formulas are identical. For formula-level differences, use a workbook comparison tool such as Spreadsheet Compare when available, or inspect the formulas directly.

Is Spreadsheet Compare available in Excel for Mac or the web?
Do not assume so. The Inquire and Compare Files workflow described here is for supported Windows desktop installations, and availability depends on the Office edition. Check the features included with your version.

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.