AppGarden Hardcoded Value Detector logo AppGarden

Hardcoded Value Detector

What Excel™'s inconsistent formula warning misses, and how to audit a column

By Paul Kemp · Published

The green triangle is useful, but it is a neighbour check. It asks whether a cell differs from the cells beside it. That leaves gaps.

Where it can miss

Audit a column in three steps

These steps apply to Excel™ in Microsoft 365. Use a spare column next to your data, and delete it afterwards.

  1. In the first row, enter =ISFORMULA(D2) and fill down. Filter for FALSE. In a column that should be all formulas, each FALSE is a typed value.
  2. In a second helper column, enter =FORMULATEXT(D2) and fill down. Sort or filter it. Rows whose text looks different from the rest stand out.
  3. Scan the FORMULATEXT results for numbers that are not references, such as *1.2.

For a quick visual pass, press Ctrl+` to show formulas instead of values.

What to leave alone

Columns that mix two formula shapes may be deliberate, for example a different calculation for a different product line. Decide before you overwrite.

Skip the helper columns

Hardcoded Value Detector does this per sheet. It needs at least three matching formulas in a run before a gap means anything, so a lone formula is not checked. It lists the 200 most serious findings in the pane and says how many more there are. The paid whole-workbook check covers every visible sheet; checking one sheet, every explanation and Undo are free.

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.