To compare two Excel sheets, use a direct cell-by-cell formula when rows line up, or match records by a unique key when their order may differ. For a same-row check, with corresponding values in A2:C100 on Sheet1 and Sheet2, enter =IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Match","Different") in a result column on Sheet1 and fill it down.
Choose the comparison method first
The right method depends on what “compare” means for your workbook. You may need to find changed values in two aligned lists, identify records that exist in one sheet but not the other, or compare fields for records that have moved into a different order. These are different tasks, and a formula that works for one can produce misleading results for another.
- Same rows and columns: compare matching cells directly. This is quick when both sheets are sorted and structured identically.
- Same records, different order: match on a stable identifier, such as an employee ID or invoice number, then compare the fields attached to it.
- Find missing records: test whether each identifier on one sheet appears on the other.
- Large or recurring comparisons: consider Power Query, which can merge tables and show unmatched records.
Before comparing, check that each sheet uses the same definitions and units. A date stored as text on one sheet and as a date value on another, for example, may look identical while behaving differently in formulas. Keep an untouched copy of the source data before cleaning or sorting it.
Compare cells when the rows are aligned
A direct comparison is the simplest option if row 2 on the first sheet represents the same item as row 2 on the second, row 3 matches row 3, and so on. In the opening example, the formula checks three columns. It returns “Match” only when all three comparisons are true; otherwise it returns “Different.” Change the cell references and labels to suit your layout.
For a single field, with the values to compare in A2 on Sheet1 and A2 on Sheet2, enter this formula in a result cell on Sheet1:
=IF(A2=Sheet2!A2,"Match","Different")
For sheet names containing spaces, enclose the name in single quotation marks. With the comparison value in A2 on the current sheet and the other value in A2 on a sheet named Revised Data, use:
=IF(A2='Revised Data'!A2,"Match","Different")
Copy the formula down for all relevant rows. If you compare several columns, make sure to include every field that matters; a matching name does not prove that a matching address or amount is also correct. Direct comparisons are not suitable if either sheet has been independently sorted, because the formula compares positions rather than identifying the same real-world record.
Match records by a key when order differs
When rows can move, use a key that uniquely identifies each record. Examples include a product code, customer ID, or invoice number. Avoid relying on a person’s name alone if two people can share it. A key should be present on both sheets, consistently formatted, and ideally unique.
Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100; Sheet2 has the same fields in columns A and B. To compare each Sheet1 amount with the amount for the same ID on Sheet2, put this formula in C2 on Sheet1 and fill it down:
=IFNA(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different"),"Missing")
XLOOKUP finds the ID from A2 in Sheet2’s ID range and returns the corresponding amount. The outer IF compares that result with B2; IFNA displays “Missing” if the ID cannot be found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If an ID appears more than once, XLOOKUP returns the first matching result, so resolve duplicates or use a method designed to compare repeated records.
If the question is only whether an ID appears on the other sheet, with the ID in A2 and the comparison list in Sheet2!A2:A100, use:
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Found","Missing")
COUNTIF checks for at least one occurrence. It does not establish that the ID is unique or compare the other fields. Use it as an existence check, not as proof that the entire record matches.
Find records that are missing from either sheet
A comparison should usually be done in both directions. Checking every Sheet1 ID against Sheet2 identifies records missing from Sheet2, but it will not reveal IDs that exist only in Sheet2. Put a check beside each list, then repeat the check with the sheets reversed.
For example, with IDs in A2:A100 on Sheet1 and IDs in A2:A100 on Sheet2, place the COUNTIF check beside the first list and fill it down. Then place a corresponding check beside the second list, changing the range being searched to Sheet1. Filter each result column for “Missing” to review the unmatched records. If the lists are long, turn on filters with Home > Sort & Filter > Filter, or use the filter arrows if the data is already an Excel table.
Look at the key values before treating every unmatched item as a genuine omission. A leading or trailing space, a different data type, or an incorrectly entered ID can make two visually similar entries fail to match. Confirm the source record rather than simply changing a formula result to “Found.”
Review differences clearly with formulas and formatting
A helper column with clear labels is often more useful than a grid of TRUE and FALSE results. Labels such as “Match,” “Different,” and “Missing” are easy to filter, count, and explain to someone reviewing the workbook. Keep the helper column beside the data it describes, and give it a heading such as Comparison status.
To highlight those results, select the status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different and choose a format; repeat for Missing if needed. This approach formats the result labels without requiring a conditional-formatting rule to reference another worksheet directly.
For a detailed audit, add separate checks for important fields rather than combining every test into one opaque result. For instance, compare the amount, date, and status in separate columns. That makes it easier to identify exactly what changed and reduces the chance that a correct field hides an incorrect one.
Use Power Query for larger or repeat comparisons
Power Query can be useful when the source lists are large, refreshed regularly, or need a repeatable report of unmatched records. In Excel for Microsoft 365 on Windows, format each dataset as a table, select a cell in the first table, and choose Data > From Table/Range. Repeat for the second table, then load both into the Power Query Editor.
In the editor, choose Home > Merge Queries, select the two queries, and select the matching key column in each preview. A Left Anti join returns rows from the first table that have no matching key in the second. Run the comparison in the opposite direction to find rows unique to the second table. A Left Outer join keeps all rows from the first table and can bring in matching fields from the second for further comparison.
Power Query is a transformation and reporting tool, not a guarantee that the chosen key is correct. Check the selected columns, data types, and duplicate keys before relying on the output. When the source data changes, refresh the query to update the results. The Windows ribbon path and available connectors can vary by Excel build; Excel for Mac has Power Query support, but some capabilities differ from Windows, so check the commands available in that version.
Common comparison problems and their fixes
- Rows are in a different order: do not compare the same row numbers. Match on a stable identifier instead.
- Duplicate identifiers: a lookup may return only one matching row. Check for duplicates and decide whether the key needs another field, such as both invoice number and line number.
- Extra spaces: visually identical text can differ because of spaces. Inspect or clean the source consistently before comparing; do not remove spaces blindly if they are meaningful.
- Numbers stored as text: a value such as 00125 may be an identifier rather than the number 125. Decide whether leading zeros matter and standardize the data without losing them.
- Dates and amounts: confirm both sheets use the same date interpretation, currency, units, and rounding expectations. A displayed value may hide a difference in the underlying value.
- Case differences: ordinary Excel equality comparisons and common lookup comparisons are generally not case-sensitive. If capitalization matters, use a deliberate case-sensitive method rather than assuming a standard comparison checks it.
Another frequent misconception is that matching counts prove matching contents. Two sheets can have the same number of rows while containing different IDs or values. Compare identifiers and relevant fields, and check for duplicates, rather than relying only on row counts.
Frequently Asked Questions
How do I compare two Excel sheets and highlight differences?
For aligned rows, create a formula result for each row that says Match or Different, then apply Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to the result column. If the rows are not aligned, look up records by a unique key first.
How can I compare two sheets with different row orders?
Use a shared, unique identifier to find the corresponding record, then compare its fields. XLOOKUP can return a field from the second sheet for comparison in Microsoft 365 and Excel 2021 or later; check for duplicate IDs because a lookup may return just one match.
Can Excel tell me which rows are missing from another sheet?
Yes. Use COUNTIF to test whether each key appears in the other sheet, label absent keys as Missing, and repeat the test in the opposite direction to find omissions from both lists.
Why does Excel say two values are different when they look the same?
Possible causes include extra spaces, numbers stored as text, different date values, or hidden precision in amounts. Inspect the underlying values and standardize only after confirming what the data is meant to represent.
Can I compare two sheets automatically when data changes?
Formulas recalculate as their referenced data changes, while a Power Query comparison must generally be refreshed. For a recurring process, keep the source tables and query steps consistent, then refresh and review the updated results.
Related
- How to Paste as Plain Text in Excel Without Formatting
- How to Paste a Picture in Excel and Keep It in Place
- How to Open an XLB File in Excel (and What It Does)
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How Excel Goal Seek Works: Steps, Examples, and LimitsExcel Goal Seek works backward from a formula result: you specify the result you want, and Excel adjusts…
- Excel Text Box Wrap Text: How to Enable ItTo wrap text inside an Excel text box, select the text box, open Format Shape, choose Text Options…
- Parse Text to Columns in Excel: Steps and TipsTo parse text into separate Excel columns, select the cells, then choose Data > Text to Columns and…
- How to Set Out of Office in Outlook on Any DeviceTo set yourself out of office in Outlook, open Automatic Replies for the account you will be away…
- Power Query Data Transformation in Excel: A Practical GuidePower Query data transformation is the process of importing data into Excel, cleaning and reshaping it in the…
- P(X = x) in Excel: Calculate an Exact ProbabilityTo find an exact probability P(X = x) for a binomial variable in Excel, use BINOM.DIST with its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.