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