FORMULATEXT Function in Google Sheets: Extract Formula from a Cell as Text

Published on

The FORMULATEXT function in Google Sheets allows you to extract the formula from a cell as text, without changing the spreadsheet’s view mode. While the View > Show formulae option (or pressing Ctrl+`) shows all formulas in a sheet, the FORMULATEXT function helps you get a formula from a specific cell as a text string.

Manual Methods to Copy a Formula from a Cell

Without using a formula, here are two ways to manually copy a cell’s formula:

  • Click the cell: The formula appears in the formula bar. You can copy it from there.
  • Double-click the cell or press F2: Highlight the formula directly inside the cell and copy it.

You can then paste the copied formula using Paste special > Paste values. However, this manual approach is static. If the original formula changes, the pasted version won’t update.

Use the FORMULATEXT Function to Extract a Formula

To extract a formula as a live-updating text string, use FORMULATEXT.

Syntax:

FORMULATEXT(cell)
  • cell: The reference to the cell that contains the formula.

Example:

Assume the formula in cell C2 is:

=A2 * B2

To display this formula as text in cell D2, use:

=FORMULATEXT(C2)

Now, D2 will show the exact formula from C2. If you ever update the formula in C2, the value in D2 will update automatically.

Reasons to Use the FORMULATEXT Function in Google Sheets

Here are a few practical uses of the FORMULATEXT function:

  • View multiple formulas at once
    Normally, you can only see a formula when selecting a single cell. With FORMULATEXT, you can display formulas from multiple cells at once in a different area of the sheet.
Viewing multiple formulas at once with the FORMULATEXT function in Google Sheets
  • Check if a cell contains a formula
    FORMULATEXT returns a #N/A error if the referenced cell doesn’t contain a formula. This makes it useful for formula validation.

Tip: You can also use ISFORMULA if you only need a TRUE/FALSE result.

  • Use in conditional formatting
    Want to highlight cells containing specific functions (e.g., IMPORTRANGE, QUERY, etc.)? Combine FORMULATEXT with SEARCH or REGEXMATCH.

Check out these detailed tutorials:

How to Remove the Equal Sign from an Extracted Formula

The formula returned by the FORMULATEXT function always begins with an equal sign (=). To remove it, use REGEXEXTRACT:

=REGEXEXTRACT(FORMULATEXT(C2), "^=(.*)$")

This removes the equal sign and returns just the formula body.

Important Notes

  • FORMULATEXT is not an array-enabled function. If you try to use it across a range (e.g., FORMULATEXT(A1:A10)), it will only return the formula from the first cell (A1).
  • It only works on formulas. If the referenced cell contains plain text or a number, you’ll get a #N/A error.

Conclusion

The FORMULATEXT function is a simple yet powerful way to extract formulas from cells as text in Google Sheets. It’s especially useful for documentation, debugging, auditing, or creating formula-based templates.

Whether you’re building an internal tracker or a shared report, this function helps make your formulas more transparent and easier to manage.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

2 COMMENTS

  1. So I would like to extract formula from a cell, make some adjustments to that formula, and then reuse it in a different cell. Is this possible?

    • Hi, Ian Propst-Campbell,

      It would be possible only with certain functions like Query, Importrange, Indirect function in which some of the elements are used within quotes as strings. That specific parts you can replace. If you replace any variable, you may need to then use Indirect.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.