Established onAny spreadsheet, any versionThe signals are described in their own entriesWhere to draw the line is not a software question
This site is a list of safeguards that have to be built by hand, because the tool provides none of them. At some point building them by hand costs more than moving the work, and this entry is about recognising that point.
The signals
The file is a step in a recurring process. Somebody opens it every Monday and performs the same actions. That is a program being executed by a person.
More than one person needs to change it at once. Not view it, change it. Simultaneous editing solves the copy problem and not the review problem.
Correctness has consequences. Money moves, a regulator reads it, a clinical or safety decision follows. The absence of tests and review stops being a trade-off.
It has become a program. Defined functions, recursion, dozens of steps in a stored query. The second part of this site describes that transition, and once it has happened the arguments for a language with version control apply in full.
The numbers feed something automatically. Once an output is consumed by another system, the file is an interface, and interfaces need stability the spreadsheet cannot promise.
The limits are in sight. Row counts approaching the format's ceiling, recalculation measured in minutes.
The signal that is not a signal
Somebody finding it inelegant. A workbook that is doing its job, used by one person, checked, and small, is not a problem to be solved, and the instinct to replace it with something better engineered is how organisations acquire half-finished replacements alongside the spreadsheets that are still in use.
Moving too early
Is a real failure and less discussed than moving too late. A spreadsheet can be changed by the person who understands the problem, in minutes, without involving anybody. Every replacement takes that away, and where requirements are still moving, taking it away stops the work.
So the honest rule is to move when the demands have crossed what the tool provides, and not in anticipation.
What to move, and in what order
Not everything, and not at once. The boundaries move one at a time, and each is useful alone.
The import. Make it a stored query, which the third part describes. This is the first step in every case and frequently the only one needed.
The data. Move the table itself into a database and have the sheet read from it. The calculation stays where people can see it and the storage becomes something with types and constraints.
The calculation. Move it into code in a version controlled repository, and produce the numbers from there. The spreadsheet becomes a presentation of results.
The presentation. Last, if at all. This is the part people are attached to, and often correctly: a sheet is an excellent interface for looking at numbers.
The distinction that decides it
Is the spreadsheet the artefact, or the interface? If people need to see and touch the figures, it stays and the machinery moves behind it. If it is a container for a process, the process leaves and takes the container with it.
The end of the argument
The first entry of this site said that a spreadsheet does not fail; it returns a plausible wrong answer, and nothing in the file marks it. Every part since has been a way of building, by hand, one of the safeguards that other tools provide by construction: declared types on import, a structure that makes a mistake visible, a check that does not depend on anybody noticing, a record of who changed what.
All of them work. None of them is free, and each has to be maintained by somebody who remembers why it is there. That is the real cost of the tool, it is paid quietly and continuously, and knowing what you are paying for is the whole point of reading any of this.
What we cannot verify
This entry is a set of judgements rather than claims about software. The signals are drawn from the mechanisms described throughout the site, each of which is documented in its own entry. Where an organisation should draw the line depends on things this site cannot know.
The intermediate arrangement
Between a workbook and a rewritten system there is a step most organisations skip: the calculation in code, the results in a table, and the spreadsheet reading that table for people to look at.
It keeps the interface everybody likes and moves the part that needs tests and history. Both products can consume a live table from elsewhere, and this arrangement is the one that survives longest in practice.
What to keep from the spreadsheet
The immediacy. Whatever replaces it should let the person who understands the problem change something and see the effect without waiting for anybody, or it will be worked around with a spreadsheet within a year.
That is the requirement most replacements fail, and it is the reason this entry ends a site about spreadsheets rather than dismissing them.
In short
- This site is a list of safeguards that have to be built by hand.
- Move when the file is a recurring process, or several people must change it at once.
- Move when it has become a program, or when its output feeds another system.
- Somebody finding it inelegant is not a signal, and moving too early is a real failure.
- Move the boundaries one at a time: the import, then the data, then the calculation.
- Ask whether the spreadsheet is the artefact or the interface; that decides everything.