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)
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.
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))
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.
______________
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.
Good idea! You could use ISFORMULA for conditional formatting too.
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.
Scrub that last comment re hidden formulas…I was wrong. Doh.