Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Excel FORMULATEXT Function

The FORMULATEXT function is an Excel function first introduced in Excel 2013 and later versions of Excel. The FORMULATEXT function allows users to select any cell containing a formula and return it as a text string in another cell. Therefore, it’s easy to use the Excel FORMULATEXT function. It’s also useful if users want to analyze the formulas in their worksheet alongside their results.

Overview of Excel FORMULATEXT Function


Download Working File

Download the sample Excel file to practice with.


Excel FORMULATEXT Function: Syntax and Arguments

Function Objective

To return the used formula as a string. The function takes only one argument.

Syntax

FORMULATEXT(reference)

Arguments Explanation

Argument Required/Optional Explanation
reference Required Cell containing formula 

Return Parameter

Return the used formula in the reference cell as a string or text.

Supported Versions

For Microsoft Excel 2013 and onward versions.

An Example of Using the FORMULATEXT Function

Users need to use the FORMULATEXT function if they want to display the used formula from particular cell references.

Let’s say we have the half-yearly sales of the three salespeople on a worksheet. But we use a formula to find the highest sales among them.

  • Use the following formula in cell E22.
=FORMULATEXT(E21)

Excel FORMULATEXT Function

  • Pressing ENTER results in displaying the formula used in the E21.


Alternative to FORMULATEXT to Display Showing All Formulas

As an alternative to the FORMULATEXT function, users can use the Show Formula option in the Formulas tab or press CTRL+’ to on or off the formula instead of the formula outcomes.

  • Move to the Formulas
  • Click on Show Formulas (in the Formula Auditing section).

  • Excel displays all the formulas within the active worksheet, as depicted in the below screenshot.


Using the F2 Key to Show a Specific Formula in a Cell

Sometimes it’s quite annoying to use another function to view inserted formulas. Other than the Show Formulas option, users can use the F2 function key from the keyboard to view the used formula within a cell.

  • Place your cursor in a cell, as shown in the below picture.

  • Now, press the F2 key on the keyboard to view the inserted formula.

  • Pressing the ESC key restores the cell back to the Ready mode and out of Edit.

Conclusion

This article discusses the syntax and usage of the Excel FORMULATEXT function. Also, alternatives to the FORMULATEXT function are discussed. The use of this function can be a good way of annotating your worksheet with the formulas used while learning for an Excel test or finance exam, as well as a method of analyzing the formulas in the workbook alongside their actual results.

Do check out our awesome website, ExcelDemy, to find interesting articles on Excel.

Taryn N

Taryn N

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. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

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

    • 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.:-)

  2. I too had been wondering about your use of Ctrl-Enter – Thanks for the explanation!! I’ll have to give it a go.

  3. Hi Mike thanks for the comment :-). Yes definitely and see which one of the three you prefer using.

    I am wondering now if there is some other variable that is related to whether one prefers using TAB, ENTER or CTRL-ENTER in Excel. I am right-handed so I am wondering if it (my preference) maybe is related to that, or something else entirely.

    It would also be interesting to see the results of a poll of Excel users and which of the three turned out to be the most popular.

Leave a reply

ExcelDemy
Logo