How to Use the FORMULATEXT Function in Excel
The FORMULATEXT Function is a new Excel function, which was introduced in Excel 2013 and later versions of Excel. Using the FORMULATEXT Function will allow you to select any cell containing a formula and have it returned as a text string, in another cell. This can be useful if you want to analyze the formulas in your worksheet alongside their results.
The syntax for the FORMULATEXT Function is:
where reference is a cell or a range of cells.
So, let’s get started with a simple example to illustrate how to show formulas in a worksheet and then how to use this specific function.
Table of Contents
We have a worksheet containing Sales Data of three hypothetical sales people and formulas calculating the maximum, minimum, average and utilized for other calculations in the worksheet.
Showing All the Formulas in the Worksheet Using Show Formulas
1) We could display all the formulas in the worksheet by going to Formulas>Formula Auditing>Show Formulas.
2) This returns the results shown below, and all the formulas of the worksheet in each of the cells containing them are shown.
3) Going to Formulas>Formula Auditing>Show Formulas or by pressing CTRL+` on the keyboard, toggles the formulas off and returns the worksheet back to the standard view.
Using the F2 key to show a specific formula in a cell
1) One can also click in a specific cell containing a formula, for example, in this worksheet, select cell F18 as shown below.
2) While in the cell press the F2 key on the keyboard in order to see the formula.
3) Pressing ESC restores the cell back to the Ready mode and out of Editing Mode.
Using the FORMULATEXT Function
1) In order to have the formula in a cell delivered as a text string in another cell, you would have to use the FORMULATEXT Function.
2) We have the highest sales amount of the three salespeople shown in cell E20, so in cell E21 we are going to use the FORMULATEXT Function to show the formula and thus we type:
3) Upon pressing CTRL-ENTER =MAX(B18, F18, J18) is returned to the cell as a text string, as shown below.
And there you have it.
Download Working File
The FORMULATEXT Function delivers formulas in Excel cells, in text strings. This can be a good way of annotating your worksheet with the formulas used while learning for an Excel exam or Finance exam, as well as a method of analyzing the formulas in the workbook alongside their actual results.
Please feel free to comment and tell us if you use the FORMULATEXT Function frequently.