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

[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

 

[Per Jessen]

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

 

[Jarek Kujawa]

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 -

 

[Bernd P]

Hello Keith,

Maybe not exactly what you are looking for, but I hope it leads you
into the right direction:
Formulas:
http://www.sulprobil.com/html/get_cell.html
Formula/parameter values:
http://www.sulprobil.com/html/watermarks.html

Regards,
Bernd

 

[meh2030]

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?

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?

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

 

[meh2030]

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,

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

 

[meh2030]

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,

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

 

[paul.robinson]

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

 

[paul.robinson]

Hi
Look at this thread for the second question
http://groups.google.ie/group/micro...erence+with+value+in+formula#d26f2d9079cfeadf

regards
Paul

 

[rpick60]

Press CTRL + ` (grave accent).

 

[rpick60]

Press CTRL + ` (grave accent). Will show all formulas