Show a formula in another worksheet cell, with the FORMULATEXT function. New in Excel 2013.
The FORMULATEXT function can be used in troubleshooting and auditing. For example:
Watch this video to see how FORMULATEXT works, and there are written instructions below the video.
The FORMULATEXT function has the following syntax:
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.
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.
You can also reference cells on a different worksheet or another workbook.
However, if another workbook is referenced, and that workbook is not open, the formula result will be an #N/A! error.
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.
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.
Note: The result will be an #N/A! error, if the formula in the referenced cell is longer than 8192 characters.
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.
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.
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")
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.
Last updated: September 25, 2016 6:11 PM
Contextures RSS Feed