Paste formulas not working

[Doug_1]

If I copy a value that has been generated by a formula in Excel and paste into a different cell and then try to convert the value in the cell to appear as a formula by using the available methods, it does not work. I am using Microsoft 365 on Windows 10. I have tried various ways to do this and I checked that automatic calculation is selected but Excel still will not show the formula in the pasted cell. I think this is a bug in the program and would like to know if Microsoft is trying to fix it. Any comments would be appreciated.

[#2]

@Doug_1 What do you mean by "and then try to convert the value in the cell to appear as a formula"?

If you select a cell that contains a formula and perhaps is formatted in a particular way, you can copy the cell and just paste it into another one.  Both the formula and the format will be pasted. Use Copy and Paste Special, Formulas if you only want to paste the formula.

If this doesn't make any sense to you, please describe the exact steps you are taking that lead to the problem.

[#3]

@Riny_van_Eekelen 

Thanks for responding to my request for help. The procedure that you describe for pasting a formula does not work for me. I will describe the exact procedure that I use. In my Excel file I have a sheet that contains many formulas. I want to copy one (or more) formula to a blank cell and make it appear as a formula. So, I select the cell containing the formula. I use Control-C to copy. I select a blank cell and right click. This brings up a menu containing Paste Special, which brings up the Paste Special menu. The second item from the top in that menu is Formulas so I select the little circle adjacent to Formulas and click OK. I believe that the formula should then appear in the cell, but it does not appear. What does appear is the number 0.00, however the formula does show in the edit window at the top of the sheet. Is there something wrong with my procedure or is Excel just not working as it should?

I am using Microsoft 365 and Windows 10.

[#4]

@Doug_1 Well, it seems that the formula is pasted correctly but that the calculation just results to zero. Click in the formula bar and see which cells ar being referenced by the formula.

[#5]

A typical formula that is being copied is: =+AG28/$J28*100. I used to be able to paste formulas but the problem suddenly appeared and will not go away.

[#6]

@Doug_1 

If you copy =+AG28/$J28*100 and paste as a formula into one cell down, formula in that cell will appear as =+AG29/$J29*100. Excel shifts relative references depends into which cell you copy the formula.

If you'd like to copy/paste it without changing the references, click on formula bar, Ctrl+A, Ctrl+C, go on another cell, Ctrl+V

[#7]

Thanks for responding. On my computer CTRL+A selects every cell on the sheet. But I am not copying and pasting normally. I am trying to paste into a blank cell only the formula that has been copied, not the numerical result of the calculation of the formula.

[#8]

In addition to my previous post, I tried to copy directly from the editing window to the spreadsheet but that did not work either. That procedure would not be useful to me because I normally want to copy a large number of cells and past the formulas into other cells. Incidentally, I reloaded my Excel from the internet using the repair program and that did not make any difference.

[#9]

@Doug_1 

After spending quite enough time trying to solve this problem, I have concluded that it is a bug in the program. Hopefully Microsoft will fix it in a later update. In the meantime I have found a work-around solution. If you select Formulas from the menus at the top of the screen, then go to the group called Formula Auditing and select Show Formulas. all of the cells in the sheet will appear as formulas. I have a very useful program called Snagit which can copy an image of cells directly from the screen. I can then paste this image into Excel.

[#10 · Accepted solution]

It sounds like you want to paste the formula text?

If you have the formulatext function, you could use it to reference the cell containing the formula:
=FormulaText(A1)

Alternatively, you could try formatting the cell as text and the copy paste the formula from the formula bar to the destination cell.

If my understanding of what you're trying to do is correct, then the paste special-formula behavior is not a bug as it has always pasted in the formula as an expression to be evaluated.

[#11]

Thanks very much for you help JMB17. I tried the formulatext function and was able to display the formulas for a selection of cells in a location of my choosing. That is exactly what I wanted to do.

[#12]

@Doug_1 

Look up "anchoring" a cell in a formula.

It is very useful for cutting and pasting formulas while keeping same column and/or row.

[#13]

@Doug_1 

Instead of copy/pasting into cells, you paste the contents of the formula bar, it is possible to transfer an exact copy of the formula from the source cell.  If, in addition, the destination cell has a number format of 'text', that will block the evaluation so you will be left with a visible formula.

As for the problem of copied cells updating when pasted to a new location, my answer was to ditch the whole sorry mess that is relative referencing.  If all referencing is done via defined names (these default to absolute references) the Excel 365 dynamic array formulas will spill to the correct size and so avoid the manual copy/down or copy/right that is so often the cause of user error.

[#14]

Hello BruceIan20, Thanks for the information. Anchoring the cell works find for a single cell but I have more than 100 cells that I need to work with. I don't think the method is practical for multiple cells.

[#15]

@Doug_1I can subscribe to that: I noticed that formulas don't copy down properly, the cells references that copied are correct, but the result of the formula is the result of the first cell above, that I copied down the formula from. I already updated my computer, have restarted it more than twice. If I click on the lower cells - the reference in the formula is correct, but result is not, it just copies the result from the first cell.... I really think this is a bug with a software 

[#16]

I'm having this issue.  Usually I copy and past a formula with no problem.  When I copy a formula and paste it to another cell, I get all zeros or it copies the exact answer of the copied cell.   If I click the new cell and look up at the bar at the top, I can see the formula!  But the answer is wrong.  I can't figure it out.

[#17]

@LynnPek  Try going to Formulas, Calculation Section, Calculation Options, and turning on automatic.

I went around and around on this for hours and thought it was because I updated to Excel 2019. I just forgot all the things I customized in my old version to make life easier

[#18]

@LynnPek I have the same problem. Please update me if you found a solution.