The seven mistakes
- 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.
- A number typed into a formula: It is right until the rate changes. Then it is wrong in every row it was copied into.
- 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.
Why these seven
Researchers who audit real spreadsheets, Raymond Panko among them, have found mistakes in most of the files they examined, and the ones that matter are rarely the ones that show an error. These seven compute quietly and look right, which is why they survive review.
For practices checking client workbooks, see Proper Spreadsheets for accountants and bookkeepers.