How can I display formula values instead of the formula?

[Dan G]

I have a bunch of cells that contain formulas with values from all over the
place and
I would like to be able to see the values of each formula quickly.

To simplify, using the example formula =(A1+B2)/C3*D4)
I would like to see =(1+2)/3*4

I know I can sort of do this with the watch window or I can do something
like this -
="(" & A1 &" + "& B2 &") / "& C3 &" * "& D4 - in another cell but neither of
those are what I need. The watch window does not show the whole formula at
once and the 2nd is not easily replicated for many cells.

Thanks for looking,
Dan

 

[Spiky]

Press CTRL-`. It's a toggle, so press it again to turn off.

 

[Dan G]

That seems to just show me the formula from the cell above the one that I am
currently in.

 

[Dave Peterson]

That's ctrl-backquote (the key to the left of the !/1 on my USA keyboard).

Or you could use:
tools|options|view tab|check formulas
(xl2003 menus)

 

[Dan G]

Gotcha, I was hitting single quote.
Doing this seems to show the same as hitting F2 which just shows the
formula. Am I still not getting it? (Or not explaining what I am looking for
well enough more likely...)

I am thinking that I might just be out of luck.

 

[Spiky]

I think you're right that we aren't communicating well.

F2 moves you into Edit mode in one cell.

But simpler for one cell at a time is to simply have the Formula Bar
showing and look up there to see the formula.

CTRL-` moves you into the Show Formulas view, which shows all the
formulas in the sheet. Although if they are long, it won't necessarily
show every character in the formula.

There is a UDF which is part of morefunc (search on download.com)
which will show formulas in a different cell. It's called FORMULATEXT,
and it simply shows a formula as the value in a cell. Like this:
=FORMULATEXT(A1) .....this will show whatever the formula looks like
in A1.

 

[Dan G]

OK, i thought that what I said was clear but here goes again.

I have no problem seeing the formulas. That is easy. What I want is to be
able to see the actual values that the formula uses laid out (without going
to the watch window every time).

So, using the example that I first wrote:
I can easily see =(A1+B2)/C3*D4).
I want to see =(1+2)/3*4

Basically, I want a quick way to produce the same results as
="(" & A1 &" + "& B2 &") / "& C3 &" * "& D4



Obviously this is a simplistic example, the actual formulas point across
multiple sheets etc...

I am not sure how much more explicit or clear that I can be.

 

[MikeR]

OK, i thought that what I said was clear but here goes again.

I have no problem seeing the formulas. That is easy. What I want is to be
able to see the actual values that the formula uses laid out (without going
to the watch window every time).

So, using the example that I first wrote:
I can easily see =(A1+B2)/C3*D4).
I want to see =(1+2)/3*4

Mismatched parens? How about =(A1+B2)/C3*B4
Mike

 

[Dan G]

No, that was just a miskey.
I guess i am just going to go with either the question i am asking is not
possible, is just too lowbrow for this group, or is just not something that
you can do and will continue to suck it up and just continue to do something
like this

="(" & A1 &" + "& B2 &") / "& C3 &" * "& D4 for the cells that i really need
to see what numbers are being used in the formulas.

Sadly, it takes a long time when there are 10 or 20 numbers across sheets in
the formulas.

 

[Gord Dibben]

Have a look at Dana's construction at this google search result.

http://tinyurl.com/4qnq7r

May give you a solution.


Gord Dibben MS Excel MVP

 

[Bernard]

thanks for the help
Sincerely

Have a look at Dana's construction at this google search result.

http://tinyurl.com/4qnq7r

May give you a solution.


Gord Dibben MS Excel MVP