Applicable For: This tip works in Microsoft Excel 2013 & Excel 2016, and Office Excel 365 (for both Windows & Mac Editions)
Prior to the Excel 2013 edition, to view all the formulas in a given worksheet in Excel, we had to use the View Formulas button, or use the nifty shortcut I highlighted in another post on this topic – View All Formulas in Excel with a Single Click.
But from the Excel 2013 and onward editions (including Excel 2016 – for Windows & for Mac), we have another better way to view the formulas used in calculations. This method is fantastic, because it allows you to see the formulas, without having to flip the switch, and see only values or only formulas.
In this technique, the original values and formulas can stay where they are. We can simply use the newly introduced Excel function in a new cell, which will then show you the formula of any given cell pretty easily.
This new function is called FORMULATEXT.
=FORMULATEXT(cell_reference to a cell containing a formula)
With this new function, you can see the formula within any other Excel cell, without flipping up the on/off option. It allows you to see the value and see the formula, all at the same time. Much better than the chicken only or egg only options…
This has been specially great while teaching or showing off stuff to someone. Now you can use complex formulas, and the FormulaText function will show the formula, while the original value stays put, making it easier to understand the formula and its working, while having the value displayed directly.
Great, simple tip. I hope you like it!
Founder: ExcelChamp.Net – Effective Tips to Simplify Excel, Every Day!
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!