Replace the =
in the formula using Ctrl+H with another character. What I usually do is replace =
with Z=
. This will turn the formula into text since the equal sign will not be the first character in the formula.
Copy the formulas wherever you want and then do Ctrl+H and change Z=
(or whatever you chose) to =
.
How to copy formula without changing its content? [closed]

29072023  
Question
For example, in A1 I have '=B1', in A2 I have '=B2'.
If I copy A1 and A2, and paste it into another cell, the content may change according to its position. It will change into '=*1' and '=*2', which is the cell below the pasted area.
Notice:
1.It is possible to change content into $A$1
, but this means I have to manually change the content and then change it back. If I have to use the '$' way, if there is any short cut to switch between with '$' and without '$'?
2.A possible workaround is found here: http://www.wallstreetoasis.com/forums/howtocopyexactformulainexcelwithoutchangingreference
3.A similar question. Disable Excel's Automatic Cell Reference Change After Copy/Paste
Solution
OTHER TIPS
When you're creating the formula, you can use
F4
to cycle between the $ options. If you're editing a formula,F4
works only when your cursor is on the reference you want to edit.Excel does not have an option to paste multiple cells where the formula text stays the same. I suggest to replace the
=
with a random character by usingCtrlH
, then copy the cells, and then replace the random character back withCtrlH
.
Looking at your question you want the new cells to have the exact same formula, which you will then edit (I think). So 'paste link' in the 'paste special' menu, which makes formula links to the existing cells (if you copy cell A1 and A2 and 'paste link' somewhere, it creates the formulas =A1
and =A2
), will probably not be the solution you look for. But I hope shows some other options excel has available.
I will modify my answer if I have misunderstood your question. If your desire is to have your formulas set equal to the value in column B
in each corresponding row, then you can use the $
on the column portion alone.
For example, you could have =$B1
in column A
. You could drag that formula down and the row numbers would change, so you'd have =$B2
in A2
, =$B3
in A3
and so forth.
If you then copied column A
and pasted into column D
, the formulas in column D
would be =$B1
in D1
, =$B2
in D2
and so on.
Hope this is useful.