Keep raw and summary apart

Four rows, and two of them say Penang.

The order in row 5 carries a leading space — " Penang" rather than "Penang". On the grid the two are indistinguishable; the space occupies the same nothing that the left-hand edge of the cell does. LEN is the cheapest way to see it, because it counts characters and characters are not a matter of opinion.

Run it on the padded cell. Then change the reference to B3, the clean Penang two rows above, and watch the number drop by one.

Criteria matching is exact. " Penang" is not "Penang", so a total built the obvious way silently covers two of the three Penang orders and leaves the third somewhere else — which is to say, nowhere.

This is the number that ends up in an email. It is plausible, it is confidently formatted, and it is short by one order.

Run the Penang total. Then add up the three Penang amounts on the grid by hand — 22.00, 44.00 and 19.50 — and compare.

A summary block earns its keep by being checkable against the raw block it came from. Every order belongs to exactly one city, so the city totals must add up to the grand total. If they don't, the difference is not a rounding artefact — it is orders that fell out of every bucket.

So take the raw total and subtract each city total from it. On a summary that covers everything, the answer is zero. Here it will not be, and the number it returns is not a vague warning: it is the missing money, to the cent, which is usually enough to find the row on sight.

The formula below is deliberately long. A reconciliation line is the one place in a sheet where spelling the arithmetic out beats being clever.

Now the fix — and the whole point of this chapter is where it goes.

TRIM removes leading and trailing spaces from a piece of text. The instinct is to run it, look at the tidy result, and type that result over cell B5. Thirty seconds, one cell, problem gone.

And the evidence is gone with it. Nobody can now tell that the file arrived with a padded value, which means nobody fixes it upstream, which means it arrives padded again next month and you spend the thirty seconds again — this time not knowing you have already found it once.

The working version costs one column. A new column D, =TRIM(B2) filled down, and every summary formula points at D instead of B. Column B is never touched, the two sit side by side, and the difference between them is a permanent record of what the source got wrong.

The layout that makes all of this automatic is three blocks on one sheet, left to right:

• Raw. Exactly what you were given. Nothing is typed into it, ever, including corrections you are certain about.
• Working. The cleaned columns, every one of them a formula reading from raw. If the source file is replaced next month, this recomputes and you do nothing.
• Summary. The numbers somebody asked for, reading from working, with a reconciliation line at the bottom that must come to zero.

Formulas point one way only — summary reads working, working reads raw, and nothing ever reads back the other direction. A sheet built like this can be handed to someone else, because every number in it can be traced to a cell they can see. A sheet where the corrections were typed in cannot, no matter how careful the person typing was.

A real Excel file would give each block its own sheet tab, which is tidier. The rule is identical either way, and the engine running these examples has one sheet, so blocks side by side is how this grid shows it.

Next in this track: the two things everybody does to a table without thinking — sorting it and filtering it — and what each one quietly does to the numbers already on the page.