What Excel™'s inconsistent formula warning misses, and how to audit a column
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
- A typed value at the top or bottom of a run, with a neighbour on one side only.
- A constant buried inside a formula that is the same in every row.
- A whole column built the same wrong way. Every cell agrees, so none is flagged.
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.
- 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. - 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. - 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.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Hardcoded Value Detector is not affiliated with or endorsed by Microsoft.