Find Hard-Coded Numbers in Formulas

Excel Formulas › Auditing & Error-Proofing

2013+ISFORMULA

A typed number where a formula should be — a hard-coded total over a SUM — is a silent model error. ISFORMULA flags cells in a calculated range that are not formulas, so plugged-in values can’t hide.

Quick formula: flag a constant where a formula is expected:

=IF(ISFORMULA(A2), "OK", "Hard-coded!")

ISFORMULA is FALSE for a typed-in number; in a range that should be all formulas, that means someone overrode it.

Functions used (tap for the full reference guide):

ISFORMULA ISNUMBER

The example

A pasted value over a calculation.

	A	B
1	Cell	Check
2	=SUM(...)	OK
3	48000 (typed)	Hard-coded!

The formula

The formula:

=IF(ISFORMULA(A2), "OK", "Hard-coded!") // not a formula → flag

How it works

How it works:

ISFORMULA(cell) returns TRUE for formula cells, FALSE for typed constants (Excel 2013+).
In a range that should be all formulas, FALSE means someone pasted or typed a value over the logic.
Use =NOT(ISFORMULA(A2)) with conditional formatting to shade every hard-coded cell.
Count overrides across a model with SUMPRODUCT(--NOT(ISFORMULA(range))).

Hard-codes are how models rot. Someone “just fixes” a cell by typing the right number over a formula; later an input changes and that cell no longer updates, quietly wrong. Shading non-formula cells in your calculation areas (a CF rule of =NOT(ISFORMULA(A1))) makes every override visible at a glance — the single best defense against silent model decay.

Try it: interactive demo

Live demo

Cell content (start with = for a formula).

Check:

Variations

Shade hard-codes (CF)

Highlight overrides:

=NOT(ISFORMULA(A1))

Count overrides

How many typed:

=SUMPRODUCT(--NOT(ISFORMULA(range)))

Constant in a formula

Spot a magic number:

=ISNUMBER(SEARCH("0.0825", FORMULATEXT(A2)))

Pitfalls & errors

2013+ only. ISFORMULA needs Excel 2013 or later.

Blanks read FALSE. Empty cells aren’t formulas — exclude them from the audit range.

Intentional constants. Some cells should be typed (inputs) — audit only calculated ranges.

Practice workbook

📊

Download the free Find Hard-Coded Numbers in Formulas practice workbook

A hard-coded-finder sheet with the CF-shade, count, and magic-number variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find hard-coded numbers in formulas in Excel?

Use =IF(ISFORMULA(A2), "OK", "Hard-coded!") in ranges that should be all formulas; FALSE flags typed-in values.

How do I highlight every override?

Add a conditional-formatting rule =NOT(ISFORMULA(A1)) across the calculation area to shade hard-codes.

How do I find a magic number inside a formula?

Search the formula text: =ISNUMBER(SEARCH("0.0825", FORMULATEXT(A2))).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: ISFORMULA ISNUMBER