1) The real business question: how many sales are being lost or duplicated because of a bad match?

In many SMEs, the issue is not “how do I do an Excel join?” but how to prevent an order line and an invoice line from being incorrectly paired. As soon as you join exports from a CRM, ERP, e-commerce platform, or accounting tool, a tiny data-entry difference can create either a duplicate or a missing sale. CNIL reminds organizations that data should remain relevant to the stated purpose, and that cleaning data makes the dataset usable; NIST, meanwhile, treats record quality as a matter of completeness and uniqueness.

2) Practical method: start from a key, not a hope

The right method has four layers. First, use a strictly shared and stable key, ideally a business identifier already present in both exports, such as the order number, internal customer ID, or invoice reference. Second, normalize formats before any join: remove stray spaces, align upper/lower case, standardize dates, and preserve leading zeros. Third, measure key quality. If it is not unique, treat it as a risky key and add manual review. Fourth, decide what to do with ambiguous cases before opening Excel. This discipline matches the idea of identity resolution: NIST aims for a single unique entity within a given context, while CNIL emphasizes minimization and relevance of reused data.

3) Fictional numeric example: when 1% ambiguity creates 100% noise

Fictional example. A company exports 12,480 order lines from its ERP and 12,510 billing lines from accounting software. It wants to connect both tables to view revenue by customer. On paper, 12,300 lines seem to match. In reality, 180 references are truncated, 70 are duplicate entries, and 40 are credit notes. If the team runs a “closest match” join on customer name, it ends up duplicating sales and creating margin gaps that are hard to explain. If it starts with an exact match on the business reference, then reviews exceptions manually, it reduces noise without inventing revenue. The gain is not magical; it is mainly accounting and managerial. CNIL also reminds organizations that any database reuse requires prior checks on source, collection context, and compliance; that applies just as much when exports contain personal data.

4) Where Excel helps, and where Excel misleads

Excel is useful for preparation, filtering, and auditing; it becomes risky when it starts acting as an “interpretation engine.” An exact join on a clean key can remain reliable in Excel, but once you need approximate matching, multiple rules, or strong traceability of exceptions, each step must be documented. Think in three outputs: safe matches, doubtful matches, and non-matches. Doubtful rows should never be merged automatically. They should be routed to human review, with an explicit reason: missing key, inconsistent format, source duplicate, or field conflict. This also protects commercial governance: a slightly less “complete” database is better than a report that overstates revenue. Rectification rules also remind us that corrected data must be updated in the relevant datasets.

5) Limits, compliance, and executive common sense

Three limits matter. First, no join fixes inconsistent source data: if two systems describe the same customer with different identifiers, you need a master rule first. Second, exports containing personal data require checks on purpose, legal basis, and retained fields; CNIL emphasizes that for both processing and database reuse. Third, the more you mix algorithms and manual handling, the simpler your traceability must be for someone other than the file’s author. In short: a successful join is not one that finds “everything,” but one that can be explained without eye-rolling.

6) Seven-day action checklist

Day 1 — Owner: sales leadership. Observable result: one written business question, for example “which customer revenue figure is reliable at 98%?”. Day 2 — Owner: controlling/finance. Result: list of shared fields and their status (unique, incomplete, variable). Day 3 — Owner: data/IT. Result: a primary join key and a fallback key documented. Day 4 — Owner: operational referent. Result: a sample of 50 rows tested and failure causes classified. Day 5 — Owner: internal control or finance. Result: a written rule for ambiguous cases. Day 6 — Owner: data/IT. Result: a reconciliation file separating safe matches, doubtful matches, and excluded rows. Day 7 — Owner: leadership. Result: a go-live decision or source-data correction decision, with an accepted quality threshold. The logic is simple: before selling better, make sure your reporting is not lying.