Reconciling two spreadsheets — comparing them row by row to find what's missing or different — is one of the most tedious things you can do in Excel. It's also one of the most error-prone when done manually. This guide shows you how to do it with formulas and conditional formatting so Excel does the finding for you.
The Setup
You have two lists that should match but might not. Common scenarios: two exports from different systems that should contain the same records, a vendor statement vs. your accounts payable records, last month's customer list vs. this month's to find who churned.
Paste both lists into the same workbook — one on Sheet 1 (your primary list), one on Sheet 2 (the comparison list). Make sure both have a unique identifier column you can match on: order ID, customer ID, invoice number, etc.
Finding Rows That Exist in One List But Not the Other
On Sheet 1, add a column called "In Sheet 2?" and use MATCH to check:
=IF(ISNA(MATCH(A2, Sheet2!A:A, 0)), "MISSING", "Found")
Where A2 is the ID on Sheet 1 and Sheet2!A:A is the ID column on Sheet 2. If the ID isn't found in Sheet 2, it returns MISSING. Apply conditional formatting to highlight MISSING rows in red.
Do the same on Sheet 2 to find IDs that exist there but not on Sheet 1. These are your two sets of discrepancies: records missing from Sheet 2, and records that shouldn't be there.
Comparing Values for Matching Rows
Once you know which rows exist in both lists, compare the values. Use VLOOKUP to pull the Sheet 2 value for each Sheet 1 row, then calculate the difference:
=E2-VLOOKUP(A2, Sheet2!A:E, 5, FALSE)
Where E2 is the value on Sheet 1, A2 is the ID, and the VLOOKUP pulls column 5 (the value) from Sheet 2. A result of 0 means they match. Any other number is a discrepancy.
Apply conditional formatting to highlight non-zero differences. Sort by the difference column to put the largest discrepancies at the top.
Summarizing the Reconciliation
Add a summary section:
- Total records in Sheet 1: COUNTA of IDs
- Total records in Sheet 2: COUNTA of IDs
- Records in Sheet 1 not in Sheet 2: COUNTIF of MISSING in your match column
- Records in Sheet 2 not in Sheet 1: same on Sheet 2
- Matching records with value discrepancies: COUNTIF of non-zero differences
- Total discrepancy amount: SUMIF of absolute differences
These six numbers define the scope of your reconciliation problem.
The Easy Way: Using SheetXAI in Excel
Example 1: Both spreadsheets are already open.
"I have two lists on Sheet 1 and Sheet 2. Both should have the same invoice records matched by invoice ID. Find records that exist in one list but not the other, and flag any invoices where the amount differs between the two sheets."
SheetXAI reads both sheets, adds the match and comparison formulas, highlights discrepancies, and builds the reconciliation summary.
Example 2: Your data comes from two different systems.
"Pull our accounts payable records from QuickBooks and our vendor statement data from the uploaded file. Reconcile them by invoice number and show me what's missing and what has amount discrepancies."
SheetXAI pulls from both sources and builds the full reconciliation report.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Use VLOOKUP Across Multiple Sheets in Google Sheets, How to Build a Budget vs. Actuals Report in Excel, and Google Sheets AI Guide.
