Purpose
Return value
Syntax
=FORMULATEXT(reference)
- reference - Reference to cell or cell range.
How to use
The FORMULATEXT function returns a formula as a text string from a cell reference. The FORMULATEXT can be used to extract a formula as text from a cell reference. The text returned by FORMULATEXT is the same as displayed in the formula bar when a cell with a formula is selected. Once text is extracted with FORMULA text, it can be handled as text in another formula.
FORMULATEXT takes just one argument, reference, which is normally a cell reference like A1. If you use FORMULATEXT on a cell that doesn't contain a formula, it returns #N/A. FORMULATEXT will handle formulas up to 8192 characters.
In Excel 365, the FORMULATEXT function will return more than one result when given a range that contains formulas. These results will spill like other dynamic array formulas. In earlier versions of Excel, FORMULATEXT will return a single result from the upper left cell in the range.
To check if a cell contains a formula, use the ISFORMULA function. To temporarily display all formula in a worksheet with the keyboard shortcut Control + `.
Examples
Assuming the formula =TODAY() in cell A1:
=FORMULATEXT(A1) // returns "=TODAY()"
With the formula =C1+B1 in cell D1:
=FORMULATEXT(D1) // returns "=C1+B1"
With the text "apple" in cell F1:
=FORMULATEXT(F1) // returns #NA!
One quirk of FORMULATEXT is that it will not display a circular reference error if given a reference to the same cell it resides in. For example, if the formula below is entered in cell A1:
=FORMULATEXT(A1) // returns =FORMULATEXT(A1)
The result is simply "=FORMULATEXT(A1)".
Notes
- To test if a cell contains a formula or not, use the ISFORMULA function.
- FORMULATEXT will return #N/A if a cell does not contain a formula.
- FORMULATEXT will return #N/A when referencing another workbook that is not open.
- FORMULATEXT was introduced in Excel 2013.