Forum Discussion
Doug_1
Apr 07, 2021Copper Contributor
Paste formulas not working
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.
- 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.
- PeterBartholomew1Silver Contributor
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.
- Riny_van_EekelenPlatinum Contributor
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.
- Doug_1Copper Contributor
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.
- Riny_van_EekelenPlatinum Contributor
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.