-2

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/how-to-copy-exact-formula-in-excel-without-changing-reference

3.A similar question. Disable Excel's Automatic Cell Reference Change After Copy/Paste

Community
  • 1
  • 1
ZK Zhao
  • 19,885
  • 47
  • 132
  • 206

3 Answers3

6

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 =.

shA.t
  • 16,580
  • 5
  • 54
  • 111
ApplePie
  • 8,814
  • 5
  • 39
  • 60
1

  1. 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.

  2. 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 using Ctrl-H, then copy the cells, and then replace the random character back with Ctrl-H.

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.

DoubleYou
  • 1,057
  • 11
  • 25
0

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.

Nicholas Flees
  • 1,943
  • 3
  • 22
  • 30
  • It's OK to use `$`. But how can I quickly add `$` to an selected area? – ZK Zhao May 16 '14 at 01:03
  • You can to manually select the ranges and hit F4 to change it. If there is a very simple pattern in your formulas you could try using Ctrl+H on the column (for example `A`) and replacing it with `$A$` which would essentially lock it. However, you risk breaking your formulas. In this case, the simple presence of the letter A anywhere in the formula would break that method. – ApplePie May 16 '14 at 01:05