Show Formulas with FORMULATEXT in Excel 2013

There is a new function in Excel 2013 – FORMULATEXT – that lets you show the text from a cell’s formula.

In the screen shot below, cell C2 contains the formula:



It shows the formula that’s in cell B2, just as if you had clicked on cell B2 and looked in the formula bar.


Use FORMULATEXT for Troubleshooting

You can use FORMULATEXT for auditing or troubleshooting a worksheet. For example, combine FORMULATEXT with the INDIRECT function, to check the formula in any cell.

In the screenshot below, a cell address (B2) is entered in cell B4, and the FORMULATEXT result shows the formula from cell B2.




For more FORMULATEXT information and examples, please visit my Contextures website. You can read the details there, and download the sample file: Excel FORMULATEXT Function

Video: Excel FORMULATEXT Function

To see the steps for creating a FORMULATEXT function, and a few examples, please watch this short video tutorial.

Or watch on YouTube: Get Formula Text with Excel 2013 FORMULATEXT Function


You may also like...

4 Responses

  1. It’s really odd that it doesn’t generate a circular reference error — even if you use it with another function. For example, put this in cell A1:


    This will be useful to quickly identify formula cells using conditional formatting.

  2. Jeff Weir says:

    And of course you can toggle between showformulas mode and normal mode with Ctrl + Grave as you show at

    Aside: What’s interesting about the Ctrl + Grave funcitonality is that it shows formulas even if you’d intentially set them to be hidden (i.e. if you ticked the ‘Hidden’ checkbox on the Protection tab of the Format Cells dialog box, and then locked the spreadsheet.)

    I better do a find and replace on =N(“You are the worse boss I’ve ever worked for”)+NOW() before my boss reads this.

  3. Jeff Weir says:

    Scrub that last comment re hidden formulas…I was wrong. Doh.

Leave a Reply

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