Find formulas replaced by values in Google Sheets™
When a formula is overwritten by the number it used to produce, nothing changes on screen. The sheet only goes wrong later, when an input moves and that one cell does not.
How it happens
- Paste special › Values only over a range that reached one cell too far.
- Copying a block from another sheet or an export straight over live rows.
- Typing the "right" answer into a cell to make a report balance before a deadline.
- An import or a script that writes values back into a template.
Method 1: show every formula
View › Show › Formulas switches the sheet to formula view. A
column that should read =B2*C2, =B3*C3… and has a bare number in
it stands out. Switch back the same way. Good for one sheet you can see on one screen.
Method 2: ISFORMULA and conditional formatting
Select the column that should be all formulas, open Format › Conditional
formatting, choose Custom formula is, and enter
=AND(D2<>"",NOT(ISFORMULA(D2))) (with D2 being the first cell
of your selection). Every cell holding a typed value lights up. It keeps working as the sheet
changes — but you have to set it up per column, and you have to know which columns to watch.
Method 3: check the whole spreadsheet
Neither method above can tell a pasted value from a column that was always meant to hold numbers; you decide that by eye. Hardcoded Value Detector decides it from the sheet itself: it finds each run of cells that share a formula pattern and reports the cell where a typed number breaks it — including the first and last cell of a run, where there is no neighbour on one side. Each finding shows the formula the cell should have, and the fix writes it back with Undo always available.
Array formulas are handled deliberately: cells filled by ARRAYFORMULA,
FILTER or SEQUENCE hold values without formulas of their own, and
are never reported as pasted.
Also worth reading: Google Sheets™ total not updating? and 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.