The FORMULATEXT function in Excel is used to display the formula present in a given cell as text. This is especially useful when you want to document or analyze formulas in a sheet without manually typing them out. It shows the exact formula that the cell contains, even if the formula returns an error or a blank value.
Syntax
| Function | Description |
|---|---|
| FORMULATEXT(reference) | Returns a text string that represents the formula in the referenced cell. |
reference: A required argument, which is the reference of the cell whose formula you want to extract as text.
Example
Let's say you have a table with a few calculated values, and you want to show the formulas used in specific cells. Below is a table with calculated values and their formulas shown using the FORMULATEXT function.
| Cell | Formula | Value | FORMULATEXT |
|---|---|---|---|
| A2 | =B2*C2 | 200 | =FORMULATEXT(A2) |
| B2 | =100 | 100 | =FORMULATEXT(B2) |
| C2 | =2 | 2 | =FORMULATEXT(C2) |
In this example, the FORMULATEXT function is applied to cells A2, B2, and C2 to extract their respective formulas. You can see that the FORMULATEXT function allows you to visualize the formula present in each cell.
Practice Exercise 1
Task: You have a set of data where you need to calculate the total sales and then use the FORMULATEXT function to display the formula used for these calculations.
Hint: In the "Total Sales" column, calculate the product of price and quantity, then use FORMULATEXT to display the formula used in that column.
Practice Exercise 2
Task: You have a table that shows commission rates based on the total sales and you want to calculate the commission amount, followed by using the FORMULATEXT function to display the formula.
Hint: For the "Commission" column, multiply "Total Sales" with the "Commission Rate", then apply the FORMULATEXT function in the adjacent column to display the formula used in the commission calculation.
Solution to Exercise 1
The FORMULATEXT function for each row is:
=FORMULATEXT(D2)
=FORMULATEXT(D3)
=FORMULATEXT(D4)Solution to Exercise 2
The FORMULATEXT function for each row is:
=FORMULATEXT(D2)
=FORMULATEXT(D3)
=FORMULATEXT(D4)