Comparing two Excel sheets is common when you receive a revised report, reconcile two exports, or need to find what changed between two versions of a workbook. Before comparing, decide whether you need to find changed cells, missing records, or differences in the overall lists.
Compare the same cell on two sheets
If Sheet1 and Sheet2 have the same layout, a simple formula can flag differences:
=IF(Sheet1!A2=Sheet2!A2,"Same","Different")Copy the formula across the relevant range when the cell positions correspond.
Highlight changed cells
Conditional Formatting can be used with a formula to make changed values easier to review. This works best when both sheets have the same structure and the cells being compared represent the same records.
Find records missing from the other sheet
If the sheets are not in the same order, compare a stable identifier such as an invoice number, employee ID or customer ID rather than comparing row numbers. COUNTIF or XLOOKUP can check whether an identifier exists on the other sheet.
=IF(COUNTIF(Sheet2!$A:$A,A2)=0,"Missing from Sheet2","Found")Compare revised and original files safely
- Keep an untouched copy of both source sheets.
- Choose a stable key or identifier.
- Normalize obvious data-quality issues.
- Compare presence first, then compare values for matching records.
- Review differences before changing either source.
Common problems
- False differences: extra spaces, different number formats, text-versus-number values, or different date representations can cause unexpected results.
- Wrong matches: duplicate identifiers make a simple lookup ambiguous.
- Row-by-row comparison fails: the two sheets may have been sorted differently.
- Large workbooks become difficult to review: use helper columns, filters, or a separate difference report.
Compare sheets or compare workbooks?
If the information is stored in separate workbooks, the same principles apply, but linked formulas and external references add another layer of complexity. For a repeatable reconciliation process, keep a stable key and document which version is the source of truth.
Practical checklist
- Never overwrite the original sheets before the comparison is complete.
- Use a stable identifier instead of row position whenever possible.
- Check data types and whitespace before trusting a mismatch.
- Separate missing records from changed values.
- Keep a reviewable difference report when the comparison is part of a recurring process.
Migration 074: sheet-comparison refinement.
Do not compare row numbers unless the rows are aligned
Two sheets can contain the same records in different orders. Comparing A2 with A2 is only valid when the row positions represent the same record. For exports from different systems, use a stable identifier such as an order ID, employee ID, invoice number, or product code to match records first.
A practical reconciliation workflow
- Keep an untouched copy of both source sheets.
- Choose the field that uniquely identifies a record.
- Check whether each identifier exists in the other sheet.
- For matching identifiers, compare the fields that matter.
- Separate missing records from changed records.
- Review the differences before updating either source.
Same-layout sheets versus reordered sheets
For two sheets with the same layout and guaranteed row alignment, a direct formula such as =IF(Sheet1!A2=Sheet2!A2,"Same","Different") is simple. For reordered or independently exported data, use a key-based lookup instead. This prevents a harmless row-order change from being reported as dozens of false differences.
Check data quality before declaring a difference
False differences often come from leading or trailing spaces, numbers stored as text, inconsistent date values, or other formatting differences. Normalize the data first, then compare. A reconciliation report is only as reliable as the data being compared.
When the files are large
For large workbooks or repeated reconciliation jobs, consider structured tables, Power Query, or a dedicated comparison workflow rather than maintaining thousands of ad-hoc formulas. The goal is a repeatable comparison process that another person can understand and rerun.
Practice Reconciling Two Excel Sheets
Use this free Excel workbook to practise comparing an original dataset with a revised version. The sample records include changed values, a missing record, and a newly added record, helping you learn how to produce a reviewable difference report.
What You Will Practise
- Compare two versions of a dataset using a stable, unique Record ID.
- Identify changes to budget amounts and project statuses.
- Find records that are missing from the revised sheet.
- Identify newly added records.
- Prepare a difference report and review each change before accepting it.
How to Use the Workbook
- Open the workbook and review the Original Dataset and Revised Dataset sheets.
- Use the Comparison sheet to compare records by their Record ID, rather than relying on row positions.
- Review the Difference Report sheet to examine changed values, missing records, and new records.
- Confirm each detected difference with the relevant data owner before making changes to your actual files.
A detected difference is a review flag, not proof that either version is incorrect. Keep an untouched copy of the original data and verify changes before updating the source records.
Compatibility note: The workbook uses XLOOKUP in some formulas. If your version of Excel does not support XLOOKUP, use INDEX/MATCH where appropriate.
Download the Excel Sheet Reconciliation Practice Workbook (.xlsx)