Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
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

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .