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?

6 Answers

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

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

1

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 found the solution to this. It starts with =formulatext() function, plus several nested =substitution() functions, and cell("address",[reference]).

The solution works for any kind of formula and can be copied and pasted to other cells even where the formula changes significantly.

Here's the solution tailored specifically to my needs:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(I6),"$",""),SUBSTITUTE(CELL("address",G6),"$",""),TEXT(G6,"$#,##0.00")&" "&$G$5),SUBSTITUTE(CELL("address",H6),"$",""),H6&" "&H5),SUBSTITUTE(CELL("address",F6),"$",""),TEXT(F6,"$#,##0.00")&" "&$F$5),SUBSTITUTE(CELL("address",E6),"$",""),E6&" "&$E$5),SUBSTITUTE(CELL("address",D6),"$",""),ROUND(D6,2))&" "&$D$5,"*"," × "),"-"," - "),"/"," ÷ ")

The number of nested =substitution() on the left side depends on the number of cell references you are replacing with cell values.

Cell references to row 5 are actually the column headers of the table of data that I was working with because I wanted the formula to include labels for each referenced cell value. So for example:

| Chickens | Eggs per Chicken | Total Eggs|
| -------- | ---------------- |-----------|
| 5 | 4 | 20 |
| 3 | 9 | 27 |

This solution's cell references to row 5 allow for each value reference to be labeled like this:

= 5 Chickens x 4 Eggs per Chicken

The cell reference I6 is the location of the formula that you want to transform. All other cell references on row 6 are different variables to put into your formula located at I6. As stated previously, all cell references on row 5 are column headers of the table of data so that the formula includes labels for each cell reference. The formula above supports five variables. If you want your formula to handle more variables, add more nested =substitute functions. If you want your formula to handle less variables, remove the appropriate nested =substitute functions. Modify the text() function for each cell reference to make the resulting formula look the way you want.

Reading this article helped me figure out the solution:

0

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy