Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.