🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel FORMULATEXT & ISFORMULA — Read and Audit the Formula Inside a Cell

|

Excel FORMULATEXT & ISFORMULA — Read and Audit the Formula Inside a Cell

TL;DR — FORMULATEXT(reference) returns the formula in a cell as text (the literal =… string, not its result), and ISFORMULA(reference) returns TRUE/FALSE for whether a cell holds a formula at all. Neither one calculates — they inspect. Their job is auditing, documenting, and error-proofing a sheet, not producing numbers. The one rule that trips everyone: point them at a cell reference, and if that cell isn't a formula, FORMULATEXT returns #N/A.

=FORMULATEXT(C2)                          ' shows the formula text sitting in C2
=ISFORMULA(C2)                            ' TRUE if C2 is a formula, FALSE if it's a typed value
=IF(ISFORMULA(C2), FORMULATEXT(C2), "—")  ' the safe pairing: check first, then read

Almost every Excel function answers "what does this compute to?" These two answer a different question: "what is actually in this cell?" That makes them the tools you reach for when a model looks right but you need to prove it — when you're documenting how a number was derived, or hunting for the one cell where someone typed 1450 over a live formula.

What you'll learn

  • The mental model: these are introspection functions — they read the sheet, not the math
  • FORMULATEXT's main job: self-documenting tables that show a formula next to its result
  • Why FORMULATEXT returns #N/A, and the ISFORMULA guard that fixes it
  • The killer ISFORMULA move: a conditional-formatting rule that highlights every formula cell
  • When these beat Show Formulas (Ctrl+`) — and when they don't

The mental model: inspect, don't calculate

Normally a cell shows you the result of its formula. C2 displays 1,240; the =B2*1.08 that produced it is hidden behind that number. FORMULATEXT pulls the hidden thing into the open — it returns the source text of the formula as a plain string you can display, print, or pick apart:

=FORMULATEXT(C2)     ' returns the text  =B2*1.08

ISFORMULA answers the yes/no version of the same question — is there a formula in here at all, or is this just a typed-in value? — and hands back TRUE or FALSE.

Hold onto this framing: both functions take a reference to another cell and report on what lives there. You are not passing them a calculation to run. That's why =FORMULATEXT(1+1) is meaningless — there's no cell to inspect. They read the contents of a location, treating a formula as text to be examined rather than logic to be executed.

FORMULATEXT's real job: self-documenting tables

The single best use of FORMULATEXT is putting a formula and its result side by side. Tutorials, audit worksheets, "how was this calculated?" documentation — any time you want a reader to see both the answer and the mechanism:

' Column C computes; column D shows how, automatically
C2:  =B2*1.08
D2:  =FORMULATEXT(C2)     ' displays: =B2*1.08

Because D2 reads C2's formula, it stays correct when you edit C2 — change the tax rate and the documentation column updates itself. That's the advantage over typing the formula out as a text label by hand: a hand-typed label goes stale the moment someone edits the real formula; FORMULATEXT never lies about what the cell actually contains.

The #N/A trap, and the ISFORMULA guard

Here's the fact behind every "FORMULATEXT is broken" question: if the referenced cell is not a formula — a typed number, text, or an empty cell — FORMULATEXT returns #N/A, not a blank:

=FORMULATEXT(A2)     ' A2 contains the typed value 1450  ->  #N/A
=FORMULATEXT(A3)     ' A3 is empty                        ->  #N/A

So you can't blindly fill a whole column with it — the constant cells will litter #N/A everywhere. This is exactly what ISFORMULA is for. Guard the read with a check, and you get clean output on mixed data:

=IF(ISFORMULA(C2), FORMULATEXT(C2), "(constant)")

Read that as: if C2 is a formula, show its text; otherwise say it's a constant. IFERROR would also swallow the #N/A, but ISFORMULA states the intent plainly — you're distinguishing formulas from hard-coded values, which is the whole point of an audit. Reach for IF + ISFORMULA over IFERROR when the difference between "formula" and "constant" is the thing you actually care about.

The killer ISFORMULA move: highlight every formula cell

On its own, ISFORMULA looks almost too simple to matter. Its power shows up in a conditional-formatting rule, where a single TRUE/FALSE per cell is exactly what the feature consumes.

Select your data, add a rule of type "Use a formula to determine which cells to format," and enter:

=ISFORMULA(A1)

Every cell containing a formula lights up; every hard-coded value stays plain. In one glance you can see which numbers in a financial model are live and which have been overwritten by hand — the classic way a model silently breaks is someone pasting a value over a formula, and this makes that instantly visible. It's the cheapest model-integrity check there is, and it updates automatically as the sheet changes.

FORMULATEXT vs Show Formulas (Ctrl+`)

