How to show all formulas of cells in cells (and not the calculated result) ?

  • Thread starter Thread starter Keith Clark
  • Start date Start date
K

Keith Clark

Assume I get an Excel sheet with a lot of fields with assigne formulas.

Is there a way to show the formulas instead of the calculated result?

No, I don't want to click on each cell to see the detailed formula single by single
in the top entry field.
Instead all formulas should be displayed in cell itself (maybe as text)?

Is there such a view mode option?

Second question:

Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?

Example:

Original formula:

K9=A6+B7/(B8-B9)+SUM(R5:R8)

then I would like to see somewhere (in cell itself or console or wherever):

K9=2525+4335/(19-3)+SUM(6+7+8+9)

Keith
 
Hi Keith

To show the formulas in the cells,

Goto Tools > Options > Select View tab > In the "Window Options"
section check Formulas > OK

Regards,
Per
 
or press

CTRL+`



Hi Keith

To show the formulas in the cells,

Goto Tools > Options >  Select View tab > In the "Window Options"
section check Formulas > OK

Regards,
Per








- Poka¿ cytowany tekst -
Click to expand...
 
Assume I get an Excel sheet with a lot of fields with assigne formulas.

Is there a way to show the formulas instead of the calculated result?

No, I don't want to click on each cell to see the detailed formula singleby single
in the top entry field.
Instead all formulas should be displayed in cell itself (maybe as text)?

Is there such a view mode option?

Second question:

Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?

Example:

Original formula:

K9=A6+B7/(B8-B9)+SUM(R5:R8)

then I would like to see somewhere (in cell itself or console or wherever):

K9=2525+4335/(19-3)+SUM(6+7+8+9)

Keith
Keith,

Is there such a view mode option?
Click to expand...

Yes, there is such a view mode. You can use Ctrl + ~ to turn the mode
on and off.
Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?
Click to expand...

I know of only one way to get a partial answer for this. Let's say
you have a simple spreadsheet that has 5 in A1, 10 in A2, and =A1+A2
in A3. If you put your cursor in the formula in A3, highlight A2 only
(i.e. both the A and the 2), and then press F9 you'll see the formula
change to =A1+10. As a side note, be sure to hit Esc to get out of
the formula. If you hit the Enter key, your formula will be
transformed to =A1+10 instead of =A1+A2.

Best,

Matt Herbert
 
Assume I get an Excel sheet with a lot of fields with assigne formulas.

Is there a way to show the formulas instead of the calculated result?

No, I don't want to click on each cell to see the detailed formula singleby single
in the top entry field.
Instead all formulas should be displayed in cell itself (maybe as text)?

Is there such a view mode option?

Second question:

Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?

Example:

Original formula:

K9=A6+B7/(B8-B9)+SUM(R5:R8)

then I would like to see somewhere (in cell itself or console or wherever):

K9=2525+4335/(19-3)+SUM(6+7+8+9)

Keith
Click to expand...

Keith,

I guess my original post did not take.
Is there a way to show the formulas instead of the calculated result?
Click to expand...

Yes. Use Ctrl + ~ to turn this mode on and off.
Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?
Click to expand...

I know of only one way to manually do this. Let's say you have a
spreadsheet with the following data in the respective cells: 5 in cell
A1, 10 in cell A2, and =A1+A2+SUM(A1:A2) in cell A3. Select the
formula cell (i.e. A3) and do the following: highlight A1 (i.e. the
"A" and the "1" only) and hit F9; highlight A2 (i.e. the "A" and the
"2" only) and hit F9; highlight A1:A2 (i.e. the "A" and the "1" and
the ":" and the "A" and the "2" only) and hit F9. I'm not aware of a
mode that will allow you to view this manual transformation.

I do have one side note about this method. If you transform a cell
reference (e.g. A1) into the value (i.e. 5) and then hit the enter
key, your formula will turn into =5+A2+SUM(A1:A2). If you make the
mistake of hitting enter, you should be able to "undo" to get the
formula back; however, use Esc instead of Enter when you are manually
evaluating your formulas via F9.

Also, play around with different combinations for changing the
references from cells to values (e.g. highlight A1+A2 only and hit F9,
or A2+SUM(A1:A2), etc.).

Best,

Matt Herbert
 
Assume I get an Excel sheet with a lot of fields with assigne formulas.

Is there a way to show the formulas instead of the calculated result?

No, I don't want to click on each cell to see the detailed formula singleby single
in the top entry field.
Instead all formulas should be displayed in cell itself (maybe as text)?

Is there such a view mode option?

Second question:

Is there a way to show these formulas with replaced current cell values but NOT as calculated end result?

Example:

Original formula:

K9=A6+B7/(B8-B9)+SUM(R5:R8)

then I would like to see somewhere (in cell itself or console or wherever):

K9=2525+4335/(19-3)+SUM(6+7+8+9)

Keith
Click to expand...

Keith,

I guess my original post did not take.

“Is there a way to show the formulas instead of the calculated
result?”

Yes. Use Ctrl + ~ to turn this mode on and off.

“Is there a way to show these formulas with replaced current cell
values but NOT as calculated end result?”

I know of only one way to manually do this. Let's say you have a
spreadsheet with the following data in the respective cells: 5 in cell
A1, 10 in cell A2, and =A1+A2+SUM(A1:A2) in cell A3. Select the
formula cell (i.e. A3) and do the following: highlight A1 (i.e. the
"A" and the "1" only) and hit F9; highlight A2 (i.e. the "A" and the
"2" only) and hit F9; highlight A1:A2 (i.e. the "A" and the "1" and
the ":" and the "A" and the "2" only) and hit F9. I'm not aware of a
mode that will allow you to view this manual transformation.

I do have one side note about this method. If you transform a cell
reference (e.g. A1) into the value (i.e. 5) and then hit the enter
key, your formula will turn into =5+A2+SUM(A1:A2). If you make the
mistake of hitting enter, you should be able to "undo" to get the
formula back; however, use Esc instead of Enter when you are manually
evaluating your formulas via F9.

Also, play around with different combinations for changing the
references from cells to values (e.g. highlight A1+A2 only and hit F9,
or A2+SUM(A1:A2), etc.).

Best,

Matt Herbert
 
Hi
On first question do Ctr + the key with 3 symbols on it below esc. Do
it again to switch off.
I can't answer the second question.
regards
Paul
 
Back
Top