FORMULATEXT Function

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.