2

I have a bunch of formulas in a column that I wish to duplicate exactly into another column, where I will go through and tweak them so that a different function is run on the same parameters. When I do a copy/paste, the cell addresses are all altered to be one higher, which is not desired. The only way that I have found is to move the cells using cut/paste, but then I destroy the source cells, leaving me without the duplicate cells.

If you want more detail, I have a column full of CORREL() calculations, and I want to duplicate them to another column where I will do SLOPE() calculations.

Chris Lieb
  • 195

2 Answers2

4

Excel has a handy little coordinate locking facility. It involves prefixing the coordinate you want to lock with a $

Say you have a formula which references cell B3 and you copy it and paste it one cell down - as you know it changes it to be B4. if you copy it one cell to the right it changes it to C3. Right and down becomes C4. It keeps the distance relative to where you paste it.

Prefixing a cell coordinate with $ locks that part of the coordinate.

So following our previous example:

Formula    Down   Right   Down+Right
B3         B4     C3      C4
$B3        B4     B3      B4
B$3        B3     C3      C3
$B$3       B3     B3      B3

It's very handy and it's nice the way you can lock only one of the axis if you want.

Majenko
  • 32,428
  • Don't know why I didn't think of that. I've been using $s a lot recently, but it hadn't occurred to me to use them here. Thanks – Chris Lieb Mar 28 '11 at 21:34
0

Alternative is to find/replace "=correl(" with "###correl(" so these are no longer valid formulas, so they don't update when copied or moved around.

Copy and paste them, then replace "###" with "=" in the original and "###correl(" with "=slope(" in the target.

AdamV
  • 6,288