The Excel FORMULATEXT function

By in Excel 15, Excel Basic, Formulas, Worksheet Functions on .

With Excel 2013, Microsoft introduced several new functions, one of which is worth a separate mention. FORMULATEXT displays as text the formula in a cell.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0116%20formulatext%20function.shtml

Tushar Mehta

9 thoughts on “The Excel FORMULATEXT function

  1. Tushar Mehta Post author

    Smart, I guess. Apparently, Excel recognizes the formula doesn’t actually mess with its calculation engine and thus refrains from a circular reference error.

    Some time back (a long time back?) I noticed the same about OFFSET. In A1 enter =OFFSET(A1,0,1,1,1). No problem whatsoever. Same with =INDEX(C:C,1) in any cell in column C except C1.

  2. Pete

    So no longer need to use the old GET.CELL(6, range reference) via a named range – once I upgrade from Excel 2003…

  3. Jeff Weir

    @Alex: Good question. Particulary since you can just push Ctrl + Grave Accent key (`) to toggle between Show Formulas and Show Results. Although I do occasionally use the method Pete suggests purely so I can demonstrate what it looks like to others for learning purposes.

  4. fzz

    Why would =FORMULATEXT(A1) entered in A1 behave any differently than =ROW(A1) entered in A1?

    In these cases ROW is returning the cell’s .Row property, and FORMULATEXT is returning its .Formula property. It’s only happenstance that in the FORMULATEXT formula the cell’s .Value and .Formula properties are the same, but it matters to the recalculation engine that FORMULATEXT returns the .Formula property.

    So slowly incorporating Longre’s MOREFUNC UDFs from years ago into Excel. Boldly providing a feature Lotus 1-2-3 added 23 years ago. Better late than never.

    As for use, pity it lacks a 2nd argument like INDIRECT’s with which users could opt for R1C1 addressing. Using R1C1 addressing it could be used to check whether different cells had the same formula in R1C1 addressing, that is, were copy-paste/[Ctrl]+[Enter] equivalent.

  5. Simon Benninga

    FORMULATEXT(long time coming!) is nice, but when pointed at a cell containing a number it returns #N/A . My books contain GETFORMULA, a slight improvement. Note the “formula.local” which returns the local language version of the formula (not sure what FORMULATEXT does … )

    ‘8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
    Function getformula(r As Range) As String
    Application.Volatile
    If r.HasArray Then
    getformula = “<– " & " {" & r.FormulaLocal & "}"
    Else
    getformula = "<– " & " " & r.FormulaLocal
    End If
    End Function

  6. Anthony

    I have just been experimenting with this feature and I will get a lot of use out of it in teaching students to create formulas in excel. I can now test to see if they have created the formula that I required and automatically comment on it from an adjacent cell.

  7. Curtis Fuller

    Is it possible to do the opposite?

    For example, I’m going to have a list of references which will have formulas. (i.e. “(A+B)*E” or “((A+C)*B)/E” and user response will lookup the formula, and using named ranges I want it to calculate the answer based on the saved formula using named ranges.

    Hope that makes sense.
    Thanks!

Leave a Reply

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax