Magic numbers in spreadsheet formulas: why a 1.15 hurts
=B2*1.15 works. It gives the right answer today. The trouble is everything the
1.15 does not say.
What a magic number is
A "magic number" is a constant written straight into a formula: a markup, a tax rate, an exchange rate, a headcount, a growth assumption. It is an input to the model, but it lives where inputs are not looked for.
Why it hurts
- Nobody can see it. The sheet shows the result. Whoever reads the model has to click into the cell to learn that 15% was ever assumed.
- Nobody knows what it means. Is 1.15 VAT plus something? A margin? Last year's inflation? A labelled cell says; a bare number never does.
-
Changing it is a search. When the rate becomes 1.2 it has to be found in
every formula that repeats it. Find and replace will also change the 1.15
that meant something else, and miss the one written as
115%. - The copy that is missed stays wrong. One formula keeps the old rate, and the totals disagree by an amount nobody can explain.
Which numbers are fine
Not every digit in a formula is an assumption. 0 and 1, a
/100 for a percentage, the 2 in ROUND(x,2), the column
number in VLOOKUP, a square
^2: these are structure, not policy, and moving them into a cell makes the
sheet worse, not better. A year in DATE(2026,…) is different — that one goes
stale.
How to fix one
- Put the number in its own cell with a label next to it — an Assumptions tab is
the usual home. For example,
Assumptions!A2reads "Markup" andAssumptions!B2holds1.15. - Optionally name it: Data › Named ranges, call it
Markup. - Point the formula at it with an absolute reference:
=B2*Assumptions!$B$2, or=B2*Markup. - Fill the formula down. Next time the markup changes, it changes in one place.
Finding them
Google Sheets™ does not flag constants inside formulas. Hardcoded Value Detector lists every one on the active sheet for free, skipping the structural numbers above, and explains each finding in one line.
Also worth reading: How to find hardcoded values in Google Sheets™.
Check your own sheet for free. Hardcoded Value Detector is free in the Google Workspace™ Marketplace. Find out how to install it, or read the step-by-step guide.