Contextures

Show Formulas with Excel FORMULATEXT

Show a formula in another worksheet cell, with the FORMULATEXT function. New in Excel 2013.

How Could You Use FORMULATEXT?

The FORMULATEXT function can be used in troubleshooting and auditing. For example:

  • Show a cell's formula
  • Calculate the length of a formula
  • Show the formula in a specific cell
  • Show the formula, or message if cell does not contain a formula

FORMULATEXT function

Watch this video to see how FORMULATEXT works, and there are written instructions below the video.

FORMULATEXT Syntax

The FORMULATEXT function has the following syntax:

  • FORMULATEXT(reference)
    • reference can be a single cell or range of cells

FORMULATEXT Tips and Traps

  • If the reference is to a range of cells, the result is the value in the top left cell of the range.
  • The reference can be to the cell that contains the FORMULATEXT formula -- a circular reference warning won't be created.
  • The result is an #N/A error if:
    • The referenced cell does not contain a formula.
    • The formula in the referenced cell is longer than 8192 characters.
    • Worksheet protection prevents the formula from being seen
    • The reference is to another workbook that is closed.
  • The result is a #VALUE! error if an invalid data type is referenced

Example 1: Show a cell's formula

The FORMULATEXT function returns a text string that shows the formula in a worksheet cell. The following formula is entered in cell C2, and it shows the formula entered in cell B2.

=FORMULATEXT(B2)

FORMULATEXT function

The result is the same text that is displayed in the formula bar if you select the referenced cell. In the screenshot below, cell B2 is selected, and you can see its formula in the formula bar. The FORMULATEXT result in cell C2 is the same.

FORMULATEXT function

You can also reference cells on a different worksheet or another workbook.

FORMULATEXT function

However, if another workbook is referenced, and that workbook is not open, the formula result will be an #N/A! error.

FORMULATEXT function

If you reference a range of cells, the formula result will show the formula in the upper left cell of the referenced range. In the screenshot below, the formula in cell B8 is returned.

FORMULATEXT function

Example 2: Calculate length of formula

You can use FORMULATEXT with the LEN function, to calculate the length of a cell's formula.

In cell B4, the LEN formula on its own calculates the length of the formula result in cell B2 -- 16 characters.

IN cell B5, the FORMULATEXT function is included, and it calculates the length of the formula in cell B2 -- 36 characters.

=LEN(FORMULATEXT(B2))

FORMULATEXT function

Note: The result will be an #N/A! error, if the formula in the referenced cell is longer than 8192 characters.

Example 3: Show formula in specific cell

To create a troubleshooting and auditing tool, you can use FORMULATEXT with the INDIRECT function. Then, type a cell address in the referenced cell, to see the formula in the specified cell.

In the screenshot below, a cell address (B2) is entered in cell B4, and the FORMULATEXT result shows the formula in cell B2.

=FORMULATEXT(INDIRECT(B4))

FORMULATEXT function

Example 4: Show formula or message

If the FORMULATEXT function refers to a cell that does not contain a formula, the result is an #N/A! error.

In the screenshot below, cell B2 contains a constant value -- 45 -- instead of a formula. The result in cell C2 is an #N/A! error.

=FORMULATEXT(B2)

FORMULATEXT function

You can use the ISFORMULA function (new in Excel 2013), to check for a formula in the referenced cell. If there is a formula, show the formula's text. If there is no formula, show a message, such as, "Not a formula"

In the screenshot below, cell B5 contains a constant value -- 45 -- instead of a formula. The result in cell C5 is the message, "Not a formula".

=IF(ISFORMULA(B5),FORMULATEXT(B5), "Not a formula")

FORMULATEXT function

Download the Sample File

To see the formulas used in today's examples, you can download the FORMULATEXT function sample workbook. The file is zipped, and is in Excel 2007/2010 file format.

Excel Functions Tutorials

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Function

INDIRECT Function

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 25, 2016 6:11 PM
Contextures RSS Feed