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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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...

Answer 4

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)