An example
=B2*1.2
The VAT rate lives inside the formula, filled down three hundred rows. When the rate changes, somebody edits the rows they remember and the rest keep the old rate.
How it happens
Typing 1.2 is quicker than putting the rate in a cell of its own and pointing at it. The formula gives the right answer, so nothing ever makes anybody look at it again.
Why it matters
An assumption inside a formula cannot be seen, reviewed or changed in one place. Rates, prices, percentages and thresholds all change, and a file built this way needs every copy found and edited by hand, with nothing to say when one is missed.
How to find it by hand
Show formulas instead of their results with Ctrl and the backtick key, the key above Tab, in both Excel and Google Sheets. Then read down each column for numbers inside formulas and ask of each one whether it could ever change.
Move each one that could into a clearly labelled cell, and point every formula at that cell.
How Proper Spreadsheets finds it
The checker reads every formula and sets aside the numbers that are arithmetic rather than assumptions, such as 100 for a percentage or 12 for the months in a year, and the numbers that are structure, such as the places in a ROUND. Each remaining number is reported once per sheet, with how many formulas carry it.
Check a whole file in seconds
Finding this by hand means reading every formula in the file. The free check reads them all, along with six other mistakes that show no error, and points to each cell. Your file is deleted as soon as the check finishes.
Other mistakes worth checking for
- A total that stops short of the data: The formula is fine. It just ends a few rows too early, and nothing on the screen says so.
- One row that calculates something different: Every row in the column does the same thing, except one. The answer it shows looks perfectly normal.
- A figure typed over a formula: The column calculates, row after row, until the one row where somebody typed the answer in.
- A total that disagrees with its rows: The rows add up to one figure and the total says another.
- Error values, and the one cell causing them: Two hundred cells show #REF!. One of them is the cause, and it is rarely the first one you see.
- A formula that points at a sheet that is gone: The cell shows 0. It should show an error, and would, if IFERROR were not hiding it.