Ask anyone who has written code against a spreadsheet what they are afraid of, and they will tell you the same thing. The library will eat the chart. It will mangle the conditional formatting. You will open the file and your careful work will be gone.
I went to check that fear, and found something considerably worse hiding behind it.
The promise I was about to make
I was building a tool for a client, a print and textile designer with a studio business, that would take handwritten expense cards and append them to her existing spreadsheet. The specification I had written contained a promise: nothing that breaks the formulas you already have.
Before making a promise like that to somebody about their own bookkeeping, it is worth testing. So I built a synthetic spreadsheet designed to resemble hers and to be difficult: formulas, a chart, conditional formatting, a data validation dropdown, a cross sheet lookup, a defined name, frozen panes, a comment. Every number below is from that test file. Her workbook was never opened.
The thing everyone fears did not happen
Appending a row preserved all of it. The chart survived. The conditional formatting survived. The dropdown survived, the comment survived, the column widths and number formats survived.
The scary, famous failure mode simply did not occur.
What did happen
Appending a row moved the data down, and did not move the formulas with it. A spreadsheet application does that for you automatically when you type in it. A library writing to the file does not.
So the data now ran to row ten, and the total still stopped at row nine.
| Before | After | |
|---|---|---|
| Last row of data | 9 | 10 |
| The total formula | sum of rows 2 to 9 | sum of rows 2 to 9 |
One expense sat outside the total. No error. No warning. No red cell. The number looked entirely normal, and it was wrong.
On the test sheet the total under-reported by twelve dollars and forty cents.
Twelve dollars is nothing. That is the point. It is small enough that nobody would ever catch it by looking, and the mechanism that produced it does not care about magnitude. The same bug with a different row is a different number.
Why this is the same failure as a fabricated date
I had already been caught once on this project by an assistant inventing a year and filing it with confidence. This looked like an unrelated problem in a different part of the system, and it is not. It is the same failure wearing different clothes.
Neither one crashed. Neither one produced output that looked wrong. Both produced a financial record that was quietly incorrect, through a door the specification had not thought to guard.
A crash is cheap, because you can see it. This class of failure is expensive precisely because everything downstream keeps working.
So the writer refuses
The spreadsheet writer now scans every formula and every defined name for ranges that would exclude the new row, and will not write when it finds one. It names the exact cell and range, so the message is something you can act on rather than a shrug.
Safe shapes still pass through untouched: full column formulas, ranges that already extend past the data, sheets with no aggregates at all. And the default output is a separate file she reviews and imports, so her workbook is not written to unless its formulas are demonstrably safe.
Refusing is not caution for its own sake. It came out of a measurement.
The question I would never have thought to ask
The best thing this produced was not the fix. It was a question for the client that would not have occurred to me otherwise.
How are the totals in your spreadsheet written?
A sheet that totals an entire column can be appended to safely, forever. A sheet that totals rows two through forty seven cannot, because the moment you add row forty eight it silently drops out. Those two spreadsheets look identical when you open them. They are completely different engineering problems.
You cannot get to that question by thinking harder about the code. You get there by asking what the thing must never do, and then testing whether it actually never does it.
Which is the whole method, in one line: what must this produce, how will I know it is right, what must it never do.
The third question is the one people skip. It is also the one that buys you a tool you can trust with your own money.
Those three are the first half of the list I actually use before building anything. The full six, and the stop rule that goes with them, are on one page, free and with nothing to sign up for. The longer argument they came out of is here.