Can You Compare Two Excel Sheets? Methods That Work

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, you can compare two sheets cell by cell, check whether rows or IDs appear in both, highlight differences with formulas, or use Power Query for larger tables. The best method depends on whether the data is in the same order, whether you need to compare values or formulas, and how much of the comparison you want to automate.

What “compare two Excel sheets” can mean

A comparison can answer several different questions, and choosing the right one matters. If two sheets have the same layout and row order, a cell-by-cell check can show which positions differ. If rows may be sorted differently, you should match records by a stable identifier, such as an order number or employee ID. If you need a summary of additions, deletions, and changed fields, a query-based comparison is often more useful than a single formula.

It also helps to decide what counts as a difference. Two cells might display the same text but contain different underlying values, such as a date stored as text on one sheet and as a date serial number on the other. Conversely, two cells can contain the same value but look different because one has different number formatting. You may want to compare:

  • Values: the text, numbers, dates, or results shown in cells.
  • Formulas: the expressions used to calculate those results.
  • Structure: added or missing rows, columns, or worksheet content.
  • Formatting: fonts, fills, number formats, and other appearance settings.

Most everyday comparisons focus on values and records. A formula-based comparison normally checks the results stored in cells, not whether the formulas that produced them are identical.

Compare matching cells when the layouts are identical

When each row and column represents the same thing on both sheets, place a comparison formula in a third sheet or in an unused area. For example, with corresponding values in Sheet1!A2 and Sheet2!A2, enter =IF(Sheet1!A2=Sheet2!A2,"Same","Different") in a comparison cell. If Sheet1!A2 contains North and Sheet2!A2 contains South, the result is Different; if both contain North, it is Same. Fill the formula across and down to check a larger matching range.

This method is quick, but it assumes the row and column positions correspond. If a row was inserted in one sheet, a comparison may report many differences even when most records are still present. It can also miss the reason for a mismatch: a result of Different does not tell you which value is correct or whether the row itself was matched properly.

Excel’s standard equality comparison is not case-sensitive for text, so “north” and “North” are generally treated as equal by the formula above. If capitalization matters, use EXACT instead. With the same layout and values in Sheet1!A2 and Sheet2!A2, the formula =IF(EXACT(Sheet1!A2,Sheet2!A2),"Same","Different") returns Different for “north” versus “North” and Same when the text matches exactly. EXACT compares text case-sensitively; it is not a general test of formatting.

Find records that are missing or added

If the sheets may be sorted differently, compare a key column rather than matching rows by position. A key should identify a record reliably: an invoice number is usually better than a customer name, because names can repeat or change. Suppose Sheet1 has IDs in A2:A500 and Sheet2 has IDs in A2:A500. In a helper column on Sheet1, use =IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$500,A2)=0,"Missing from Sheet2","Found")). If A2 contains ID 1042 and that ID is absent from Sheet2’s range, the result is Missing from Sheet2; if it appears there, the result is Found. Fill the formula down to check the rest of Sheet1.

To find records that exist only in Sheet2, reverse the check: enter an equivalent formula on Sheet2 and point COUNTIF at Sheet1’s ID range. Checking in both directions is important. A one-way check can identify records missing from the second sheet but cannot reveal records that were added only to the second sheet.

Blank keys need care. The formula above leaves a blank key blank rather than treating it as a record, but it does not resolve duplicate IDs. If an ID can appear more than once, decide whether the comparison should check only for presence or should also compare the number of occurrences. A key that is not unique may make a record-level comparison ambiguous.

Compare records using more than one field

Sometimes no single column uniquely identifies a row. A combination of fields, such as customer ID and invoice date, may act as a composite key. With customer IDs in column A and invoice dates in column B on both sheets, and the record to check in Sheet1 row 2, enter =IF(COUNTIFS(Sheet2!$A$2:$A$500,A2,Sheet2!$B$2:$B$500,B2)=0,"Missing","Found") in a helper column on Sheet1. If the pair in A2 and B2 does not occur together in Sheet2, the result is Missing; if the pair is present, it is Found.

COUNTIFS checks that both conditions are true on the same row. That distinction matters: finding a customer ID somewhere and a date somewhere is not enough if they belong to different records. After confirming that a record exists, you can compare its other fields, such as amount or status. If those fields are not in corresponding positions, first match the records by their key rather than comparing row numbers directly.

Composite keys can still be imperfect. If dates contain times, two entries that display the same date may have different underlying time values. If text fields contain leading or trailing spaces, apparently identical keys may fail to match. Clean or standardize the source data before drawing conclusions from a comparison.

Highlight differences with conditional formatting

Color can make a comparison easier to scan, especially when there are many cells. One dependable approach is to calculate the results in helper cells first, then apply conditional formatting to those results. Select the helper range and choose Home > Conditional Formatting > Highlight Cells Rules to create a rule for text that contains “Different,” or use Home > Conditional Formatting > New Rule to set a custom format.

For a same-position comparison, you can also put formulas such as the cell-by-cell IF check in a comparison grid and format the results. This keeps the comparison logic visible and avoids relying on color alone. Color is a display aid, not a record of what changed: keep the formula or a separate report if you need to review, share, or audit the findings later.

