Excel FORMULATEXT function

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.

Related Functions

Excel ISFORMULA function

The Excel ISFORMULA function determines if a cell contains a formula, returning TRUE or FALSE, useful for formula auditing.

Content Navigation