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: With the formula =C1+B1 in cell D1: With the text “apple” in cell F1: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
