Spreadsheets do not warn you when a formula is subtly wrong. The cell shows a number; the number looks plausible; the analyst moves on. The bug surfaces when someone runs the model with different inputs, copies the formula to a new range, or updates a lookup table whose layout shifted by one column. By then the spreadsheet has been used for decisions.
This post walks through Excel and Google Sheets formulas that look right but produce wrong results, with patterns the formula explainer flags. The pillar on reading code in plain English covers the general strategy; this is the spreadsheet drill-down.
Volatile functions and silent recalculation
Some Excel and Sheets functions recalculate every time anything in the workbook changes. The list:
NOW()returns the current time.TODAY()returns the current date.RAND()andRANDBETWEEN()return new random values.INDIRECT(text)recalculates because its argument is a string-resolved reference.OFFSET(reference, rows, cols, ...)recalculates because its arguments determine the range at runtime.INFO()andCELL()(some forms) return state that may change.
A formula that uses any of these is volatile. A spreadsheet with thousands of volatile cells recalculates the volatile cells (and their dependents) on every keystroke in any cell. Performance degrades; saves take longer; analysts blame their hardware.
Worse: NOW and TODAY can shift answers between sessions. A model that says “current cash on hand as of TODAY()” displays correctly today but a different value tomorrow. If the report is shared as a static PDF, that is fine. If the spreadsheet is the source of truth, future readers see different numbers than today’s reader.
How to spot in reading: any of the volatile function names. If your formula includes one and you do not need the recalculation behavior, replace it with a static value or a manually-refreshable input.
VLOOKUP and the “approximate match” default
VLOOKUP(lookup_value, table, column_index, [range_lookup]) has an optional fourth parameter that defaults to TRUE. TRUE means “approximate match for sorted data.” FALSE means “exact match.”
Most users want exact match. Most VLOOKUP calls in spreadsheets do not specify the fourth argument, accepting the default. The result: a lookup that returns a “close enough” value when the data is sorted in a way the writer assumed, and a wildly wrong value when it is not.
Example: VLOOKUP("widget-A", $D$2:$E$100, 2, TRUE). If widget-A is not in column D, the function returns the value next to the largest entry in D that is less than or equal to “widget-A” lexicographically. That could be widget-9999, wave-X, or anything alphabetically prior. The result type-checks but is meaningless.
The fix: always pass FALSE explicitly when you want exact match. Or use INDEX/MATCH which does not have this default. Or use XLOOKUP (Excel 365 and recent Sheets) which defaults to exact match and is generally clearer.
How to spot in reading: any VLOOKUP without the fourth argument. The explainer flags this.
Absolute versus relative reference confusion
A reference like A1 is relative: when copied down or sideways, it adjusts. $A$1 is absolute: it stays the same. $A1 is column-absolute, row-relative. A$1 is the opposite.
Bug shape: a formula =B2*$C$1 that calculates revenue using a tax rate from C1. Copied down, it correctly references C1 every row. But if someone later changes the structure and inserts a row above, $C$1 silently still points to the old row 1, which is now a header. The formula starts multiplying by a string and produces #VALUE!. Or it points to a row that now contains a different value, and no error fires; the math is just wrong.
Subtler bug: a formula that uses =SUM(A2:A100). Copied to a new column, it becomes =SUM(B2:B100). Correct. But if you intended to sum a fixed column regardless of where the formula lands, you needed =SUM($A$2:$A$100).
How to spot in reading: any formula with a mix of absolute and relative references. Walk through what happens when copied right and what happens when copied down. The explainer breaks this down for each reference.
Hidden type coercion: numbers stored as text
A column that looks like numbers but is actually text breaks numeric operations silently in some places and produces #VALUE! in others.
Common cause: data imported from a CSV where leading zeros were preserved by storing as text. A column of order numbers like “001234” looks numeric in the cell display but is stored as the string “001234”.
=A2+B2 against text columns produces #VALUE!. Easy to catch.
=SUM(A2:A100) against text columns silently produces 0 (in some versions) or skips the text values (in others). Hard to catch.
=COUNTIF(A2:A100, ">100") against text columns may treat the comparison lexicographically: “002” is “less than” “1” because of string ordering.
The fix: explicitly cast with VALUE(A2) or use --A2 (double-negative coerces text to number). Or fix the source data so the column is numeric.
How to spot in reading: a column whose cells are left-aligned (text default) when you expected right-aligned (number default). Or formulas that treat the column as text but compare to numeric literals.
Array formulas and implicit array context
Modern Excel (365) and Google Sheets have dynamic arrays: a formula that returns multiple values automatically spills to fill the destination range. =A2:A100*2 in a single cell spills 99 values down.
Older Excel (pre-365) requires explicit array formulas: {=A2:A100*2} entered with Ctrl+Shift+Enter, or it returns a single value (the first one) and the rest of your range stays empty.
Migration bugs: a model written in Excel 365 with implicit arrays opened in Excel 2019 silently breaks. Each formula returns one value where it used to return many. The model looks calculated but most cells now have wrong data. The reverse direction (Excel 2019 model in 365) usually works because dynamic arrays are backwards-compatible.
How to spot in reading: any formula with a range argument where you expected a single value. If the workbook may be opened in older Excel, plan for breakage.
IFERROR that hides real bugs
=IFERROR(VLOOKUP(...), 0) returns 0 when the lookup fails. This is convenient. It also hides bugs.
If the lookup fails because the lookup table is empty, missing, or pointing at the wrong range, IFERROR silently produces 0 instead of #N/A. The cell looks fine. The total downstream looks low but plausible. The bug is invisible until someone notices the total does not match what they expected.
The pattern is worse with nested IFERRORs: =IFERROR(VLOOKUP(...), IFERROR(VLOOKUP(...), 0)). Three failure points, all silenced. If any of the underlying lookups misfires, the result is 0 with no warning.
The fix: in production models, prefer explicit checks. =IF(ISNA(VLOOKUP(...)), error_marker, VLOOKUP(...)) distinguishes “lookup truly missing” (error_marker) from “lookup returned 0” (numeric 0). Or skip IFERROR entirely and let #N/A propagate so a downstream consumer can detect the missing data.
How to spot in reading: any IFERROR that returns 0 or empty string for missing data. Ask: would the analyst notice if 5% of the lookups silently failed and got replaced with 0?
SUMIF/SUMIFS criteria gotchas
SUMIF(range, criteria, [sum_range]) and the multi-criteria SUMIFS have a few traps:
- Text criteria are case-insensitive.
SUMIF(A2:A100, "ACTIVE", B2:B100)matches “active”, “Active”, “ACTIVE” all the same. If your data has subtle distinctions (ALL CAPS for emphasis vs lowercase for normal), the criteria does not respect them. - Wildcards in criteria:
SUMIF(A2:A100, "PRO_*", B2:B100)matches text starting with “PRO_”. But the underscore is a literal character; the asterisk is the wildcard. If you intended the underscore as a wildcard, the formula misbehaves. - Date criteria:
SUMIFS(B2:B100, A2:A100, ">2026-01-01")may fail because the comparison string is not interpreted as a date. The fix:SUMIFS(B2:B100, A2:A100, ">"&DATE(2026,1,1))or">"&DATEVALUE("2026-01-01").
How to spot in reading: any SUMIF or SUMIFS with text or date criteria. Verify the criteria match what the data actually looks like, including case and exact format.
Circular references that “settle”
A formula that depends on itself produces a circular reference. By default Excel and Sheets warn and replace the value with 0. With iterative calculation enabled, they “solve” the circular reference by repeating the calculation until it converges (or hits a max iteration count).
Models that use iterative calculation deliberately (interest-on-loan-balance models, for example) work this way. Models that have circular references by accident also work, in the sense that they produce numbers, but the numbers are wrong in subtle ways.
Worse: when iterative calculation is on and someone shares the workbook with someone whose Excel has it off, the recipient sees zeros where the original had numbers. They may not notice for hours.
How to spot in reading: any formula whose dependency tree references itself directly or indirectly. The explainer cannot always trace this (it sees one formula at a time), but it flags formulas that look like they could be part of a circular reference (self-referential ranges, OFFSET or INDIRECT pointing at the formula’s own row).
How to catch these in practice
For each formula you read or write, walk the 4-step strategy from the pillar post:
- State the intent. “Look up the price for this SKU.” “Sum the active accounts in the Western region.”
- Decompose into function calls.
- Verify each function’s behavior matches intent. Pay special attention to default parameter values (VLOOKUP’s TRUE, IFERROR’s silent fallback, SUMIF’s case-insensitivity).
- Test edge cases: empty data, missing lookups, text-where-numbers-expected, copied formulas in new positions.
The Excel and Sheets formula explainer decomposes any formula function-by-function and flags volatile functions, missing-fourth-argument VLOOKUP, and other patterns automatically. Paste a formula, read the explanation, ask whether it matches what you intended.
Closing
Spreadsheet bugs differ from code bugs in a key way: there is no test suite, no compiler, no linter that flags a wrong formula. The model produces numbers; the numbers look right; nobody questions them until something downstream goes wrong. Reading carefully at the moment formulas are written or modified is the only defense. The patterns above account for the majority of “the model produced the wrong number and nobody noticed for a month” stories. The pillar covers the general strategy; this post drills into the spreadsheet-specific cases.