When Two Spreadsheets Tell Different Stories
One of the most frustrating situations in e-commerce operations is opening two spreadsheets that are supposed to reflect the same reality — order totals, inventory counts, SKU-level revenue — and finding that the numbers simply do not match. The discrepancy might be a few units off, or it might be thousands of dollars that cannot be reconciled at month-end. Either way, the problem has real consequences.
When a startup is pitching to investors or reporting to stakeholders, mismatched data undermines credibility instantly. When a fulfillment team is pulling from an inventory sheet that drifts from the orders sheet, stockouts and overselling follow. The cost of unresolved Excel data discrepancies is not just operational inconvenience — it erodes trust in every number the business produces.
The challenge is that most discrepancies are not caused by someone entering a wrong number once. They compound silently over weeks: a formula that references the wrong column, a date format that varies between sources, duplicate order IDs that get counted twice. Understanding how to systematically find and fix these issues is a skill that pays off every reporting cycle.
What Proper Reconciliation Actually Requires
Reconciling two Excel spreadsheets is not the same as eyeballing them side by side and hoping for the best. Done properly, the work involves a structured diagnostic process before any corrections are made.
The first requirement is a clearly defined key — a unique identifier that exists in both sheets and can serve as the join column. For an e-commerce dataset, this is almost always the Order ID. Without a reliable key, any match you attempt is meaningless because you may be comparing rows that do not actually correspond to each other.
The second requirement is format normalization. Date fields exported from Shopify often arrive as text strings ("2024-01-15") while a warehouse management system might export the same date as a serial number (45306). A VLOOKUP between these two will return errors that look like missing data but are actually just a formatting mismatch.
The third requirement is a structured diff — a method for surfacing exactly which rows differ, not just whether a summary total disagrees. Summary totals are lagging indicators of a problem; the row-level diff is where the actual cause lives. Skipping straight to the summary and adjusting figures manually is the most common mistake people make, and it guarantees the problem will recur.
A Practical Approach to Finding and Fixing the Gaps
Establish the Comparison Architecture
The cleanest way to approach this is to build a third sheet — call it RECONCILE — that pulls from both source sheets without modifying them. Source sheets stay untouched. All comparison logic lives in the reconciliation layer. This matters because as soon as you start editing source data to make things balance, you lose the audit trail that would explain why they diverged in the first place.
In the RECONCILE sheet, the first column should be a master list of all unique Order IDs drawn from both sources combined. The formula =IFERROR(UNIQUE(VSTACK(Sheet1[OrderID], Sheet2[OrderID])),"") works well in Excel 365. For older Excel versions, a Power Query merge on full outer join achieves the same result and is often cleaner for larger datasets above 50,000 rows.
Use XLOOKUP or VLOOKUP to Pull Matching Values
Once the master ID list exists, pull the revenue figure from each sheet into adjacent columns. A structure like =XLOOKUP(A2, Sheet1[OrderID], Sheet1[Revenue], "NOT FOUND") in column B and the equivalent for Sheet2 in column C gives a side-by-side comparison for every ID. Any row showing "NOT FOUND" in either column is an ID that exists in one source but not the other — these are your missing records.
For rows where both values exist, column D should calculate the delta: =IF(AND(B2<>"NOT FOUND",C2<>"NOT FOUND"), B2-C2, "N/A"). Filter column D for values not equal to zero and not equal to "N/A" — what remains is the set of matched records where the figures disagree. This is the working list for investigation.
Diagnose the Root Cause Before Correcting
For a typical e-commerce startup with three to five data sources (Shopify, a 3PL, an accounting export, and perhaps a manual returns log), there are four common causes of discrepancy that appear repeatedly.
The first is duplicate rows. A refund processed on the same day as the original order can generate two rows with the same Order ID if the export logic tags both events. Running =COUNTIF(Sheet1[OrderID], A2) and filtering for values greater than 1 identifies these immediately. If duplicates exist, the revenue figure from that source needs SUMIF logic rather than a simple lookup: =SUMIF(Sheet1[OrderID], A2, Sheet1[Revenue]).
The second is currency or unit mismatch. If one sheet reports in USD and another in local currency, or one reports at the item level while the other reports at the order level, the numbers will never reconcile until the aggregation level is aligned. A quick pivot table on each source — grouping by Order ID and summing Revenue — normalizes both to order-level totals before any comparison begins.
The third is truncation from text imports. Order IDs that begin with zeros ("001234") often get stripped to integers ("1234") when a CSV opens in Excel without a defined column format. Setting the import column type to Text in the Power Query editor prevents this. Without that step, XLOOKUP will return "NOT FOUND" for every leading-zero ID even when the record genuinely exists in both files.
The fourth is date-range boundary mismatch. An orders sheet exported at 11:59 PM EST and a fulfillment sheet exported at midnight UTC will have a different set of records for the final hour of any given day. Filtering both sheets to a date range that ends 24 hours before the export timestamp eliminates this edge zone.
What Goes Wrong When This Work Is Rushed
The most expensive mistake is jumping to correction before completing the diagnosis. Adjusting a summary total to make two sheets balance looks like a fix but is actually just moving the discrepancy out of sight. The next reporting cycle will surface it again, usually larger.
A second common problem is using VLOOKUP on non-unique keys. VLOOKUP returns the first match it finds and silently ignores all subsequent ones. If an Order ID appears three times in a source sheet due to partial shipments, VLOOKUP pulls only the first row's value. The reconciliation appears to work but is consistently undercounting. Replacing VLOOKUP with SUMIF wherever duplicates are possible is a non-negotiable upgrade.
Another pitfall is failing to lock the comparison architecture against source file changes. If Sheet1 and Sheet2 are live files that get overwritten each week, hardcoded column references in the reconciliation sheet break silently. Using structured table references (Sheet1[Revenue] rather than Sheet1!C:C) and naming tables explicitly reduces but does not eliminate this risk. Versioned snapshots — saved as Orders_2024-01-15.xlsx rather than Orders_Latest.xlsx — are a better long-term habit.
Finally, reconciliation is genuinely difficult to self-review after hours of staring at the same dataset. Errors in formula logic — a misplaced parenthesis that shifts a conditional, a SUMIF range that is one column off — stop being visible once they have been present for more than thirty minutes. A fresh set of eyes on the comparison logic, even briefly, catches the things exhaustion hides.
The Takeaway for Anyone Doing This Work
The core insight is that Excel data reconciliation is a diagnostic discipline, not a correction task. The correction is the last ten percent of the work. The first ninety percent is building a reliable comparison architecture, normalizing formats, identifying the category of discrepancy, and tracing it to a root cause.
Done systematically — with a dedicated reconciliation sheet, XLOOKUP or SUMIF matching logic, and a structured approach to duplicate and format issues — the process becomes repeatable. The same framework that resolves this month's discrepancy also catches next month's before it compounds.
If you would rather have this kind of structured data work handled by a team that does it every day, consider a business plan deck that documents your data governance approach, or explore how others have tackled similar challenges: precision data transfers between spreadsheets and structuring business spreadsheets for financial questions.