How to Compare Data in Two Excel Sheets Step by Step

To compare data in two Excel sheets, first decide whether matching records appear in the same row. If they do, compare corresponding cells; if rows may be in a different order, match records by a unique ID and then compare the values you care about. The steps below use Microsoft Excel for Microsoft 365 on Windows and include formulas you can fill down to check whole lists.

Before you begin, make a copy of the workbook and confirm that both sheets contain the same kind of data. A comparison only gives useful results when you know what identifies a record, such as an order number, employee ID, or product code. Comparing by row position alone can produce misleading differences if either sheet has been sorted or had rows inserted.

What You’ll Need

An Excel workbook containing the two sheets you want to compare.
A clear idea of whether the data is in the same row order or needs to be matched by an identifier.
A unique key column, such as an ID or code, if the rows may be in a different order. Ideally, each key appears once in each sheet.
Microsoft Excel for Microsoft 365 on Windows for the formulas shown. XLOOKUP is also available in Excel 2021 and later; if your Excel version does not support it, use the COUNTIF method to check presence and compare matching rows after sorting.

Step-by-Step

Prepare the sheets. Open the workbook and identify the two tabs. For clarity, rename them to simple names such as Sheet1 and Sheet2: double-click a sheet tab, type the new name, and press Enter. Check that the headers describe the same fields and that neither sheet has an extra title row, hidden subtotal row, or blank row mixed into the data. Keep the original data unchanged; add comparison results in a spare column or on a new sheet.
Choose the right comparison method. If each record is in the same row on both sheets, a direct cell-by-cell comparison is quick. If records might be sorted differently, compare using a unique ID instead. For example, a person’s name is often not a reliable key because two people can share a name, while an employee ID is designed to identify one record. If you are unsure whether the order matches, check a few recognizable IDs near the top, middle, and bottom before choosing the row-by-row method.
Compare corresponding cells when row order is identical. Suppose both sheets have names in column A and amounts in column B, with the first record on row 2. In Sheet1, put a comparison heading in C1. With the corresponding values in Sheet1!B2 and Sheet2!B2, enter this in Sheet1!C2:

With the same records on the same rows and values in column B, use =IF(B2=Sheet2!B2,"Match","Different"). If both cells contain 250, the result is Match; if one contains 250 and the other 275, the result is Different. For a direct comparison of the first column instead, use the equivalent references to A2. This checks one field at a time, so repeat it for each column that matters.

To apply the check to more rows, select C2 and drag the small fill handle at the lower-right corner of the cell down to the last record. Excel will adjust the row references as it fills. Alternatively, double-click the fill handle when the adjacent data column is populated continuously. Check the first and last filled formulas to make sure the range reaches every record.
Check whether each ID exists when row order may differ. Assume the unique IDs are in column A, with data starting on row 2 and ending on row 100. Put a heading such as “Presence in Sheet2” in Sheet1!C1. With the ID to check in Sheet1!A2 and the IDs to search in Sheet2!A2:A100, enter this in Sheet1!C2:

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing from Sheet2","Present")

If the ID in Sheet1!A2 appears anywhere in Sheet2!A2:A100, the result is Present; otherwise, it is Missing from Sheet2. The dollar signs keep the search range fixed when you fill the formula down. Drag the fill handle through the full list in Sheet1. To find IDs that appear only in Sheet2, enter the corresponding check on Sheet2, searching the IDs in Sheet1 instead.
Compare a value for the matching ID. A presence check tells you that a record exists, but not whether its details agree. For example, assume each sheet has a unique ID in column A and an amount in column B, from row 2 to row 100. In Sheet1, use column D for the result. With the ID in Sheet1!A2, the amount to check in Sheet1!B2, and the lookup ranges in Sheet2!A2:A100 and Sheet2!B2:B100, enter:

=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Different"),"Missing ID")

XLOOKUP finds the ID in Sheet2 and returns the amount from the same row. If ID 104 has amount 250 in both sheets, the result is Match. If Sheet2 has 275 for ID 104, the result is Different. If ID 104 is not in the lookup range, the result is Missing ID. Fill the formula down to check the remaining records. Repeat with the appropriate return column if you need to compare other fields.
Review and filter the results. Scan the result column for Different, Missing ID, or Missing from Sheet2. You can turn on filters by selecting a cell in the data and choosing Data > Filter, then use the result column’s filter arrow to show only the exceptions. Confirm a few flagged rows directly on both tabs before making corrections. If you use Ctrl+F to find a particular ID on Windows, Command+F is the usual equivalent in Excel for Mac.
Check the scope before relying on the comparison. Make sure the formulas cover the full data range and that the two lists use the same key format. If the workbook will be updated regularly, leave the formulas in place and extend them when new rows are added, or convert each data range into an Excel table using Insert > Table. Tables expand as you add records and make it easier to keep related data together, but check that any formula ranges include the new rows.

