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:

=FORMULATEXT(reference)

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.

Introduction

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:

=FORMULATEXT(E20)

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

HowToUseTheFORMULATEXTFunctionInExcel

Conclusion

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.

Useful Links

The FORMULATEXT Function

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

2 Responses

  1. henk.stander@petrosa.co.za' Henk says:

    Hi Taryn,
    I am curious, why do you always use ctrl-enter instead of just enter?
    Regards

    • Taryn N says:

      Hi Henk thanks for the comment, the reason I use CTRL-ENTER is because after pressing CTRL-ENTER one stays in the cell that contains the actual formula, whereas pressing ENTER takes you to one cell below the actual formula, TAB of course takes one to one cell right of the cell containing the formula. So its a preference on my part basically :-). It would actually be interesting to see what the preferences of general Excel users are – and if its related to anything else – maybe ambidextrous people for example, prefer for the most part using the TAB key. So yes one can use ENTER or TAB instead of CTRL-ENTER but I like staying in the cell that contains the actual formula.:-)

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.