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

Tushar Mehta


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


    Or this


  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
    If r.HasArray Then
    getformula = “<– " & " {" & r.FormulaLocal & "}"
    getformula = "<– " & " " & r.FormulaLocal
    End If
    End Function

  8. Anthony says:

    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.

  9. Curtis Fuller says:

    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.

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply