An example
=VLOOKUP(A2, Rates!A:B, 2, FALSE) showing #N/A
The lookup found no match for the value in A2. Every total that reads this cell now shows #N/A as well.
What each one means
#REF! means a cell or sheet the formula used has been deleted. #DIV/0! means it divided by an empty cell or zero. #N/A means a lookup found no match. #VALUE! means text was used where a number was needed. #NAME? means a function name is misspelt, or not available in this program.
Why it matters
An error spreads: every formula that reads the broken cell breaks too, so one deleted cell can fill a summary with errors. The cell to fix is the first in the chain.
How to find it by hand
In Excel, Formulas then Error Checking steps through each error, and Trace Precedents draws arrows to the cells a formula reads. In Google Sheets, hover over an error to read its message, then follow the cells its formula names until you reach one whose own inputs are fine.
How Proper Spreadsheets finds it
The checker reads every cell back and reports only the cells where an error starts, each with how many other cells fail because of it, so you fix one cell rather than reading two hundred.
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.
- 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.
- 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.