Apr 07 2021 04:58 PM
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.
Apr 07 2021 09:44 PM
@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.
Apr 08 2021 08:57 AM
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.
Apr 08 2021 11:37 AM
@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.
Apr 08 2021 12:08 PM
Apr 08 2021 12:41 PM
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
Apr 08 2021 01:08 PM
Apr 08 2021 01:17 PM
Apr 09 2021 08:17 AM
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.
Apr 09 2021 11:04 AM
SolutionApr 09 2021 08:25 PM
Sep 19 2021 12:13 PM - edited Sep 19 2021 12:13 PM
Look up "anchoring" a cell in a formula.
It is very useful for cutting and pasting formulas while keeping same column and/or row.
Sep 19 2021 01:25 PM
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.
Sep 19 2021 03:49 PM
Jun 17 2022 01:28 PM
@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
Jun 30 2022 09:02 AM
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.
Aug 12 2022 07:07 AM
@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