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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project