Excel already has a built-in way to see formulas: Show Formulas on the Formulas tab, or the Ctrl+`` shortcut, which flips the entire sheet to display formulas instead of results. So when do you need FORMULATEXT?

  • Show Formulas is a temporary, whole-sheet view — great for a quick scan, gone the moment you toggle it off. It doesn't put formula text into cells you can reference, filter, or print alongside results.
  • FORMULATEXT is permanent and per-cell — it pins one cell's formula into a cell of its own, so you can document it, compare two versions, feed it into SEARCH/text functions, or build an audit column that travels with the workbook.

Rule of thumb: reaching for a quick look → Ctrl+`` . Building documentation, an audit trail, or a comparison that has to stay in the sheet → FORMULATEXT.

One version note: both functions arrived in Excel 2013. They exist in every modern version and Microsoft 365, but on Excel 2010 or earlier you're back to Show Formulas or a VBA helper.

The judgment call

FORMULATEXT and ISFORMULA almost never appear in a formula you ship — they don't compute the answer, they describe how the answer is built. That's the point. Keep them in the audit-and-documentation drawer alongside the IS functions: when a value looks right but you need certainty, they turn "trust me" into "here's the proof." Use FORMULATEXT to make a model explain itself and ISFORMULA to guard that read (and to paint your model so hard-coded overrides can't hide). Treat any #N/A from FORMULATEXT not as a bug but as a message — that cell is a constant — and you're already using it the way a careful analyst does.

How ExcelMaster helps

Auditing a workbook by hand is slow: you click cell after cell, reading the formula bar, trying to spot the one that's been overwritten. ExcelMaster does it in one pass — ask it to "flag every cell where a formula was replaced by a typed value" or "add a column showing how each total is calculated," and it builds the ISFORMULA-based conditional formatting or the FORMULATEXT documentation column for you, guarded against #N/A so it stays clean on mixed data. You get an audit trail that keeps itself honest as the model changes.

Frequently asked questions

What does the FORMULATEXT function do in Excel?

=FORMULATEXT(reference) returns the formula in the referenced cell as a text string — for example, it shows =B2*1.08 instead of the value that formula produces. It's used to document or audit a sheet by displaying the formula itself next to its result. If the cell isn't a formula, it returns #N/A.

Why does FORMULATEXT return #N/A?

Because the cell you pointed it at doesn't contain a formula — it's a typed number, text, or empty. FORMULATEXT only has something to return when the target is an actual formula. Guard it with =IF(ISFORMULA(A2), FORMULATEXT(A2), "constant") so constant cells show a label instead of an error.

What is the ISFORMULA function used for?

=ISFORMULA(reference) returns TRUE if the cell contains a formula and FALSE otherwise. Its best use is inside a conditional-formatting rule — enter =ISFORMULA(A1) to highlight every formula cell, making it obvious which values in a model are live and which were typed in by hand.

Can I show all the formulas in a worksheet as text?

For a quick, temporary view of the whole sheet, press Ctrl+`` (Show Formulas) or use Formulas → Show Formulas. To pin a specific formula into a cell permanently — for documentation, printing, or comparison — use FORMULATEXT in an adjacent column instead.

Are FORMULATEXT and ISFORMULA available in Excel 2016?

Yes. Both were introduced in Excel 2013 and are present in Excel 2016, 2019, 2021, and Microsoft 365. Only Excel 2010 and earlier lack them, where Show Formulas or a small VBA function is the fallback.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-09.

Related guides: Excel IS Functions · Excel CELL · Excel TYPE & N · Excel IF