The Excel FORMULATEXT function returns a formula as a text string from given reference. You can use FORMULATEXT to extract a formula as text from a cell. If you use FORMULATEXT on a cell that doesn’t contain a formula, you’ll get an #N/A error.
Syntax:= FORMULATEXT (reference)
The FORMULATEXT function syntax has the following arguments:
- Reference here is the reference to a cell or range of cells from which we want the formula as text.
Example: Let’s look at some Excel FORMULATEXT function examples and explore how to use the FORMULATEXT function as a worksheet function in Microsoft Excel:
Now let’s understand the function more via using it in an example.
Here we have a list of output value in Values column.
We will extract the formula from the reference cell to the output.
Syntax: =FORMULATEXT(C2)
Result:
The function returns the formula of the reference cell or error if formula doesn’t exist
Based on the Excel spreadsheet above, the following FORMULATEXT examples would return:
Syntax: =FORMULATEXT(C3)
Result: =SUM(A3:B10)
Syntax: =FORMULATEXT(C4)
Result: =TODAY()
Syntax: =FORMULATEXT(C5)
Result: =A5+B5
Syntax: =FORMULATEXT(C6)
Result: =AVERAGE(A6:B10)
Syntax: =FORMULATEXT(C7)
Result: =SQRT(B7)
Syntax: =FORMULATEXT(C8)
Result: =A8/A7
Syntax: =FORMULATEXT(C9)
Result: #N/A
Syntax: =FORMULATEXT(C10)
Result: =SHEET(B10)
Note:
- #N/A error occurs when:
1. We have used the function and referred a cell that doesn’t have any formula on it.
2. We have referred it to another workbook and that workbook is not open.
3. When the formula in the cell is too long. The function supports only 8192 characters.
4. If the worksheet cannot be opened as it is a protected worksheet.
- #VALUE! error occurs when an invalid data type is used as input.
- Suppose we are using an Excel worksheet with lots of data and formulas. Now we aren’t sure whether a formula was used in all rows of, let’s say, column D. In this scenario, we can use ISFORMULA to find out the same and then apply FORMULATEXT function.
- We entered too many arguments. This error would pop up when we give reference to more than one cell.
- If the reference given in the function is for more than one cell, it will give us the formula that is in the upper left cell of the given range.
- If we are using older versions of Excel, we need to use VBA to get the Formula used in the cell.
