The Excel FORMULATEXT function returns the formula in another cell as text — exactly what you’d see in the formula bar, equals sign and all. It’s the cleanest way to document a model, build an audit column beside calculated results, or spot the one cell in a column where someone pasted a hard-coded value over a formula.
Syntax
| Argument | Description | |
|---|---|---|
reference | Required | The cell (or range — the top-left cell is used) whose formula you want as text. Can point to another sheet or another open workbook. |
Available in: Excel 2013 and later, Excel for Microsoft 365, Excel for the web, and Excel for Mac. Excel 2010 and earlier show #NAME?.
Build an audit column beside your results
Column D below is calculated, and column E exposes how with one FORMULATEXT per row. Row 4 instantly stands out — someone has “adjusted” the total:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total | Audit |
| 2 | Laptop Stand | 4 | $49 | $196 | =B2*C2 |
| 3 | USB-C Hub | 2 | $65 | $130 | =B3*C3 |
| 4 | Webcam Pro | 3 | $119 | $360 | =ROUND(B4*C4,0)+3 |
Drag it down and every formula in the column is laid bare. Because the output is ordinary text, you can search it, filter it, or test it like any other string:
Try it: interactive FORMULATEXT demo
Point FORMULATEXT at different cells in the table above and see what comes back — including the #N/A case.
Audit tricks: find hard-coded values and document models
The #N/A behavior is a feature. Wrap FORMULATEXT in IFERROR and you get a clean “is this cell a formula or a constant?” report:
Excel 2013+ also ships ISFORMULA, which returns TRUE/FALSE and pairs well for conditional formatting — highlight every cell in a totals column that should be a formula but isn’t:
In Excel 365 a single FORMULATEXT can document a whole column at once — give it a range and it spills:
Quick alternative for a one-off look: Ctrl + ` (the backtick key) toggles all formulas visible on the sheet. FORMULATEXT is for when you want the formula text to live in a cell permanently.
Errors & common pitfalls
#N/A — the referenced cell has no formula. Typed numbers, text, empty cells, and values pasted as values all return #N/A. It also appears when the reference points into a workbook that isn’t open, or to a protected sheet with hidden formulas.
#NAME? — older Excel. FORMULATEXT arrived in Excel 2013. Files opened in Excel 2010 or earlier can’t evaluate it.
Pitfall: it shows the formula, not the logic of precedents. FORMULATEXT reads one cell. If that formula references other formulas, you only see the top layer — use Formulas › Trace Precedents to walk the chain.
Pitfall: the text follows the file’s formula language. What you get is the formula as stored — if a colleague opens the file with different regional settings, separators in their formula bar may differ from your captured text.
Pitfall: pointing FORMULATEXT at itself. =FORMULATEXT(A1) typed in A1 is legal and returns its own text — occasionally useful, mostly confusing. Keep audit columns separate from the formulas they document.
Practice workbook
Frequently asked questions
What does FORMULATEXT return if the cell has no formula?
Which Excel versions have FORMULATEXT?
How is FORMULATEXT different from ISFORMULA?
Can FORMULATEXT read a cell in another workbook?
How do I find cells where a formula was overwritten with a value?
=IFERROR(FORMULATEXT(D2), "hard-coded!"), or apply a conditional-formatting rule of =NOT(ISFORMULA(D2)) to the calculated range — every pasted-over cell lights up.Does FORMULATEXT recalculate when the target formula changes?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class