AppGarden Hardcoded Value Detector logo AppGarden

Hardcoded Value Detector

How to find hardcoded values in Google Sheets™

By Paul Kemp · Published · Updated

A "hardcoded value" is a number that should have been worked out but was typed in instead. In a Google Sheet™ it hides in one of two places, and each needs a different way of looking.

1. A number typed over a formula

A column works out =B2*C2, =B3*C3, =B4*C4… and one cell in the middle holds 4,200. Someone pasted a value there, or typed the answer they expected. It looks identical to its neighbours. It stops updating the moment an input changes, and nothing tells you.

Finding it by hand:

  1. View › Show › Formulas (Ctrl+`). Every cell shows its formula instead of its result, so a plain number in a column of formulas stands out.
  2. Or select the column and look for the odd one out as you press ↓, reading the formula bar.
  3. Or, in a spare column, =ISFORMULA(D2) filled down: a FALSE among the TRUEs marks the pasted cell.

All three work on one column you already suspect. None of them looks across a whole sheet, and all of them rely on you noticing. The hardest case is the first or last cell of a series — the total at the bottom, the first month of a forecast — because there is no neighbour on one side to compare with.

2. A constant inside a formula

=B2*1.2, =SUM(D2:D9)*1.075. The 1.2 is a VAT rate, a markup, an exchange rate: an assumption, written where nobody will see it. When it changes, it has to be found and edited in every cell that repeats it, and the one that is missed goes on being wrong.

Finding it by hand: Edit › Find and replace, tick Also search within formulas, and search for the number. That only works when you already know which number you are looking for.

The usual fix is to move the constant into its own labelled cell (say Assumptions!B2) and point every formula at it with an absolute reference: =B2*Assumptions!$B$2.

Checking a whole sheet at once

Google Sheets™ has no built-in check for either of these. That is the gap Hardcoded Value Detector fills. It reads the sheet, finds each run of formulas that share a shape, and reports the cell where a typed number breaks the run — including at the first and last cell. It also lists the literal numbers inside formulas, and explains each finding in one line. Checking the active sheet is free and unlimited.

Also worth reading: Google Sheets™ total not updating? A number was typed over the formula.

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.