Forum Discussion
Paste formulas not working
- Apr 09, 2021It 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.
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.
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.
- Doug_1Apr 08, 2021Copper ContributorA 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.
- SergeiBaklanApr 08, 2021Diamond Contributor
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
- Doug_1Apr 08, 2021Copper ContributorIn 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.