FORMULATEXTformula takes a cell reference as an input and returns the formula as text that is written in the referenced cell. This can be useful for auditing and debugging spreadsheets or for dynamically referencing the formula of a cell in another part of the sheet.
- How to use
- Examples of using
FORMULATEXTformula not working?
- Similar formulas to
FORMULATEXT formula with the syntax shown below, it has 1 required parameter:
- cell (required):
The cell reference (e.g. A1) or range (e.g. A1:B2) from which to extract the formula text.
ExamplesHere are a few example use cases that explain how to use the
FORMULATEXTformula in Google Sheets.
Auditing and debugging
FORMULATEXT, you can easily extract and view formulas from referenced cells to help audit and debug your spreadsheet.
FORMULATEXT in combination with other formulas like
INDIRECT, you can dynamically reference the formula of a cell in another part of the sheet.
FORMULATEXTnot working? Here are some common mistakes people make when using the
FORMULATEXTGoogle Sheets Formula:
Incorrect cell reference
One common mistake is to reference a cell that does not contain a formula. Double check that the cell reference you provide is a cell with a formula.
Referencing a range
Another mistake is to reference a range of cells instead of a single cell. FORMULATEXT only works with single cells, so make sure to reference a single cell.
Using a text value
A mistake that can happen is to use a text value instead of a cell reference in the FORMULATEXT function. Make sure to reference the cell containing the formula you want to display as text.
Using an unsupported function
FORMULATEXT does not work with all types of formulas. If you try to use it with an unsupported function, it will return an error. Check the list of supported functions before using FORMULATEXT.
Using an invalid cell reference
If you provide an invalid cell reference to FORMULATEXT, it will return an error. Make sure to double check your cell references and correct any errors.
The following functions are similar to
FORMULATEXT or are often used with it in a formula:
CELLformula returns information about the formatting, location, or contents of a cell. The type of information returned depends on the value of the
ADDRESSformula in Google Sheets returns a cell reference as a string, given the row and column numbers. It can also return an absolute or relative reference, and can include sheet name in the reference. This formula is most commonly used to create dynamic references in other formulas or to create a cell reference based on certain criteria.
INDIRECTformula is used to return a cell reference specified by a string. This can be useful when you need to dynamically reference a cell based on the value in another cell. The
INDIRECTformula can also be used to reference cells in other sheets or workbooks.
COLUMNformula returns the column number of a given cell reference. It is most commonly used as a helper function for other formulas that require the column number as an argument.
ROWformula returns the row number of a specified cell reference. If no cell reference is provided, it returns the row number of the cell in which the formula is entered. This formula is most commonly used for data analysis and manipulation.
You can learn more about the
FORMULATEXT Google Sheets function on Google Support.