Show Formulas with FORMULATEXT 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:

=FORMULATEXT(B2)

cell C2 contains FORMULATEXT formula
cell C2 contains FORMULATEXT formula

Matches Formula Bar

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

formulatext04

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.

=FORMULATEXT(INDIRECT(B4))

FORMULATEXT result shows the formula from cell B2
FORMULATEXT result shows the formula from cell B2

More on FORMULATEXT

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, you can watch this short video tutorial.

______________

0 thoughts on “Show Formulas with FORMULATEXT Excel 2013”

  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:
    =LEFT(FORMULATEXT(A1),5)
    This will be useful to quickly identify formula cells using conditional formatting.

  2. And of course you can toggle between showformulas mode and normal mode with Ctrl + Grave as you show at http://blog.contextures.com/archives/2011/09/16/excel-formulas-show-in-cell/
    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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.