The Excel FORMULATEXT function

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

7 Comments

  1. Did you notice that FORMULATEXT doesn’t generate any circular reference errors? Put this in cell A1:

    =FORMULATEXT(A1)

    Or this

    =MID(FORMULATEXT(A1),18,2)

  2. Tushar Mehta says:

    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.

  3. Pete says:

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

  4. Alex Godofsky says:

    What is the use case for this?

  5. Jeff Weir says:

    @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.

  6. fzz says:

    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.

  7. 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

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: