Copy & Paste in Excel

[DaveNamou]

I have 80 cells that have formulas within them. I am trying to copy and paste them to another column to duplicate them.

As you know, Excel changes the formula by adding a value to the formula to match the number of cells you moved. So if my formula was =sum(F5:G5) and then I copy and paste it 5 rows to the right, it changes to =sum(K5:L5).

How do I cut and paste the EXACT formula without the changes?

Also, just for future reference, is there a way to cut and paste a formula and only have ONE of the values in the formula change?

Thanks in advance!!! Dave Namou, MCSE CCEA

 

[euskadi]

Put a $ before the column name before you copy/paste:

=sum(F5:G5)

becomes

=sum($F5:$G5)

 

[mbarron]

If you highlight the cells and then click on one of the borders you can "move" the cells to the new location by dragging it to its new location without changing the cell relations. =sum(F5:G5) will remain =sum(F5:G5)

If you want to keep a constant cell reference (called an absolute cell reference in the help file) use the $ sign EX =sum($F$5:$G$5) copied and pasted will remain the same wherever it is paste.

Mike

 

[DaveNamou]

Thank you for your replies!

That will help in the future. But is it possible for the current 80 cells that I have now, or I'm I out of luck and have to manually change them all first?

Thanks Again! Dave Namou, MCSE CCEA

 

[mbarron]

How far do you have to move it. If it's only the 5 to the right you have above you can use the move method. Highlight the cells and then "grab" the border and drag the cells to their new location.

Mike

 

[DaveNamou]

I'm not moving them. I'm copying them. I need two columns. I was just thinking about making the 2nd column = the value of the first column. But I would prefer to have the values calculated using the values on the other sheet.
Dave Namou, MCSE CCEA

 

[mbarron]

If I'm reading it correctly you want the values in the second column to equal the results from the first set of formula.

Such as cell C1 is = sum(A1+B1) and then you H1 to equal the results of C1. (possibly even on another sheet?)

You can do this by highlighting the cells then use a right click and drag to the new location. Choose the "Link Here" option when you release. The resulting cell will be (H1 if using my example above) =C1. To take it to another sheet drag to the appropriate sheet and press the Alt" key to drag onto the new sheet.

Mike