Position-based import grids accept a shifted column without complaint. Five rules for building spreadsheet deliverables that cannot be silently misread.
Most accounting and ERP systems accept bulk data through a paste grid: copy a block of cells, paste it in, review, commit. It is fast, it is well understood, and it has one property that quietly undoes a great deal of careful work.
The grid reads by position, not by heading. Insert one column anywhere inside the block and every field moves one place to the right. The date lands in the reference field, the payee lands in the account field, and the import succeeds. No error, no warning, no rejected rows — just a set of records that are plausible and wrong all the way down.
Five rules make a spreadsheet deliverable safe to paste from. They cost very little to apply and each one closes a failure that produces no error message.
01 · Keep helper columns outside the paste block
Working papers need columns the destination system will never accept: provenance notes, source file references, review flags, a filter column. These are useful and they must not sit inside the block.
Position matters more than most people expect. A helper column to the right of the block is harmless — nothing after the last field shifts anything before it. A helper column inside the block shifts every field beyond it. A single column in the leftmost position, before the block begins, is also safe.
The rule — the import fields are contiguous and in the destination system’s own order. Anything else goes before the first field or after the last, never between them.
02 · Make the helper columns look different, and say so
If a column is not meant to be copied, that has to be visible to whoever is doing the copying, at the moment they are doing it.
Shade every helper column the same colour, use that colour for nothing else, and write the instruction into the column heading itself rather than into a note elsewhere in the workbook. The shading then means exactly one thing — for you, not for the system — and it means it consistently.
The rule — one visual convention, one meaning, stated in the heading.
03 · Derive column letters, never hardcode them
Summary formulas that reference columns by letter are a delayed fault. Write a total that sums one column conditional on two others, hardcode those letters, and the formula is correct — until someone inserts a column ahead of them. Then the letters point at different fields entirely.
The formula keeps calculating. It produces a number. The number is meaningless, and it is the number someone will check the deliverable against.
The rule — generate every column letter from the column’s index, and assert that the produced formula string contains the letters you expect before saving. A wrong total that still computes is the worst available outcome.
04 · Understand what inserting a column does to existing formulas
Adding a column to a workbook someone already holds is a common request, and it has a specific hazard: inserting a column moves the cells but does not always move what the formulas point at. Insert a column before a referenced one and a formula reading a neighbouring cell now reads a different value. The sheet recalculates without complaint.
Where this has to be done, copy the workbook rather than editing the original, insert the column, then rewrite every formula’s references — taking care to leave text inside quotation marks alone, so that a link target containing a column letter is not corrupted.
The rule — treat column insertion as a change to the formulas, not just to the cells.
05 · Different destinations use different names for the same grid
One more positional trap. The same system frequently uses different column headings for structurally identical grids depending on the transaction type — the field that is a payee in one is a received-from in another, and the account field is named differently alongside it.
A single template reused across both looks right and imports into the wrong fields for one of them.
The rule — build the block from the destination’s own naming for that specific transaction type, and do not share one layout across two that merely look alike.
The common thread
Every failure here shares a shape: the system accepts the input and produces a result, so nothing signals that anything went wrong. Validation that checks whether the import succeeded cannot catch any of them, because the import always succeeds.
What does catch them is verifying the deliverable’s own totals against the source data it was built from, before it leaves. That comparison is independent of the layout, so it fails when a field has moved even though everything else looks correct.
What a good check actually compares
The instinct when adding validation is to check the file against itself — do the columns have headers, are the dates dates, does every row have the right number of cells. None of that catches a shifted column, because a shifted block satisfies all of it.
A check earns its place only if it compares the deliverable against something that did not come from the same step:
- Totals against source. Sum the column in the deliverable and compare it against the same sum computed from the originating records. Independent of layout, so a shifted column changes one and not the other.
- Row count against source. A dropped or duplicated row is invisible in a total that happens to be plausible, and obvious in a count.
- Spot-check by identity, not position. Pick a handful of records and confirm the values against the source by looking them up on a stable identifier, rather than by reading the same row number in both.
- Column order against the destination’s expected order, asserted explicitly. Where the receiving system reads by position, the expected order is part of the contract, and a contract that is not written down is one nobody notices changing.
- Type and sign sanity per column. Refunds carried negative, dates falling inside the period, values within an order of magnitude of what that account has carried before. Each is cheap, and each is violated by a different defect.
The pattern common to all five is that they hold information the build step did not have. That is the whole basis on which a check can catch what the build got wrong, and it is the reason self-consistency checks feel productive while catching almost nothing.
Why this belongs to the firm, not to IT
It is tempting to file this under software quality and move on. It is not: the person who pays for a silently misaligned import is the reviewer who later cannot explain why a category is wrong, and the client who receives a corrected set of figures with no clear account of what happened.
The reason it recurs is that the failure has no symptom at the point it occurs. Nobody is ignoring a warning, because there is no warning. Every party behaved correctly — the file was built, the import succeeded, the totals were plausible — and the defect passed through all of them. Processes with that property do not improve through care or seniority. They improve only by adding a check that is independent of the step that failed.
Total the deliverable against its source before it leaves. It is one comparison, it takes seconds, and it is the only thing here that catches all five failures at once.
The same argument applied to any generated artefact is in verify the deliverable, not the library that wrote it, and the review procedure for coding produced this way is in how to audit an automated coding run.
If your month-end involves building paste-ready workbooks, these checks belong in the process that produces them, so a mis-shaped block fails the build rather than reaching the destination system. That validation layer is part of month-end close automation.
Questions this raises
The import reported no errors. Does that not mean it worked?
It means the receiving system could parse what it was given, which is a much weaker statement. A paste grid reads by position, so a block with a column in the wrong place is structurally valid and semantically wrong. Import success tells you the file was well formed; it says nothing about whether the values landed in the fields they belong to.
Who is supposed to catch this — the person building the file or the person importing it?
Whoever builds it, because they are the only one holding the source data the totals can be checked against. By the time the file reaches the importer, the information needed to detect a shifted column has been left behind, which is precisely why these errors survive to the ledger.
Is this only a problem for hand-built workbooks?
It is worse for generated ones, because volume removes the incidental eyeballing a manual process gets. A person pasting one workbook a month will occasionally notice something odd. A process producing forty will not, and it will produce the same defect in all of them.
What is the single most valuable check to add first?
Total the deliverable and compare it against the source it was built from, before the file leaves. That one comparison is independent of layout, so it catches a shifted column, a dropped row and a duplicated block alike — three unrelated defects, one check.