Established onExcel 365, Windows and MacExcel 2016 and laterGoogle Sheets, web
A program that goes wrong usually stops. It raises an exception, fails a test, writes a line to a log, or refuses to compile. Somebody finds out, and the finding out is separate from the harm.
A spreadsheet has none of that machinery. There is no type to violate, no test to fail, no review before the change takes effect, no history of what the cell used to contain, and no trace of how the number was reached. A formula that is wrong produces a number, the number is formatted like every other number, and it goes into the report.
Four ways it happens
The range moved. A formula summing rows 2 to 400 is copied one column across and now sums 2 to 400 of a different column, which is what was wanted, or it is dragged down and now sums 3 to 401, which is not. Nothing about the result announces which.
The function was doing something else. The most used lookup in this field defaults to an approximate match, which returns the nearest smaller value rather than nothing when the exact key is absent. An entry with no match therefore returns a real value belonging to a different row.
The data changed type on the way in. A column of identifiers with leading zeros arrives as numbers. A date in one order arrives read in the other. Both are silent, both are reversible only if somebody notices.
Somebody typed over a formula. A cell in the middle of a calculated column now holds a constant. It looks identical to its neighbours and it will never change again.
The comparison with a database, which is instructive
A column in a database has a declared type, and an attempt to put text where a number belongs is refused at the point of insertion. A constraint can require a value to be present, unique, or within a range, and the constraint is part of the data rather than part of a habit.
None of that exists in a sheet, and the reason is not oversight. A spreadsheet is a general surface on which anybody can put anything in ten seconds, and every constraint that would catch an error is also a constraint on that. The trade is real, it was made deliberately, and it is why the tool spread to every organisation on earth.
Which is why the answer this site gives is not to stop using spreadsheets. It is to know which safeguards are absent and to reconstruct the two or three that matter for the work in hand, deliberately, by hand, in cells.
Why nothing catches it
Every mechanism that catches this class of error in software is absent here, and each absence is a deliberate design decision that also explains why spreadsheets are useful.
There are no types, because a cell holding anything is what makes the tool general. There is no separate test, because the sheet is both the program and its output. There is no review, because the point is that one person can produce a result without involving anybody. There is no history, because a file is a file.
The absence of ceremony is the product. The cost of the product is that an error has nothing to stop it.
What this site does about it
The parts that follow are the four places the class can be attacked. What a function actually does, so the second failure above becomes predictable. What happens to data on the way in, so the third becomes visible. How to arrange a file, so the first and fourth become obvious rather than invisible. And how to establish afterwards that a sheet is not lying, which is the only one of the four that works retrospectively.
What we cannot verify
Research into how many spreadsheets contain errors exists and reports high proportions, using methods that differ substantially: some inspect sheets in use, some audit for a specific error class, some rely on self-reporting. The figures are not comparable with each other and none is quoted here. The mechanisms described above are demonstrable in any copy of the software and are what this site relies on instead.
The one habit worth taking from this entry
Before trusting a figure from a sheet, ask what it should be to within an order of magnitude, and check that it is. Not because the estimate is accurate, but because every failure described above produces a number that is wrong by a factor rather than by a rounding.
What a sheet does catch
Worth listing, because the boundary is the useful part. Division by zero, a reference to a deleted cell, a name it does not recognise, a value of the wrong kind passed to a function that requires a number, and a circular reference all produce a visible error value and stop.
Every one of those has something in common: there is no answer. The class this entry is about consists of the cases where there is an answer, it is arithmetically correct, and it is to a question nobody asked.
In short
- A wrong formula produces a number formatted exactly like a right one.
- There is no type, no test, no review, no history and no trace.
- The four common causes are a moved range, a defaulting function, a changed type and a typed-over formula.
- Every absent safeguard is also a reason the tool is useful.
- Three of the four are attacked before the fact and one after.
- Error-rate research exists, disagrees with itself, and is not quoted here.