Common Mistakes

Comparing rows that do not correspond. A direct comparison of B2 with B2 is only meaningful if the same record is on row 2 in both sheets. Sorting one list independently can make correct data appear different. Use an ID-based lookup when order is uncertain.
Using a non-unique key. If the same name, date, or product description appears more than once, a lookup may find the wrong occurrence. Use a genuinely unique identifier, or build a combined key from fields that together identify one record. Check for duplicates before interpreting lookup results.
Checking only whether an ID exists. COUNTIF can confirm that an ID appears in the other list, but it does not confirm that the rest of the row matches. Add a value comparison for each important field.
Ignoring extra records at the end of a range. A formula that searches only through row 100 will not find a new ID in row 101. Confirm the first and last rows in every referenced range, and update ranges when the data grows.
Assuming visually similar values are identical. A number stored as text, an extra space, a different date value, or a rounded display can affect comparisons. Check the underlying values and formats rather than relying only on how cells look.

Tips

Keep a clear comparison column. Use headings such as “Amount check” or “ID status” rather than a generic heading. It makes filtered results easier to understand later.
Compare only the fields that matter. Two sheets may intentionally use different notes or formatting while agreeing on IDs, dates, and amounts. Decide which columns are authoritative before flagging discrepancies.
Check duplicates before using a lookup. In a list where each ID should occur once, sort or filter the ID column and inspect repeated values. A duplicate can make an otherwise sensible comparison ambiguous.
Use exact values when accuracy matters. Excel’s normal equality check compares the stored cell values, not just the rounded display. If two amounts look the same because both are formatted to two decimal places, they can still differ beyond those displayed decimals.
Make the formulas easy to audit. Put the comparison result beside the source row and keep the original columns visible. If you rename tabs to names that contain spaces, Excel uses single quotes around the sheet name in a reference, for example ‘January Data’!A2.
Save a review copy. A comparison identifies differences; it does not decide which sheet is correct. Preserve the originals and record any corrections separately so you can trace what changed.

Troubleshooting

Every row says Different, even though the values look alike: First confirm that the rows actually correspond. If they do, check whether one value is stored as text and the other as a number, whether a date includes a time, or whether a cell contains extra spaces. Click each cell and inspect the formula bar. Correct the source data consistently before concluding that the values should match.

The result says Missing ID, but you can see the ID on the other sheet: Check that the ID falls inside the lookup range and that the range includes the row where you found it. Compare the actual values for leading or trailing spaces, different punctuation, or text-versus-number differences. Also confirm that the formula points to the intended sheet and key column.

The lookup returns the wrong matching record: Check for duplicate IDs. XLOOKUP returns a matching result, but repeated keys do not uniquely identify a record. Choose a unique key or resolve the duplicates before comparing details.

The formula displays as text instead of calculating: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, edit the formula, and press Enter. Also make sure the entry starts with an equals sign and that calculation is not being prevented by a typo in a sheet name or reference.

The formula shows an error after a tab was renamed: Update the sheet reference in the formula. For tab names with spaces or punctuation, use single quotes around the name, such as ‘January Data’!$A$2:$A$100. If you copied a formula from another workbook, check that it is not still pointing to that workbook.

Frequently Asked Questions

Can I compare two sheets that are in different workbooks?
Yes. Open both workbooks and use a reference to the other workbook, or copy the relevant data into one comparison workbook. External references can be harder to maintain if a file is moved or renamed, so keep both files accessible and verify the links before relying on results.

Does the equality formula distinguish uppercase and lowercase text?
No. A basic comparison such as B2=Sheet2!B2 is not case-sensitive. If capitalization itself matters, use Excel’s EXACT function. For example, with the same text field in B2 on both sheets, =IF(EXACT(B2,Sheet2!B2),"Match","Different") returns Match only when the capitalization and text are identical.

Can I compare entire rows instead of one column?
Yes, but it is usually clearer to compare key fields separately so you can see which value differs. For a small, same-order dataset, create a result formula for each important column. For differently ordered data, locate the record by a unique ID first and then compare its fields.

What if the two lists have different numbers of rows?
That is fine. Check each sheet’s IDs against the other sheet’s full ID range. The presence check will identify records that exist on one sheet but not the other, while the value comparison can check fields for IDs that are found.

Will the formulas update when I change a source value?
Yes, Excel recalculates formulas when referenced data changes, provided calculation is set to automatic. The formulas will not automatically include rows beyond their stated ranges, so extend the ranges or use structured table references when the lists grow.

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.