What is FORMULATEXT Function in Google Sheets?
The FORMULATEXT function in Google Sheets returns a formula as a text string when we give a cell’s reference as input. FORM0ULATEXT displays the formula in another cell, making it an excellent resource for deciphering complex formulas in your spreadsheets. Using FORMULATEXT, you can show the formula used for calculation in other cells in a separate location. If the referenced cell does not contain a formula, FORMULATEXT returns a #N/A error.
For example, cell A3 contains the formula =SUM(A1, A2). Now, use the following formula in cell A4: =FORMULATEXT(A3). The FORMULATEXT in Google Sheets is beneficial for avoiding errors when using complex formulas for large volumes of data.
Key Takeaways
- FORMULATEXT in Google Sheets is used to display the formula in a cell as a text. It is very helpful as it shows the actual formula applied which can be useful in debugging.
- The syntax of the FORMULATEXT formula in Google Sheets is as follows:
=FORMULATEXT(reference)
reference: The cell reference whose formula you want to display as text.
- If the referenced cell does not contain a formula, you get an error #N/A.
- FORMULATEXT works only on those cells that contain formulas. It is not helpful with values or plain text.
- It is helpful when you want to keep track of all the complex formulas in your spreadsheet without showing the calculated result.
Syntax
The best thing about FORMULATEXT is that its syntax is straightforward. The function has just one argument: the cell reference of the formula to be displayed.
=FORMULATEXT(cell_reference)
For instance, if you have a formula in cell C1 and want to display it, enter the following formula in any empty cell.
=FORMULATEXT(C1). As mentioned earlier, to avoid the #N/A error, ensure you are referencing a cell with a formula.
How to Use FORMULATEXT Function in Google Sheets?
We can use the FORMULATEXT function in two ways.
- We can enter the function manually in a cell.
- Another way is entering the formula through the Google menu bar.
Entering FORMULATEXT in Google Sheets Manually
Take a simple example where you can use the FORMULATEXT function to retrieve a formula in Google Sheets.
Step 1: In the example below, we have used some formulas in various cells and computed the result.
Step 2: To check which cell contains what formula, we can apply the following function. Instead of clicking on each cell and checking the formula, we utilize the FORMULATEXT function. Enter the following formula in cell B1 and Press Enter.
=FORMULATEXT(A1)
You get the result of the first formula used in A1.
Step 3: Now, using the Autofill option, drag it down so that the formula can be applied to the subsequent cells from B2 to B4.
The formula has been applied to all the cells from A1 to A4 to retrieve their formulas. Thus, the FORMULATEXT function retrieves the formula in a specified cell reference in the text form.
Through the Google Menu Bar
You can also use FORMULATEXT in Google Sheets shortcut through the menu bar in Google.
Place the cursor where you want the formula to be entered.Then, go to Insert -> Function and click on the drop-down arrow in Info. Select the FORMULATEXT function.
Examples
It is important to document your formulas whenever you are using a complex spreadsheet. This can be done easily using FORMULATEXT, making your sheet more transparent and simpler to understand.
It is especially useful in various scenarios, such as calculating sales and finding sales growth. Let us use FORMULATEXT to display the formulas for different scenarios, giving users an insight into how these numbers are obtained.
Example #1
Let us look at an example to see how we can implement the FORMULATEXT function. Below, we have a table containing many values. We aim to check if the values are obtained from a formula and print the formula if present or a message.
Step 1: Before enteringthe formula, let us see what can be done in case of an error. If there is no formula and you use the FORMULATEXT in Google Sheets, it gives you the #N/A error. To avoid this, we use the IFNA function in Google Sheets.
Hence, we can enter the following formula in cell B2.
=IFNA(FORMULATEXT(A2),”No formula”)
The IF function checks if cell A2 contains a formula. If yes, it prints the formula returned by the FORMULATEXT function, else it prints “No formula.”
Step 2: Press Enter. You get the formula for cell A2.
Step 3: Drag the Autofill from B2 to B9. You get the formula with the help of the FORMULATEXT function for those cells containing a formula. Alternatively, you get the “No formula” message if the cell does not contain a formula.
Example #2 – Using FORMULATEXT With CELL Function
Here is another interesting example where we have a Boolean value. We can use CELL to check if a cell contains a TRUE value and then use FORMULATEXT to display the formula if TRUE exists or simply the cell value if it’s not a TRUE.
Step 1: Enter the details in the sheet below.
Step 2: The CELL function in Google Sheets works as shown below.
It returns the information about the specified cell. Its syntax is as follows:
CELL(info_type, reference)
- info_type – The type of information needed.
- reference – The reference to the cell.
Some common info types we use include “address,” “contents,” “row,” etc.
Let us apply the following function in cell B1.
=IF(CELL(“contents”,A1)=TRUE,FORMULATEXT(A1),A1)
Explanation:
- CELL(“contents”, A1): This function returns the content of cell A1, either TRUE or FALSE.
- If TRUE, FORMULATEXT(A1) will display the formula as text.
- If there’s no TRUE, it will return just the value in the cell.
Step 3: Press Enter. You observe that the value FALSE is printed. Now, drag the formula from B1 to B6.
You notice that whenever the value in Column A is FALSE, FALSE is printed and whenever it is TRUE, the formula is printed.
Example #3 – Using FORMULATEXT With IFERROR Function
Using the FORMULATEXT function and the IFERROR function is helpful when you might have to handle an error. In this example, we use IFERROR to handle errors and display the formula as text or a custom message when an error occurs.
We have a table with some numbers. We use some formulas on them. Let us look at the table first.
Here, we have applied some formulas to the values in Column A and got some results.\
Step 1: Now, let us apply a formula to Column D where we display the formula or a custom message if there’s an error.
Apply the following formula in cell D2. Then, drag it to D3, D4, and D5, respectively.
=IFERROR(B2,”Error”)
It displays the cell value if there is no error in B2 and the error message otherwise.
Explanation:
- IFERROR(B2,”Error”)
- If the cell B2 contains an error, the IFERROR function will catch the error.
- If there is an error, the formula will display the message “Error.”
- If there is no error, it will display the value in Column B.
Step 2: Apply the following formula in cell D2.
=IF(C2=”Error”, “Erroneous calculation”, FORMULATEXT(B2))
Here, we check if cell Column C has an error. If so, we display the text “Erroneous calculation.”
Else, we display the formula used using FORMULATEXT. Drag the formula till D6.
Important Things to Note
- The FORMULATEXT function can be used with functions like IFERROR to block out errors in the formulas.
- The FORMULATEXT argument can also refer to another worksheet or workbook; however, it should be open, or you will get a #N/A error.
- We can use the ISFORMULA function and the FORMULATEXT function to check if a formula is present in a cell which helps avoid the #N/A error.
Frequently Asked Questions (FAQs)
The FORMULATEXT function returns a formula as a text string from a cell reference. It is handy for displaying or documenting the actual formula used in a cell. The retrieved formula can be used as a text input to another formula.
The FORMULATEXT causes errors under the following conditions. The #N/A error occurs when the workbook or worksheet accessed by the formula is not open. Finally, It also gives a #N/A error when the referenced cell does not contain a formula or is blank. It also gives a #VALUE! error when an invalid data type is used.
N/A Error
• We get this error when the referenced cell doesn’t contain a formula
• If the worksheet reference mentioned is not open
#VALUE Error
• When an invalid data type is supplied as an argument.
#REF!
• You’re may get this error if the cell reference is invalid.
When the referenced cell does not contain a formula or is blank, you get an N/A# error. You can handle the #N/A error with the IFERROR function to display a custom message.
For instance, =IFERROR(FORMULATEXT(B1), “No formula in B1”) displays “No formula in B1” if it does not contain a formula.
Download Template
This article must help understand FORMULATEXT Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is FORMULATEXT Function in Google Sheets. We learn its syntax & how to use it to find the formula in a cell with examples. You can learn more from the following articles. –
Convert Function in Google Sheets
Leave a Reply