A to Z of Excel Functions: The FORMULATEXT Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the FORMULATEXT function.

The FORMULATEXT function

New in Excel 2013, this is one of the most used functions in our neck of the woods.  It’s a really useful tool for documenting formulae, as FORMULATEXT returns a formula as a text string.  People have been writing User-Defined Functions (UDFs) for years to replicate that! 

The FORMULATEXT function employs the following syntax to operate:

The FORMULATEXT function has the following argument:

• reference: this is required and represents a cell or a reference to a range of cells.

It should be further noted that:

• the FORMULATEXT function returns what is displayed in the formula bar if you select the referenced cell
• the reference argument can be to another worksheet or workbook
• if the reference argument is to another workbook that is not open, FORMULATEXT returns the #N/A error value
• if the reference argument is to an entire row or column, or to a range or defined name containing more than one cell, FORMULATEXT returns the value in the upper leftmost cell of the row, column, or range
• in the following cases, FORMULATEXT returns the #N/A error value:
  • the cell used as the reference argument does not contain a formula
  • the formula in the cell is longer than 8,192 characters
  • the formula cannot be displayed in the worksheet; for example, due to worksheet protection
  • an external workbook that contains the formula is not open in Excel.
• invalid data types used as inputs will produce a #VALUE! error value
• entering a reference to the cell in which you are entering the function as the argument will not result in a circular reference warning.  FORMULATEXT will successfully return the formula as text in the cell.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.