The FORMULATEXT function returns the formula of a specified reference as a string. In this guide, we’re going to show you how to use FORMULATEXT function and also go over some tips and error handling methods.


Supported versions

  • Excel 2013 and later

FORMULATEXT Syntax

FORMULATEXT(reference)


Arguments

reference

A cell or range reference containing the target formulas.



Examples

Cell

=FORMULATEXT(E3)
formula returns the formula in cell C3 as a text string. As a result, you can see the formula without actually triggering it.

Range

When the FORMULATEXT function is used with a range reference, it returns an array of formulas from that reference. If you do not use Ctrl + Shift + Enter key combination to generate an array, the formula will return only the first value. Alternatively, you can use a function that works with arrays to derive the results.

The following formula uses the INDEX function to get the formula in the 3rd row and the 1st column from an array. That array returns from the FORMULATEXT function with a range reference:

=INDEX(FORMULATEXT(E9:E12),3,1)

Download Workbook


Tips

  • To check whether a cell contains a formula or not, use ISFORMULA.

Issues

#N/A

You will get an #N/A error if,

  • The reference does not have a formula.
  • The return formulas contains more than 8192 characters.
  • The formula can’t be displayed due to worksheet protection.
  • An external reference file is not open.