FORMULATEXT function
FORMULATEXT shows another cell's formula as text. It is useful for documentation, audits and teaching sheets, and returns #N/A if the cell has no formula.
Example
Read it as a sentence: show the formula that sits in F5 as text, which gives =1/0.
Excel shows =1/0
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
Data
Syntax
FORMULATEXT(reference)
-
reference(required) - The cell to read the formula from.
Returns: The formula in that cell, as text.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=FORMULATEXT(Data!F5) | =1/0 | Formula cell |
=FORMULATEXT(Data!F8) | ="" | Formula returning empty text |
=FORMULATEXT(Data!F5:F8) | =1/0 | Multi-cell range |
=FORMULATEXT(Data!F6) | #N/A | Constant cell |
=FORMULATEXT(Data!F1) | #N/A | Blank cell |
=FORMULATEXT(Nope!A1) | #N/A | Sheet that does not exist |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
6
Hello World
7
0
8
=""
Data
Watch out for
- Returns #N/A if the cell contains no formula.
- Long formulas may be cut off.
Where it works
- Excel
- FORMULATEXT arrived in Excel 2013. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .