FORMULATEXT function in Excel – display formulas

Image of software function sign fx
FORMULATEXT function in Excel to display formula in another cell

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).

FORMULATEXT function in Excel combined with IFERROR function to display formula
In the example above (which calculates a running total in Excel for a list of numbers) column E is using the FORMULATEXT function to display the formulas in column D. Column F is using the FORMULATEXT function to show the formulas in column E.
  • 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.