The FORMULATEXT function in Google Sheets is a powerful tool that allows users to display the formula contained in a specific cell as text. This can be particularly useful for documentation, sharing, or debugging purposes, providing clear visibility into the underlying calculations being performed.
Syntax
FORMULATEXT(reference)
- reference: The cell reference containing the formula you want to display. For example, A1.
Example #1
=FORMULATEXT(A1)
This function will return the formula from cell A1 as a string. If A1 contains the formula =SUM(B1:B10), the result will be “=SUM(B1:B10)”.
Example #2
=FORMULATEXT(B2)
If B2 holds the formula =A12, this function will show “=A12” as the output.
Example #3
=FORMULATEXT(C3)
If C3 contains =AVERAGE(D1:D5), the output will be “=AVERAGE(D1:D5)” allowing you to see the exact calculation used.
Error handling
- N/A: This error occurs if the referenced cell does not contain a formula. In such a case, FORMULATEXT cannot return a valid output.
- REF!: This indicates that the specified reference is invalid, often due to deleted cells or ranges.
- VALUE!: This error occurs when an argument is of the wrong type, which can happen if the reference provided is not a valid cell.