0

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.

4 Answers 4

2

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

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.

2
  • thanks, copy from formula bar is what i'm doing now, wondering if there are other ways.
    – athos
    Aug 16, 2013 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, 2013 at 8:53
0

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

1
  • thx but not what i'm looking for
    – athos
    Aug 16, 2013 at 7:31
0

My response is to simply erase the =from the formula. This will present it without a calculation, meaning the information within the cell becomes fixed.

Copying & pasting to new cells allows you to replace the = before or after editing the formula further.

You must log in to answer this question.

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