How to use FORMULATEXT function in Excel?

The `FORMULATEXT` function in Excel is used to display the formula present in a specific cell as text. This can be particularly useful if you want to document or audit the formulas used in your spreadsheet. Here’s how to use it:

Syntax

FORMULATEXT(reference)

reference: This is the cell whose formula you want to return as text. It can be a reference to a single cell or a named range that refers to a single cell.

Steps to Use `FORMULATEXT`:

Open Excel: Launch your Excel application and open the workbook you want to work with.
Select Cell to Display Formula: Click on the cell where you want the formula’s text to be displayed.
Enter the `FORMULATEXT` function:

Begin by typing `=` to start the formula.
Enter `FORMULATEXT(`.
Click on the cell containing the formula you want to display, or type the cell reference directly.
Close the parenthesis `)` and press Enter.

Example: If you want to display the formula used in cell `B2`, you would type:

   =FORMULATEXT(B2)

View the Output: The cell will now show the formula from the referenced cell as plain text.

Important Points:

  =FORMULATEXT(Sheet2!B2)

Error Handling: If the cell you reference does not contain a formula or if it results in an error, `FORMULATEXT` will return an error (#N/A).
Protected Sheet Limitation: If the cell is on a protected sheet and you don’t have access to view the formula, FORMULATEXT will also return an error.
Function Across Worksheets: You can refer to a cell on another worksheet by including the sheet name in the reference. For example:
No Support for Arrays: `FORMULATEXT` does not support array references or range references other than a single cell.

Using `FORMULATEXT` is straightforward and allows for transparent display of formulas, which can aid in debugging or reviewing the automation logic within your spreadsheet.

facebooklinkedin