FORMULATEXT

The FORMULATEXT function returns the formula in a cell as text, allowing you to display or refer to the formula itself within another cell.

Syntax

=FORMULATEXT(reference)

reference The reference to the cell containing the formula you want to extract as text.

About FORMULATEXT

When you find yourself seeking transparency in Excel formulas, the FORMULATEXT function unveils the underlying calculations with ease. It acts as a window into the intricate mechanisms behind your spreadsheet wizardry, offering clarity and insight into the formulas governing your data manipulations.

To employ FORMULATEXT effectively, you simply reference the cell containing the formula you wish to reveal. This function then dutifully presents the formula as text, allowing you to capture and showcase it elsewhere in your spreadsheet. Whether for documentation purposes, troubleshooting, or educational endeavors, FORMULATEXT emerges as a valuable ally in unraveling the mysteries of Excel formulas.

With FORMULATEXT, you gain the power to extract and display formulas with simplicity and precision, empowering you to understand, communicate, and utilize your Excel models more effectively.

Examples

Suppose cell A1 contains a formula '=SUM(B1:B5)'. To display this formula as text in cell C1, use the FORMULATEXT function:

=FORMULATEXT(A1)

This will output the text '=SUM(B1:B5)' in cell C1, revealing the formula from cell A1.

If you have a more complex formula in cell D2, like '=IF(A2>B2, A2 - B2, B2 - A2)', and you want to showcase it in cell E2 for reference, you can utilize FORMULATEXT in the following way:

=FORMULATEXT(D2)

This will present the formula '=IF(A2>B2, A2 - B2, B2 - A2)' as text in cell E2.

Questions

Can I use the FORMULATEXT function to extract formulas from cells containing functions?

Yes, FORMULATEXT can extract formulas from any cell, whether it contains a basic arithmetic operation, a built-in Excel function, or a complex nested formula. It provides the formula in text format, regardless of its complexity.

Is FORMULATEXT useful for documenting complex Excel models?

Absolutely! FORMULATEXT serves as a valuable tool for documenting and explaining complex Excel models. By displaying formulas as text, you can create detailed documentation within your workbook, aiding in understanding, auditing, and sharing your work.

Can I use FORMULATEXT to extract formulas from hidden cells?

Yes, FORMULATEXT can extract formulas from both visible and hidden cells. It reveals the formula contained in the referenced cell, regardless of the cell's visibility settings.

Does FORMULATEXT work with dynamic arrays and spill ranges in Excel?

Yes, FORMULATEXT is fully compatible with dynamic arrays and spill ranges in Excel. You can reference cells with dynamic array formulas, and FORMULATEXT will accurately display the formulas in text format.

Related functions

CELL
INFO