The scariest spreadsheet errors are not the ones that show a big red #ERROR. They are the formulas that look fine and quietly return the wrong number, which then flows into a report someone trusts. Here are five that bite people constantly.
1. Relative references that should be absolute
When you copy a formula down, references shift. If a formula should always point at one cell, like a tax rate in B1, lock it with dollar signs: `$B$1`. Forgetting this is the number one cause of formulas that work in row 2 and break in row 3.
2. Approximate-match lookups
A VLOOKUP without `FALSE` (or `0`) at the end does an approximate match. On unsorted data it returns plausible-looking but wrong results, with no error. Always specify exact match unless you genuinely want ranges.
3. Hidden text in number cells
Numbers imported as text do not add up. A column that looks numeric but is secretly text will make SUM return a smaller total than expected, silently ignoring the text values. A quick fix is multiplying by 1 or using VALUE to convert.
4. Misaligned ranges in SUMIFS
In SUMIFS, the sum range and every criteria range must be the same size and shape. If one runs A2:A100 and another runs B2:B99, you get wrong totals or an error. Keep ranges identical.
5. Trusting AutoSum on filtered data
A normal SUM adds hidden rows too, so it ignores your filter. To total only the visible filtered rows, use SUBTOTAL or AGGREGATE instead.
The habit that prevents all five
Spot-check one formula by hand against a calculator before you trust a sheet. Thirty seconds of verification catches the silent errors that no warning will.