Some time ago, I posted a question
asking if in Excel, there was any way to simultaneously view both the formula and the resulting value in a given cell. I was aware of the option to view only values (which is the default setting) or formulas* but I was hoping to find a way to view both simultaneously.
The solution provided by one of the experts (imnorie
) works great and I use it all the time. However, it involves an unusual process and syntax I had not seen before or since:
1) enter a formula into a cell (say A1)
2) select another cell (say B1) where you would like A1's formula to be displayed
3) open the Name Manager
4) click "New"
5) enter any name in the "Name:" box, eg "ShowFormula"
6) in the "Refers to:" box, enter =GET.CELL(6,A1)
7) click OK
8) close the Name Manager
9) in B1 enter: =ShowFormula
This is really a great trick and as mentioned, I use it all the time — especially when building formulas which are large and/or confusing. It's made me wonder what other great tricks may be available using a similar format.
By the way, in the original thread there were a couple of comments which mentioned something called "Excel4".
What type of formula is this?
What is the significance of the number 6 as the first argument?
Was Excel4 a previous version or does it refer to something else?
Does anyone have a list of similar formulas or know of a good place to find similar useful formulas?
*In Excel 2013: File > Options > Advanced > scroll to
"Display options for this worksheet:" > then tick
"Show formulas in cells instead of their characteristic results"