Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Try this in cell C1:


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

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

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

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):


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

I have a similar solution for the above problem.

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

Your Answer


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.