Purpose: Excel’s FORMULATEXT function enables you to display the formula in another cell. It can be useful to inspect a cell’s formula visually without having to click in it to display the formula in the formula bar.
SYNTAX — FORMULATEXT function
=FORMULATEXT (Cell reference)
SYNTAX — Example: refer to a cell on the same worksheet
=FORMULATEXT (D3)
- The above example will return the formula in cell D3 as a text string, which can be edited like any text.
- If there is no formula in cell D3, or if the cell reference is to another workbook which is closed, then an “#N/A” error is returned.
- To replace the error with a different action, you can use an IFERROR statement — see the IFERROR topic for examples.
SYNTAX — Example: refer to a cell on a different worksheet
=FORMULATEXT (Sheet2!I3)
- The above example will return the formula in cell I3 on Sheet2.
- The cell reference can be in the same worksheet, or a different worksheet, and even a different workbook (the workbook needs to be currently open to return the formula).
- To apply the FORMULATEXT function, simply type the formula into a cell.
- To repeat the formula down a column, you can drag the right-hand corner of the cell. See our quick tip video on how to copy content by filling cells down a column.
- Surprisingly, the FORMULATEXT function can refer to the cell into which it is typed. As shown in the example above, typing “=FORMULATEXT(F17)” into cell F17 will result in the text string “=FORMULATEXT(F17)”. No error is triggered, Excel doesn’t seem to mind the circular reference.