# How can I show values in the formula bar instead of cell references?

Here is what I have in my worksheet:

A1=2 B1=3 C1=A1+B1

I want the actual cell to show `5` but when I copy `C1` I want the formula in the cell to show `=2+3`, not `=A1+B1` or `5` is there a way to do this?

-

Try this in cell C1:

``````=A1&"+"&B1
``````

This will give you the actual contents of A1 followed by a plus sign followed by the actual contents of B1, so in your example it where `A1 = 2` and `B1 = 3` you will get `C1 = 2+3`

-
Thank you for your response, but not quite what I was looking for. I want the actual cell to show "5" but the formula in the cell to show "=2+3" not "=A1+B1" – Sara Jan 31 '13 at 14:09

You can do this using F9 in the formula bar: If you select/highlight any term in your formula and press F9, it will get evaluated and the value is shown instead of the term.

E.g. in your example, if you highlight `A1` your formula will become `=2+B1`. If you highlight the full `A1+B1`, your formula will though become `=5`. Thus, you need to do it manually for each term.

Having said that, I'm not really sure I can see any use of the above technique, above from quickly checking/debugging the result of a complex formula. Though for this, the Evaluate Formula functionality (in the Formulas tab in Formula Auditing) is much better suited...

-

Excel doesn't see things that way. It uses the cell address, i.e. `A1`, to know where to get values it uses in formulas. Without the cell references the formula will not work.

The only way I know to do what you want it to actually type `=2+3` in cell C1. It will show the results of `5`, but when you select the cell you will see `=2+3`.

-

I hope I'm providing a reasonable alternative to what you've asked for. The formulas below will allow you to show the cell address and formula in another cell. That would not entail copying the original formula cell to another cell and have it show its formula rather than its value, but instead using specialized formulas in other cells to get the job done.

To show the cell address, use the following formula (I happened to use cell C12 as input to this formula):

``````  =SUBSTITUTE(ADDRESS(1,COLUMN(C12),4),"1","")&ROW(C12)
``````

To show the formula for the cell you are referencing, you will have to use a VBA function such as the following:

``````  Function ShowFormula(Rng As Range)
ShowFormula = Rng.formula
End Function
``````

A composite worksheet formula to put all this into one cell would be:

``````  =SUBSTITUTE(ADDRESS(1,COLUMN(C12),4),"1","")&ROW(C12)&"  =  "&ShowFormula(C12)
``````
-

I have a similar solution for the above problem.
A1=2
B1=3

1. Copy both 2 & 3 and then paste in other cell by using Paste special option by choosing (Transpose and values).
2. Again copy that transpose values and past in another cell by using (HOME - Clipboard-Paste All) by that both the numbers are pasted in a single cell.
3. Click on wrap Text.
4. Now in that active cell click (Ctrl+F) Find and Relapse, in Find Column type only one space and in replace column give + (Plus) Sign and click on Replace All.
5. Your data shows like 2+3 & now in that cell add = at the beginning that becomes a formula.
-