FORMULATEXT
The FORMULATEXT function returns the formula in a cell as text. Use it to display or refer to the formula itself within another cell. This can help in auditing or documenting your spreadsheet.
Syntax 🔗
=FORMULATEXT(reference)
reference | The reference to the cell containing the formula you want to extract as text. |
About FORMULATEXT 🔗
The FORMULATEXT function in Excel lets you view the actual formula in a cell, providing clarity on how your data is computed. To use it, just reference the cell with the formula you want to display. FORMULATEXT returns the formula as text, which you can then use in other parts of your spreadsheet. This is useful for documentation, troubleshooting, or teaching, making it easier to understand and communicate your Excel models.
Examples 🔗
If cell A1 contains a formula '=SUM(B1:B5)', you can display this formula as text in cell C1 using the FORMULATEXT function:
=FORMULATEXT(A1)
This will show the text '=SUM(B1:B5)' in cell C1, displaying the formula from cell A1.
For a more complex formula in cell D2, such as '=IF(A2>B2, A2 - B2, B2 - A2)', and you want to display it in cell E2 for reference, apply FORMULATEXT as shown:
=FORMULATEXT(D2)
This will display the formula '=IF(A2>B2, A2 - B2, B2 - A2)' as text in cell E2.
Notes 🔗
The FORMULATEXT function displays the formula in a cell as text within the same workbook. It does not work with formulas from a different workbook. Use this function to improve the readability and transparency of your spreadsheet calculations by showing the underlying formulas.
Questions 🔗
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.