Most FMCG teams do not reconcile retailer data with a tool. They do it in Excel, and the problem is rarely the tool. The problem is that the workbook mixes the retailer's raw file, the analyst's mapping, the comparison formulas and the notes about exceptions in the same sheets, so every month the analyst rebuilds the logic by hand and nobody can say exactly what changed. A reconciliation that cannot be reproduced is not a process; it is a series of decisions that happen to be recorded in a spreadsheet.
This guide structures the Excel reconciliation into five zones — raw data, mapping, controls, comparison and exceptions — and shows what each zone must contain, what it must not contain, and how the zones fit together so the same file can be rerun next month with the same logic and a different data.
Workbook structure
One workbook, five zones, each on its own sheet, in a fixed order. The raw sheet holds the retailer file exactly as delivered, with nothing else on it. The mapping sheet holds the tables that translate retailer codes, stores and periods into your internal references. The controls sheet holds the counts and checks that prove the inputs are complete before the comparison runs. The comparison sheet holds the joined data and the differences. The exceptions sheet holds the difference lines that need a decision, with an owner, a status and an age.
The order is not decoration. Data flows one way, from raw to mapping to comparison, and the controls sit between raw and comparison so that a bad input never reaches the difference table. When an analyst needs to explain a number, they walk back up the flow to the raw source. When a formula breaks, it breaks in one zone, not in a sheet that contains three kinds of content.
The same discipline applies to the tabs inside a sheet. The comparison sheet, for example, should have a fixed layout for the current period, not ten past months side by side with different column positions. The historical files stay in the version folder, not in the workbook. A workbook with one clean comparison table per period is easier to read, easier to check and easier to automate than one that grows sideways every month.
Raw-data rules
The raw sheet is a contract: the retailer's file goes in exactly as delivered, and nothing in that sheet is ever typed over. The values are read by the rest of the workbook, so any manual fix applied to the raw sheet corrupts the audit trail. If the retailer file has a date in the wrong format or a quantity that is clearly nonsense, the fix belongs in a transformation step, not in the source cell.
Two practical rules make this possible. First, import the file with a tool rather than copy-pasting it: Power Query's combine-files flow turns a folder of monthly retailer files into one table with a documented transformation step, and the same query can be refreshed next month instead of rebuilt. Second, add an intake stamp to the raw table — the file name, the date received and a row count — so the workbook can prove which version of the retailer's data it is working from.
The row count is the first control and it belongs with the raw data, not in someone's head. If the retailer sent 4,000 rows last month and 3,700 this month, the difference is either a missing store or a missing category, and the reconciliation should stop until that is explained. Comparing the delivered row count against an expected population is the cheapest check in the whole process, and it is the one most workbooks skip because the analyst "noticed the file looked small."
Mapping joins
The mapping sheet holds the lookup tables the comparison depends on: retailer product code to internal SKU, retailer store code to internal location, retailer reporting period to your financial period. Each lookup is a small table on its own part of the sheet, and each has an effective date, because product codes and store codes change. The lookup is the place where the product mapping work done elsewhere in the reconciliation lands.
The joins in the comparison sheet reference these tables by name. If a retailer code has no mapping, the join returns a missing value and the line becomes an exception, which is correct: an unmapped product is a difference to resolve, not a row to drop. The cardinality check matters too. A lookup that returns two SKUs for one retailer code is a mapping error that silently doubles sales; the comparison should flag any one-to-many result instead of letting Excel pick the first match.
Keeping the mapping in its own zone is what lets you test it. When a new retailer code appears, the analyst adds one row to the mapping table and the whole comparison reruns. When someone proposes "fixing" a sales figure by changing a join, the mapping sheet makes it visible as a mapping change with a date, rather than an invisible edit inside a formula.
Comparison table
The comparison table joins the retailer data to the internal data at a stated grain — store by product by period — and produces the difference for every line. The grain is a column set, not a sentence: every row carries store, product, period, retailer units, internal units and the variance. Lines that match within tolerance are marked matched. Lines that do not are the difference table that feeds the exceptions sheet.
The comparison logic must be stable and named. Whether the variance is retailer minus internal, whether it is computed in units or value, whether a timing difference is netted or shown separately — each is a decision that changes the output, and each must be written in the workbook's documentation sheet once, so that next month's run does not silently change the sign of the variance. The same point applies to tolerances: a tolerance is a rule with a value and a justification, not a number the analyst remembers to apply.
The comparison sheet should also carry the control totals: delivered units from the raw sheet, mapped units after the joins, and the share of value that matched. Those three numbers tell you whether the difference table is real. If mapped value is 97% of delivered value, the 3% missing at the join is an input problem, and no amount of investigating the 5% variance will be meaningful until the join is complete.
Exceptions
The exceptions sheet is a log, not a scratchpad. Each exception line carries the identifiers, the variance, a cause category, an owner, a status and the period in which it first appeared. The categories are fixed — timing, mapping, returns, data error, commercial change — so that the same difference does not get labelled differently by different people. The statuses are fixed too: open, pending evidence, resolved, disputed, agreed.
The exceptions log is what turns a reconciliation into a control process. A difference that appears in three consecutive months with the same retailer code is a recurring problem with a root cause, and the log makes the recurrence visible because the line carries its first-seen period. A difference that gets "resolved" by changing the retailer figure and never appears again is a different story from one resolved by evidence. The log records which one happened.
This is the zone where the reconciliation connects to the wider exception lifecycle used in the rest of the operating model. The Excel exceptions sheet and a formal exception system can hold the same records with the same fields; the sheet is the small-scale version that works while the process is manual, and it keeps the same discipline so that moving to a system later is a migration, not a redesign.
Versioning
A reconciliation is only as good as its history, because retailer files arrive corrected, and the correction changes the answer. Each monthly run produces a version of the workbook tied to a version of the retailer file. The workbook keeps the run date, the file name, the intake row count and the mapped share in a small run log sheet, so that when the retailer sends an updated file in week three, the analyst can state exactly what changed between run one and run two rather than quietly absorbing the new numbers.
The version folder holds the closed runs. One folder per period, the workbook saved with a version suffix and the source file kept alongside it. This costs a few seconds of discipline and eliminates the two failure modes that dominate manual reconciliation: the analyst working on an old version of the retailer file without knowing it, and the analyst correcting a figure in the output without being able to say whether the source or the logic changed.
Versioning also makes the workbook safe to share. When the number moves between Monday's version and Tuesday's version, the run log says why. Without it, every movement in a reported variance triggers a re-run from memory, and the team reconciles the reconciliation instead of the data.
Failure points
The workbook will fail in predictable places, and naming them is half the fix. The first is the raw sheet being edited by hand, which breaks the audit trail silently. The second is logic living in the analyst's head — the tolerance, the sign of the variance, the treatment of returns — so that a change in personnel changes the output with no record. The third is one-to-many mapping, which produces plausible doubled sales. The fourth is the missing coverage check, where a file that shrank by 300 rows reconciles "fine" because the remaining rows all match.
The common thread is that each failure is invisible in the final output. A reconciliation that matches can still be wrong in all four ways. The controls — intake count, mapped share, cardinality check, run log — are the parts of the workbook that make those failures visible, which is why they are zones, not afterthoughts.
Spreadsheet research gives the same warning from the evidence side: cell-level error rates in spreadsheet development run at a few percent, and for a workbook with hundreds of formula cells that means errors are present rather than possible. The response is not to abandon Excel but to treat it as software: the logic is code, the raw data is input, and the same testing discipline that applies to code applies to the workbook. The standardization work that governs the files feeding this process belongs to the same system.
Where Excel reconciliations usually breakThe raw data and the logic share the same sheets, so the output cannot be tested, the exceptions live in notes, and the workbook is rebuilt every month. The first step is separating the five zones and adding the intake count; the automation question only becomes real once that run is stable.
When Excel stops being the answer
Excel is the right tool for the transition from an ad hoc monthly scramble to a controlled process, and it is the wrong tool for a process that has become stable and high-volume. The boundary is not company size; it is whether the workbook logic keeps changing. If the same five zones have produced the same clean result for several months with only the data changing, that logic is ready to be automated. If the analyst is still rewriting the mapping, the tolerance and the exception categories every month, automating now would automate the change itself.
The test for readiness is simple: take the last three months of runs. If the workbook required no structural changes across those three months, the process is stable enough to move into a tool or a managed operation, and the decision framework for automation applies directly. If it changed every month, the problem is not the tool, it is that the reconciliation has not yet become a defined process with defined rules — and the workbook zones in this guide are the fastest way to make it one.
Frequently asked questions
Can you reconcile retailer data properly in Excel?
Yes, for a defined transition period, provided the workbook separates raw inputs, mapping, controls, comparison and exceptions. The problems start when input cells and formula cells share the same sheets, because then the logic cannot be tested, the output cannot be audited and the workbook is rebuilt from scratch every month. That separation is the whole difference between a repeatable run and a monthly scramble.
What is the most common error in an Excel reconciliation?
Mixing the raw data with the analysis. Analysts paste retailer files into the same workbook, type over source cells, hard-code values inside formula columns and copy ranges by hand. Spreadsheet error research finds errors in a few percent of cells, and in a workbook of any size that reliably produces bottom-line errors. The fix is separating zones so the raw data is never touched.
When should I stop doing the reconciliation in Excel?
When the run is stable, the rules are documented, and the remaining bottleneck is time, not design. If the same workbook logic has produced the same clean result for several months, that logic is a candidate for automation. Automating a process that changes every month automates the change; automate only what has stopped changing.
Sources
- Microsoft Learn, Combine files overview — Power Query.
- Raymond R. Panko, Spreadsheet Development Error Experiments.
Microsoft's documentation is cited for the combine-files intake step described in this guide. Panko's research is cited for the general finding that cell-level spreadsheet error rates run at a few percent. The guide does not claim a specific rate for retail reconciliation workbooks in particular, because the sources describe spreadsheet development broadly and not this specific use case.