Most failed reconciliations aren't caused by anything dramatic. They're caused by small, boring data problems — a trailing space here, a number stored as text there — that make two records that should match look like they don't. Here are the five that catch people out most often, and the simple habits that prevent each one.
1. Duplicate references treated as errors
A single reference often legitimately appears on more than one row. One payout covers several invoices; one order has multiple line items; one subscription bills monthly under the same customer reference. If your reconciliation matches row-by-row, it either matches only the first occurrence and ignores the rest, or flags the duplicates as problems.
The fix: group rows by reference and compare summed totals, not individual lines. A £300 payout matches three £100 invoice lines because 100 + 100 + 100 = 300. Handle the one-to-many relationship deliberately rather than hoping every reference is unique.
2. Numbers stored as text
This is the silent killer of spreadsheet reconciliations. A column that looks like numbers — 1,234.50 — may actually be stored as text, often because it was exported with a currency symbol, thousands separators, or a leading apostrophe. Text "numbers" don't sum correctly, don't compare correctly, and quietly break any formula that expects a value.
The fix: make sure amount columns are genuine numbers before you compare them. Strip out currency symbols and thousands separators, and confirm the column sums to a sensible total. If selecting the column doesn't show a sum in your spreadsheet's status bar, it's text.
Select an amount column in Excel and look at the status bar. If there's no Sum shown, the values are text, not numbers — fix that before reconciling anything.
3. Whitespace and invisible characters
The reference ABC-123 and the reference ABC-123 (with a trailing space) look identical on screen but are different strings to a computer. Exports frequently introduce leading or trailing spaces, non-breaking spaces, or invisible control characters — and every one of them causes a match to fail.
The fix: trim whitespace from your key columns before matching. A good reconciliation normalises references automatically, but if you're doing it manually, wrap your keys in a TRIM() and consider CLEAN() to strip non-printing characters.
4. Inconsistent formatting of the same value
The same reference can be written several ways across systems: leading zeros (00123 vs 123), different cases (abc vs ABC), or different separators (ABC/123 vs ABC-123). Each system has its own convention, and they rarely agree.
The fix: decide on a normalised form and apply it to both files before matching — for example, strip leading zeros, convert to a consistent case, and standardise separators. The aim is that two references humans would consider "the same" become identical strings a computer can match.
5. Matching on the wrong column
Sometimes the reconciliation runs cleanly but the results are nonsense, because the two files were matched on columns that happen to overlap but don't actually represent the same thing — matching an internal ID against a customer reference, for instance, or a date against an amount.
The fix: confirm your matching key is genuinely shared before you rely on the output. A quick sanity check: how many references actually appear in both files? If the overlap is suspiciously low, you're probably matching on the wrong column. Good tools detect this by scanning the actual values in each column to find the real shared key, rather than trusting the column names.
Before trusting any reconciliation, ask: does the overlap between the two files look right? If two files that should mostly match show only a handful of matches, something upstream is wrong — usually formatting or the wrong key.
A simple pre-flight checklist
Before running any reconciliation, spend thirty seconds on these:
- Are the amount columns real numbers, not text?
- Have the key columns been trimmed of whitespace?
- Is the reference formatted consistently in both files?
- Am I matching on genuinely shared columns?
- Could any reference legitimately appear more than once?
Catching these up front turns a frustrating, error-prone reconciliation into a clean one.
Let the tool handle the traps
ReconFiles trims whitespace, coerces amounts to numbers, handles duplicate references, and auto-detects the real matching column — so these mistakes don't bite.
Try the reconciliation tool →The bottom line
Reconciliations rarely fail for interesting reasons. It's almost always whitespace, text-formatted numbers, inconsistent references, unhandled duplicates, or the wrong matching column. Build the habit of checking those five things — or use a tool that handles them for you — and the vast majority of "it won't match" headaches simply disappear.