Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

suppose cell A1 has formula "=B1+C1", if B1 is 2 and C1 is 3, A1's value will be 5.

Now, I'd like to "clone" cell A1 to B4, how could i do that?

If I just copy and paste, cell B4's formula would actually be "=C4+D4". This is the same if I just copy & paste, or choose "paste special" then "formula".

I could "cut and paste", then B4's formula would be "=B1+C1", but A1 will be empty.

How could I "clone" the content of A1 to B4?

--- additional note:

I know the difference of "B1", "B$1", "$B1" and "$B$1". I'd like to know a way that general to any of these kinds of usage.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

I will usually do this in either of the following ways:

  • I will get the skinny plus drag the formula down then drag the cell
    with the formula to where I want it. Then I will drag the formula up in the original column which will copy back the formula. (see image below) enter image description here

Or

  • I will put an apostrophe in before the equal ("=") sign which will turn the cells' contents to text. Then I can copy the cell to the destination and take the apostrophe out of both cells. Using keyboard F2, Home delete, enter. to quickly remove apostrophe.
share|improve this answer
    
never thought of the drag! thanks! –  athos Nov 14 at 2:28

Hmm, couple of ways:

  • You can copy the formula from the formula bar and paste it in B4. Might be a bit time consuming if you have a lot of formulae though.

  • You can lock the references in the formula in A1, and then the formula will not change when you copy paste it in other cells. For instance, if you change the formula in A1 to:

    =$B$1+$C$1
    

    This will allow you to copy/paste the formula to other cells. You can type the dollar signs manually, or, upon typing the formula, press F4 in succession depending on the type of locking you want to get.

share|improve this answer
    
thanks, copy from formula bar is what i'm doing now, wondering if there are other ways. –  athos Aug 16 '13 at 7:34
    
@athos You can also double click the cell containing the formula and copy it there, otherwise, using the dollars would be my approach :) –  Jerry Aug 16 '13 at 8:53

You are using relative references. You need to use absolute references by adding "$".

"=$B1+$C1" will fix the columns

"=B$1+C$1" will fix the rows

"=$B$1+$C$1" will fix the columns AND rows

share|improve this answer
    
thx but not what i'm looking for –  athos Aug 16 '13 at 7:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.