Source-to-target mapping in Excel — where it breaks

Source-to-target mapping in Excel — and where spreadsheets break

Every mapping document ends up in a spreadsheet eventually, because that is where reviewers actually work — and they are right to want it there. The problems start when the spreadsheet stops being a view of the mapping and becomes the only copy. Notes from building a system whose export has to survive Excel, Google Sheets and Numbers, and come back alive.

Excel is the right review medium — say it plainly

A client reviewer will not learn your tool. They will open a workbook, filter to their area, write notes in the margin and send it back — and a mapping process that fights this loses the review entirely, which practitioners tell us is where migrations actually fail. The goal is not to keep mappings out of Excel; it is to let the workbook travel through Excel and return without losing anything it learned.

The layout itself — 14 columns across Source, Target, Mapping, and Review bands — ships as a free source-to-target mapping template (.xlsx, ungated), if the sheet is what you came for.

The five ways the workbook breaks

The partial-range sort. Someone sorts by one column and Excel obligingly sorts only the selected range — visible values move, the rest stay. Every decision recorded by row position now points at the wrong mapping. This is the quiet catastrophe: nothing looks broken.
Identity by position. Rows get inserted, cut and pasted, reordered. If “row 40” is your only way of saying which mapping a note belongs to, the note detaches the first time anyone touches the sheet. Rows need an identity that survives movement — a hidden per-row ID — and reconciliation has to match on it, never on position or on a human-readable name that repeats.
The stale copy. Two people annotate two exports; the mapping changes underneath both. Merging by hand silently overwrites someone’s decision. Surviving this takes a content fingerprint per row, computed identically at export and import, so a changed row can be detected and skipped with its note preserved rather than blindly applied.
Protection that isn’t. Sheet protection, hidden columns and cell formats are conventions, not guarantees — Google Sheets and Numbers drop or ignore them on round-trip. Anything load-bearing that lives in formatting will eventually be edited. Signal editability to humans; verify everything on the way back in.
The invisible edit. A reviewer types over a formula cell, autocorrect “fixes” a code, a locale rewrites dates. On re-import, every cell is a claim that must be validated against what was exported — and an ambiguous edit is something to surface as a question, never to coerce into a guess.

The rules that make round-trip survivable

Match returning rows on a hidden row ID, never on position and never on names — names repeat legitimately (one source funneling many attribute rows, for instance).
Cross-check the visible fields against the matched row anyway: if the target/source cells no longer agree with the row the ID claims, a sort or cut-paste moved the visible band — skip the row and keep the note.
Fingerprint row content at export and compare at import, so staleness is detected instead of discovered in production.
Treat every reviewer note as sacred: whatever else fails to apply, the note becomes a tracked question rather than vanishing with a rejected edit.
Make re-import idempotent — importing the same workbook twice must change nothing the second time, or nobody can safely retry.
Rows added by hand in Excel get surfaced for a decision, never silently inserted: the spreadsheet proposes, the system of record decides.

The honest division of labor

The spreadsheet is the review surface: portable, familiar, annotatable, client-friendly. The system of record is whatever enforces identity, staleness and note preservation when the workbook comes back. That can be a disciplined process with checksums and one owner — or software built for it. What it cannot be is the workbook itself, because the workbook’s job is to be edited freely, and a system of record’s job is to survive exactly that.

This is the engineering behind Crossary’s export: an ordinary .xlsx anyone can edit anywhere, with a hidden identity band and content fingerprints so re-import applies what matched, skips what moved, and turns every reviewer note into a tracked question. The layout is the free template, the full walkthrough is how to create a data mapping document, and three finished workbooks’ contents are published unedited as worked examples.

Export a round-trip-safe workbook — free Or walk a worked sample first

Source-to-target mapping in Excel — and where spreadsheets break

Excel is the right review medium — say it plainly

The layout itself — 14 columns across Source, Target, Mapping, and Review bands — ships as a free source-to-target mapping template (.xlsx, ungated), if the sheet is what you came for.

The five ways the workbook breaks

The partial-range sort. Someone sorts by one column and Excel obligingly sorts only the selected range — visible values move, the rest stay. Every decision recorded by row position now points at the wrong mapping. This is the quiet catastrophe: nothing looks broken.

Identity by position. Rows get inserted, cut and pasted, reordered. If “row 40” is your only way of saying which mapping a note belongs to, the note detaches the first time anyone touches the sheet. Rows need an identity that survives movement — a hidden per-row ID — and reconciliation has to match on it, never on position or on a human-readable name that repeats.

The stale copy. Two people annotate two exports; the mapping changes underneath both. Merging by hand silently overwrites someone’s decision. Surviving this takes a content fingerprint per row, computed identically at export and import, so a changed row can be detected and skipped with its note preserved rather than blindly applied.

Protection that isn’t. Sheet protection, hidden columns and cell formats are conventions, not guarantees — Google Sheets and Numbers drop or ignore them on round-trip. Anything load-bearing that lives in formatting will eventually be edited. Signal editability to humans; verify everything on the way back in.

The invisible edit. A reviewer types over a formula cell, autocorrect “fixes” a code, a locale rewrites dates. On re-import, every cell is a claim that must be validated against what was exported — and an ambiguous edit is something to surface as a question, never to coerce into a guess.

The rules that make round-trip survivable

Match returning rows on a hidden row ID, never on position and never on names — names repeat legitimately (one source funneling many attribute rows, for instance).

Cross-check the visible fields against the matched row anyway: if the target/source cells no longer agree with the row the ID claims, a sort or cut-paste moved the visible band — skip the row and keep the note.

Fingerprint row content at export and compare at import, so staleness is detected instead of discovered in production.

Treat every reviewer note as sacred: whatever else fails to apply, the note becomes a tracked question rather than vanishing with a rejected edit.

Make re-import idempotent — importing the same workbook twice must change nothing the second time, or nobody can safely retry.

Rows added by hand in Excel get surfaced for a decision, never silently inserted: the spreadsheet proposes, the system of record decides.

The honest division of labor