How to find numbers typed over formulas in Excel™
Someone pastes a value over a formula to "fix" a number. The sheet still adds up, but that cell has stopped recalculating. Change an input and every row moves except that one.
Why Excel™ does not flag it
Excel™'s green "inconsistent formula" triangle compares a cell with its neighbours. A typed value at the very top or bottom of a column has a neighbour on one side only, so there may be nothing for it to disagree with.
Find them by hand
These steps apply to Excel™ in Microsoft 365.
- Select the column or block you want to check.
- Choose Home → Find & Select → Go To Special.
- Pick Constants, tick Numbers only, and press OK. Excel™ highlights every typed number in the selection.
- In a column that should be all formulas, any highlighted cell is a suspect.
- For a quick visual check, press Ctrl+` (the backtick key) to show formulas instead of results. Overtyped cells show a plain number while their neighbours show formulas.
- Compare the typed number with what the formula above or below would give, then retype the formula.
Work on a copy if you are unsure what the number was for.
When a column has legitimate constants
Some columns mix inputs and formulas on purpose. If a column holds two different formula shapes, treat that as deliberate until you know otherwise.
Check the whole sheet at once
Hardcoded Value Detector reads the active sheet and flags a value sitting in a formula column, then shows the formula the cell should hold. A run needs at least three matching formulas before a gap means anything. Checking the active sheet, every explanation and Undo are free; applying the fix is paid. The check runs inside Excel™ and nothing in the workbook leaves your machine.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Hardcoded Value Detector is not affiliated with or endorsed by Microsoft.