FORMULATEXT Function in Excel - Formula, Examples, How to Use

In This Article

What is the FORMULATEXT Function?

The FORMULATEXT function in Excel returns the formula displayed in the referenced cell. This function is useful for identifying the cell formula you want to reference so you can use it later.

FORMULATEXT is useful for retrieving formulas, not limited to mathematical ones, from referenced cells. This function can identify formulas embedded within text strings.

This article provides guidance on the syntax, functions, and usage of the FORMULATEXT method, along with examples for better comprehension.

Key Takeaways

The FORMULATEXT function in Excel retrieves and displays the formula from a referenced cell as a text string, aiding in understanding calculations and facilitating analysis.
The syntax is =FORMULATEXT(reference), where reference is the cell containing the formula to be extracted.
It can be applied to various types of functions, including mathematical formulas and text strings embedded with formulas, providing insights into how calculations are performed.
Additionally, FORMULATEXT can be combined with the LEN function to determine the length of the formula, which is helpful in assessing complexity and ensuring it doesn't exceed Excel's character limit.

FORMULATEXT Function Formula

The FORMULATEXT function is a lookup and reference tool in Excel that returns the formula of the referenced cell as a text string. The reference could even be from another worksheet or workbook. It was first introduced in Excel 2013.

This tool is resourceful for showing us the formula behind the referenced cell and its calculation. Understanding the calculation method used is crucial for analysis.

Let's see where we can find the function in Excel below:

The syntax for this equation is

=FORMULATEXT(reference)

where,

reference = the cell you highlighted where you want to get the calculation.

FORMULATEXT can work for various types of functions, including mathematical formulas and other functions.

Note

This function can be helpful in any sector, whether for work or school.

How to use the FORMULATEXT Function in Excel

We will show you how to use the FORMULATEXT function in this example. Solving this calculation is fairly simple and easy to use. All you have to do is find the formula for the reference you want to get by using the syntax above, like in the screenshot here:

Part Two

Interpretations

In column C:

As we can see in the screenshot above, we found the formulas for the sum equations in C3 and C4
In the screenshot below, in column C5, we also found that this tool could work for equations and phrases

Part Three

So, as we can see in this screenshot above, this equation returns the formula used in the input from column B and gives us the formula in column C.

In another example, we will demonstrate how to use the FORMULATEXT for mean, average, and mode functions.

As you can see here, cells C6-C11 give the formulas to find the answers in B6-B11. However, if you look closely at cell B7, it gives us the formula for the calculation from C7 but returns #N/A as the answer.

The #N/A represents an error in the calculation. In this instance, the error is that the mode function returns #N/A because there is no mode, meaning no number is repeated more than once in cells J5 through J7.

However, the function incorrectly identified 44 in cells L5-L10 as the mode.

For the average, it adds the numbers in the referenced cells and divides the sum by the count of cells. For B6, it gave 25 because (23 + 25 + 27)/3 equals 25. The same calculation applies to cells L5-L10:

(62 + 57 + 44 + 44 + 52 + 87)/6 = 57.667 in B11

The median represents the middle value in a set of numbers. In B9, we got 25 because it is the median of 23, 25, and 27. In B10, we got 54.5 because it is the average of the two middle numbers, 52 and 57.

So we added them together, divided them by two, and got 54.5 for the median.

Length of the formula using FORMULATEXT

We can also use this tool to find the length of the formulas from the reference by using the following syntax:

=LEN((FORMULATEXT(REFERENCE)))

where LEN represents the number of characters of a cell.

Part Six

As you can tell, the length function gave me the number of characters within the functions we calculated. For example, D3 indicates there are 11 characters in total from B3, D4 indicates 23 characters from B4, and D5 indicates 36 characters from B5.

This is resourceful because it tells us how long certain functions are and can give us insight into how they are constructed. It can also tell us when we reach the limit by ensuring it is no longer than 8192 characters.

If the function has more than 8192 characters, it will return a #VALUE! error message, indicating that the character limit has been exceeded.

FORMULATEXT Function FAQs

What is the purpose of the FORMULATEXT function?

This function aims to find the cell's formula you have chosen to reference.

What does the FORMULATEXT function return if it does not have a formula?

It returns the #VALUE! Error if there is an invalid character typed in the function.

It returns the #N/A Error if:

There is no formula
It is longer than 8192 characters
The worksheet is protected or closed

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

FORMULATEXT Function