To compare rows in two Excel sheets, compare corresponding cells when the rows are in the same order, or match records by a shared ID when the order can differ. In Microsoft Excel for Microsoft 365 on Windows, a formula such as =IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Match","Different") checks three cells in aligned rows; for reordered records, use a lookup keyed to the ID instead.
Choose the comparison that fits your data
The right method depends on what a row represents and whether the two sheets use the same order. A row-by-row comparison answers, “Are the values in row 2 on both sheets identical?” It is quick and easy to audit, but it can report differences simply because a record moved. A key-based comparison answers, “Does the record with this ID have the same details on both sheets?” That is usually the more reliable question for lists that have been sorted, filtered, or updated independently.
Decide whether you need to compare every field or only selected columns. For example, a customer export may contain an ID, name, email address, and export timestamp. If the timestamp changes on every export, comparing it may label otherwise unchanged customers as different. A comparison should include the columns that define a meaningful change, not automatically every column in the workbook.
- Same order and same row structure: compare cells at the same positions.
- Different order or inserted rows: match by a stable identifier such as an account number or product code.
- Need to identify records present in only one sheet: check each sheet’s IDs against the other.
- Large recurring comparisons: consider Power Query, which can be refreshed after the source data changes.
Prepare both sheets before comparing
Check that the sheets use consistent column headings and that the values you intend to compare are stored in compatible ways. An ID such as 00127 can be different from the number 127 if leading zeros matter. If one sheet stores the ID as text and the other stores it as a number, a lookup may not find the intended match. Dates can also look alike while containing different underlying values, such as a date with a time in one sheet and a date-only value in the other.
Remove or account for title rows, subtotals, and blank lines. Confirm which row contains the first record, and make sure formulas point to that row. If you expect one row per ID, check for duplicate IDs before relying on a key-based comparison: a lookup normally returns one matching record, not a complete comparison of every duplicate.
It helps to preserve the source data and put results in a separate column or sheet. Give the sheets clear names without spaces, such as Sheet1 and Sheet2, or use single quotation marks around a sheet name that contains spaces. For example, a reference to a sheet called January Data uses the form 'January Data'!A2.
Compare rows that are in the same order
When the records are aligned, enter a comparison formula in an empty result column. In this example, both sheets have corresponding records in rows 2 through 100, and columns A, B, and C contain the fields to check. Put the formula in D2 on Sheet1, then fill it down through D100:
With the values to compare in A2:C100 on both Sheet1 and Sheet2, enter this in Sheet1!D2; for example, if A2:C2 match but C2 differs, the result is Different.
=IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Match","Different")
AND returns TRUE only when all three comparisons are TRUE, and IF turns that result into a readable label. Adjust the formula for the actual columns you need to check. If a row has ten relevant columns, include all ten comparisons in AND. After filling the formula down, filter the result column for Different to review the exceptions.
This approach assumes row 2 on one sheet represents the same record as row 2 on the other. If a row is inserted or the data is sorted differently, the formula still compares positions, not identities. It may then flag many rows even when most records have not changed.
Compare records when row order differs
When the sheets may be sorted differently, use a unique key to find the corresponding record. Suppose column A holds a unique ID, column B a name, and column C an amount on both sheets. In Sheet1!D2, the following formula checks whether the ID exists in Sheet2 and, if it does, compares the name and amount with the matching record. Fill it down for the remaining rows.
With IDs in A2:A100, names in B2:B100, and amounts in C2:C100 on both sheets, the formula returns Different for ID 104 if its name matches but its amount is 125 on Sheet2 and 120 on Sheet1.
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing",IF(AND(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),C2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$C$2:$C$100)),"Match","Different"))
COUNTIF first checks whether the ID appears in Sheet2. If not, the result is Missing; otherwise, XLOOKUP retrieves the matching name and amount for comparison. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Its default match mode looks for an exact match. The formula assumes IDs are unique and that compared cells do not contain error values that need separate handling.
Duplicates need special attention. XLOOKUP returns the first matching ID, so it does not prove that all rows with a repeated ID match. If duplicate IDs are legitimate, create a genuinely unique key, compare the repeated records as a group, or use Power Query to examine the data at the appropriate level. Do not treat a duplicate as a harmless detail if it changes the meaning of the comparison.
Find IDs that are missing from the other sheet
A comparison made from Sheet1 finds IDs in Sheet1 that are absent from Sheet2, but it does not reveal IDs found only in Sheet2. Run the check in both directions, or combine the lists of IDs and examine which sheet contains each one. To label IDs from Sheet1 as present or missing, use this simpler check.
With IDs in Sheet1!A2:A100 and Sheet2!A2:A100, enter the formula in Sheet1!D2 and fill down. If A2 contains 104 and 104 does not appear in Sheet2’s ID range, the result is Missing.
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing","Present")
Repeat the check with the sheet references reversed to find IDs that occur only in Sheet2. Keep “missing” separate from “different”: a missing record has no matching key in the other list, while a different record exists in both but has one or more changed fields. Distinguishing these cases makes it easier to investigate whether a record was deleted, added, or edited.
Understand text, blanks, and other common surprises
Excel’s ordinary equality comparison is not case-sensitive. For example, a direct comparison treats Reed and REED as equal. If capitalization is meaningful, use the EXACT function for those text cells instead; EXACT is case-sensitive. Conversely, if capitalization should not matter but accidental spaces should, normalize the source values or compare cleaned helper values. Leading and trailing spaces are easy to overlook and can make two labels appear identical while comparing differently.
Blank cells also deserve a deliberate rule. Decide whether a blank means “no value,” whether two blanks count as a match, and whether a blank should be treated differently from a formula that returns an empty text string. Likewise, a numeric value stored as text can behave differently from a real number in comparisons and lookups. Inspect suspicious cells and correct inconsistent source data rather than assuming a formula can infer what the values were intended to mean.
Formatting is not the same as value. Two cells can display the same rounded number while holding different underlying values, or show dates in different formats while representing the same date. If the business rule is based on displayed precision, round or normalize values intentionally before comparison. If the rule is based on stored values, compare the underlying values and treat number formats as presentation only.
Finally, check the formula ranges. A range ending at row 100 will not include a new record added on row 101. Excel Tables can expand as rows are added and make references easier to maintain, while fixed ranges require review when the data grows. A formula result is only as complete as the data and ranges it checks.
Use Power Query for repeatable comparisons
For large lists or a comparison that must be repeated, Power Query can merge two tables on a key and expose matching and unmatched records. On Excel for Microsoft 365 on Windows, convert each data range to a table, for example with Ctrl+T, and confirm that the tables have headers. Select a cell in the first table and choose Data > From Table/Range. Repeat for the second table so each is available as a query.
In the Power Query Editor, choose Home > Merge Queries, select the second query, and select the matching key column in each table. Choose a join kind based on the question. A Left outer join keeps every row from the first table and brings in matching rows from the second; a Full outer join keeps rows from both tables, including those without a match. Expand the merged column to inspect the fields you need, then use the resulting values to identify missing or changed records.
Power Query is useful when the same comparison is performed regularly because the queries can be refreshed after the source tables change. It is not automatically a complete cell-by-cell audit: choose the correct key, join type, and columns, and inspect the output. The available authoring and menu options can differ in Excel for Mac and Excel for the web; the steps above are for Microsoft 365 on Windows.
Check the results before acting on them
Before using a comparison to update or delete records, inspect a sample of matches, differences, and missing IDs. Verify that the key is truly unique, the selected columns reflect the rule you intended, and both source ranges include all records. A useful check is to sort the two source tables independently and confirm that the key-based method still finds the same records. If results change merely because of sorting, the comparison is probably based on position rather than identity.
Keep the original sheets unchanged until the exceptions have been reviewed. A Different label does not explain why values differ; it only identifies a row that needs attention. Check whether the cause is a real edit, a data type mismatch, an extra space, a different date precision, or a duplicate key. That small review step prevents a technically correct formula from being mistaken for a complete explanation.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Yes. A formula can compare aligned cells or match records by an ID, and Power Query can merge tables for repeatable comparisons. The best choice depends on whether row order is reliable and whether you need to find missing records.
How do I compare two sheets if the rows are in a different order?
Use a shared, preferably unique ID to look up each record on the other sheet. Comparing row 2 with row 2 is not reliable after either list has been sorted or had rows inserted.
How do I highlight differences instead of displaying Match or Different?
You can use a comparison result column and apply conditional formatting to it, or create a conditional-formatting rule based on a formula. A result column is often easier to audit because it states which rows differ and can be filtered.
Does an Excel comparison formula distinguish uppercase and lowercase?
Ordinary equals comparisons are not case-sensitive. Use EXACT when capitalization must count as a difference, and decide separately how spaces and blank values should be handled.
What if the sheets contain duplicate IDs?
A basic XLOOKUP comparison returns the first matching record, so duplicate IDs can produce an incomplete or misleading result. Make the key unique where possible, or use a method that compares all records in each duplicate group.
Related
- Hide Columns in Excel: How to Hide and Unhide Them
- How to Import CSV to Excel: Steps and Common Fixes
- Consolidate Data in Excel: Methods and Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP Formula Examples for Everyday LookupsExcel’s XLOOKUP function finds a value in one range and returns a related value from another. For example,…
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel LAMBDA in Name Manager: How to Create and Use ItYes. In Microsoft 365 Excel for Windows, you can save a LAMBDA formula as a defined name in…
- Excel Filter Not Showing All Items? Find the CauseIf an Excel filter is not showing all items, first clear filters on every column, then check that…
- Break-Even Excel Formula: Calculate Units and SalesThe basic break-even formula in Excel is fixed costs divided by the contribution margin per unit: =B2/(B3-B4). If…
- Excel Date Plus 1 Day: Formula, Examples, and FixesTo add one day to an Excel date, add 1 to the date cell. If the date is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.