Summary
The FORMULATEXT function in Excel is used to display the formula contained in a cell as a text string. This function is highly useful for auditing and documenting the formulas used in a spreadsheet. It can help in understanding complex worksheets by showing the actual formulas used in cells instead of their resulting values.
Syntax
=FORMULATEXT(reference)
- reference: The reference to a cell for which you want to display the formula.
Return value
Returns the formula in the specified cell as a text string. If the cell does not contain a formula, it returns the #N/A error.
How to use
To use FORMULATEXT, simply enter the cell reference containing the formula you want to display as text. If the cell contains a regular value (like text or a number) and not a formula, FORMULATEXT will return the #N/A error.
Examples
Simple FORMULATEXT
Displaying a Basic Formula as Text: To show the formula of a cell that adds two numbers. Assume cell B1 contains the formula =A1 + 10:
=FORMULATEXT(B1)
This will return the text string “=A1 + 10”, revealing the actual formula used in cell B1.
FORMULATEXT for Complex Formulas
Revealing a Complex Formula in a Cell: To display a more complex formula, like a nested IF statement: If cell C1 contains =IF(A1>0, “Positive”, “Negative”), then:
=FORMULATEXT(C1)
This returns “=IF(A1>0, “Positive”, “Negative”)”, showing the detailed conditional logic used in cell C1.
FORMULATEXT with Non-Formula Cells
Handling Cells Without Formulas: To demonstrate FORMULATEXT’s behavior with a cell that contains text. If cell E1 contains the text “Hello World”, then:
=FORMULATEXT(E1)
This formula returns the #N/A error, as E1 does not contain a formula.
FORMULATEXT Circular Reference Display
Displaying Circular Reference Without Error: Using FORMULATEXT on its own cell reference in A1:
=FORMULATEXT(A1)
This formula paradoxically returns “=FORMULATEXT(A1)”, even though it’s a circular reference, demonstrating a unique quirk of FORMULATEXT.
Additional Notes
- FORMULATEXT is especially valuable for training purposes, where understanding the formulas behind the result is crucial.
- It also assists in troubleshooting and auditing spreadsheets by making the underlying formulas visible for review.
- FORMULATEXT will not show formulas in protected or hidden cells for security reasons.