Conditional formatting has a practical limitation: a rule generally cannot use a direct reference to a different worksheet in the same way a normal worksheet formula can. A helper comparison range on the current sheet is often simpler than trying to build a cross-sheet conditional-formatting rule. Also remember that a format difference is not the same as a value difference. A red fill on one sheet and a green fill on another will not be detected by the equality formulas above.

Use Power Query for larger or repeat comparisons

Power Query is useful when you need to compare substantial tables, repeat the task, or produce a list of unmatched and changed records. In Excel for Microsoft 365 on Windows, load each table into Power Query from Data > Get Data > From File > From Workbook if the data is in separate workbooks. For tables already in the current workbook, use the available options on the Data tab to create queries from the workbook data. The precise import options can depend on how the source is stored.

In the Power Query editor, choose Home > Merge Queries, select the two queries, and select the matching key column in each. Choose a join type based on what you need. A left anti join returns rows from the first query with no match in the second; a right anti join does the reverse. A full outer join includes rows from both sides, including unmatched records, and can help build a comprehensive comparison. For matching rows, expand the second query’s fields and compare the paired columns to identify changed values.

Power Query is especially helpful for recurring work because you can refresh the query after replacing or updating the source data. Still, it needs a sound matching key. Duplicate keys can create multiple joined combinations, and mismatched data types can prevent expected matches. Check the query’s row counts and inspect sample matches before treating the output as definitive. Power Query features and data-source support vary across platforms; the Windows desktop steps described here may not be available in the same form in Excel for the web or on Mac.

Compare two open workbooks side by side

If you want to inspect two sheets visually rather than calculate a difference report, open both workbooks and use View > View Side by Side. This displays the workbook windows next to each other; when useful, turn on Synchronous Scrolling so scrolling one window also scrolls the other. The feature is for viewing, not automatic matching: it will not flag changed cells or account for rows that have moved.

Side-by-side viewing works best when the sheets have similar layouts and the range is small enough for a person to inspect. It can be paired with a formula-based comparison or a report for greater confidence. On a Mac, window arrangement and the available View controls can differ with the Excel version and screen setup, so use the corresponding View options in the desktop app. Excel for the web may arrange multiple browser windows differently from the Windows desktop interface.

Common problems and ways to avoid false differences

A comparison can be technically correct but still misleading if the source data is inconsistent. Before comparing, check that the intended columns have matching data types and that both tables cover the same time period. Keep an untouched copy of the source data so that cleanup does not erase evidence of the original values.

  • Rows are out of order: Match by a stable ID or key instead of comparing the same row numbers.
  • Duplicate or missing IDs: Confirm whether the key should be unique and decide how duplicates should be handled.
  • Spaces or inconsistent text: Check for extra spaces, inconsistent punctuation, and alternate spellings before treating entries as different.
  • Dates or numbers are stored differently: A text date and a real Excel date may look alike but compare differently.
  • Displayed values are rounded: Two numbers may display the same rounded amount while their underlying values differ.
  • Formulas differ but results match: A value comparison will not reveal different formulas that currently return the same result.

For financial or operational data, decide whether you need exact equality or an allowed tolerance. Do not silently round or ignore small differences: document the rule and verify that it suits the purpose of the comparison. If the workbook contains confidential information, prefer Excel’s built-in tools or an approved organizational service rather than uploading it to an unfamiliar online comparison website.

Which comparison method should you choose?

Use the simplest method that answers the question accurately. A small, fixed layout is often best checked with a comparison formula. A list of records with a reliable ID is usually best checked by key, with checks run in both directions. A repeated reconciliation involving multiple columns is a strong candidate for Power Query. Side-by-side viewing is useful for manual review, but it should not be mistaken for an automated comparison.

Before sharing a result, include enough context for someone else to reproduce it: which sheets or files were compared, which columns formed the key, whether values or formulas were checked, and how blanks and duplicates were treated. For a recurring process, save the comparison workbook or query and record the date and source files. That makes it easier to distinguish a genuine data change from a changed method or mismatched source.

Frequently Asked Questions

Can Excel automatically compare two sheets?

Excel can compare them using formulas, conditional formatting based on helper results, or Power Query. It does not automatically know whether rows should be matched by position or by an ID, so you need to choose the comparison logic.

Can I compare two sheets in different workbooks?

Yes. You can reference cells in another open workbook with formulas, use Power Query to load and merge tables, or open both workbooks side by side for visual review. For repeated comparisons, Power Query is often easier to refresh than rebuilding formulas.

How do I find rows that are in one sheet but not the other?

Use a unique identifier and check it against the other sheet’s ID column, then repeat the check in the opposite direction. This reveals both records missing from either side. If the identifier is not unique, account for duplicates or use a combination of fields.

Does a cell comparison check formulas or displayed results?

A standard equality formula compares the cell values, including the results of formulas, rather than comparing the formula text. Two different formulas can therefore compare as equal if they currently return the same value. Formatting is not checked by a basic value comparison either.

Why does Excel say two cells are different when they look the same?

Common causes include extra spaces, text stored instead of a number or date, hidden decimal places, and different underlying time values. Inspect the cell contents and data types before changing the comparison rule; the display format can conceal a real difference.

Can I compare sheets with different row orders?

Yes. Compare a stable key such as an order ID, not the row positions. For more than one matching field, COUNTIFS or a Power Query merge can match records using multiple columns, provided the selected fields identify the records reliably.

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.