AppGarden Hardcoded Value Detector logo AppGarden

Hardcoded Value Detector

How to find numbers typed over formulas in Excel™

By Paul Kemp · Published

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.

  1. Select the column or block you want to check.
  2. Choose Home → Find & Select → Go To Special.
  3. Pick Constants, tick Numbers only, and press OK. Excel™ highlights every typed number in the selection.
  4. In a column that should be all formulas, any highlighted cell is a suspect.
  5. 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.
  6. 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.

See the detector for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Hardcoded Value Detector is not affiliated with or endorsed by Microsoft.